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, rewritten_description,
(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, 50
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
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
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
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
- 3 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
- 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
- 3 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
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
