select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `address`,round(price) as price,minimum_nights, `rating`, `review_count`,`latitude`, `longitude`,case when propertyName='' then trim(address) else CONCAT(CONCAT(propertyName,'(',trim(address)),'',')') end as addre,propertyName,
(6371 *
acos(
cos( radians( 21.2759167 ) ) *
cos( radians( `latitude` ) ) *
cos(
radians( `longitude` ) - radians( -157.8233677 )
) +
sin(radians(21.2759167)) *
sin(radians(`latitude`))
)
) as distance from `vacationrentalindex` a inner join vacationrentalindexfeed b on a.uniturl=b.unitUrl and a.headline!='' and a.thumbnailUrl!='' and b.enabled=1 and b.blocked=0 ) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Kihei Garden Estates%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Step into paradise at this beautifully updated, second-floor condo, perfectly located in the heart of Kihei. Just steps from the shoreline, this 2-bedroom, 1-bath unit offers everything you need for an unforgettable Maui getaway.
Start ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kihei
From the front door, a magical stretch of Maui beach awaits your exploration -- and inside, this condo has been beautifully remodeled from top to bottom, with all sparkling new furnishings, surfaces, and fixtures. Kihei Garden Estates B-103, a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Unit #E-202 is a 2B/2B Loft-Style unit that is fully renovated. The unit is cooled with split system AC in the living room and loft bedroom; there are ceiling fans throughout. Enjoy the new 50" TV, BluRay Player, or Complimentary WiFi. Kihei ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Kihei Garden Estates #H-203 is a 2 bedroom, 2 bath unit on the second floor in a great location near the pool. It has a fully-equipped kitchen, fans throughout, 2 flat-screen TVs, a DVD player, telephone service, and free WIFI. There is a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Kihei Garden Estates is a lovely complex right across the street from Waiohuli Beach, which you can see from your private lanai. Maui’s famous sunsets are just steps away from your door. Please note: This unit is on the second floor with no ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Kihei Garden Estates #E-102 is a 1 bedroom, 1 bath ground floor unit with a queen bed, fully-equipped kitchen, flat screen TV, telephone with long distance calling, and free WIFI. Room unit a/c throughout as well as ceiling fans. For outdoor ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Kihei Garden Estates #E-204 is a two bedroom one bath unit with a fully equipped kitchen, Cable TV, and high speed internet service. The phone is also equipped with free long distance calling to the U.S. & Canada. Air conditioning room unit in ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This Maui condo is located in the heart of Kihei Hawaii called Kihei Garden Estates, located within 300 feet of the ocean and 20 minutes from the Kahului Airport. Modern amenities, the condo is perfect for a family of 4 adults and 2 children ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Indulge in the ultimate Maui experience at our charming one-bedroom, one-bath ground-floor condominium in Kihei, Hawaii. Just steps away from the beach, Kihei Garden Estates G-102 offers a serene oasis amidst breathtaking ocean views and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Discover beachside bliss in Kihei! this enchanting home captivates with its blend of cozy ambiance and unbeatable location. Steps away, discover a plethora of restaurants, shopping, dining, and coffee spots, all within walking distance. This ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Kihei Garden Estates #G-104 is a 1 bedroom, 1 bath ground floor unit with a Cal. King bed, fully-equipped kitchen, flat screen TV, and free WIFI. Room unit a/c throughout as well as ceiling fans. For outdoor activities, Kihei Garden Estates ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Welcome to Kihei Garden Estates C-105, where your dream Maui escape begins the moment you step through the front door! Just steps away from a stunning stretch of Maui beach, this ground-level condo has been fully remodeled to offer the perfect ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
