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 (city = 'makena' OR postalCode = 'makena' OR region like '%|makena|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Located on Maui's South Shore in the heart of beautiful Makena Bay, this single story oceanfront cottage is an island paradise. Built over 60 years ago and recently remodeled, guests are provided with all the modern conveniences for an ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Makena
Our collection of Maui vacation residences are HOTEL-Zoned or have LEGAL short-term rental permits. Reserve with Private Paradise Villas with confidence knowing you will be luxuriating in a location legally designated for visitor accommodations ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Makena
Gorgeous home on quiet Old Makena Road with West-facing ocean views from every room. Slip on your beach shoes, walk down your 40 foot dogleg to the left of your front lawn, step over small rocks, and swim with the turtles at our private ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Makena
The Polo Beach condo's sleep 6 in total. Please call for very latest openings as there may be a new booking in progress. Polo Beach Club has both ocean front and Prime (on sand ) ocean front units. On Beach, adjacent to many amenities ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Makena