select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) 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, 15
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.
Same day bookings (meaning arrival is within 24 hours of...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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
HI> 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.
Same day bookings (meaning arrival is within 24 hours of...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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.
Same day bookings (meaning arrival is within 24 hours of...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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
HI> Kihei
Kihei Garden Estates #B-201 is a 2 bedroom 1 bath upstairs unit with a fully equipped kitchen, Cable TV/DVD player. There is a Queen bed in the Master and a Queen bed in the guest bedroom. Air conditioning room unit in the living room only. ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> 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
HI> Kihei
Come and enjoy our beautifully appointed one-bedroom, one bath ground floor condominium. We are located within a few steps of the ocean where you can enjoy some of the most spectacular sunsets on Maui. From Kihei Garden Estates G-102’s covered...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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
HI> Kihei
