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 '%poipu makai%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Poipu Makai is situated on an ocean bluff for excellence in viewing abundant sea life and beautiful Pacific Ocean sunsets over the Poipu coastline. Because of the unique ridge location, Poipu Makai E-1 is located on the ground floor while it is ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Koloa
POIPU MAKAI C1 AT KOLOA KAI VACATION RENTALS
Nestled on the edge of a bluff, this exclusive oceanfront property offers breathtaking views of the azure Pacific and the entire Poipu coastline from every condo. With only 15 condos, this ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
