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 palms%') AND sleeps>=1 order by rating desc LIMIT 0, 50
This magnificent end unit Penthouse was completely remodeled with everything new just a couple years ago! The Pacific Ocean stretches out as far as you can see in front of you. You often see turtles, spinner dolphins and even whales when they ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Palm trees and the beautiful blue waters of the Pacific Ocean greet you the second you open the front door of Poipu Palms 202.
This is the beginning of your perfect Hawaiian vacation.
Overlooking the powerful Pacific Ocean and the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Poipu
Part of the intimate Poipu Palms condo community, this tropical property stuns with incredible ocean views. Out on the private lanai, gather with your ohana to sip on a cool drink and let the ocean breezes lull you into complete relaxation ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU PALMS 204 AT KOLOA KAI VACATION RENTALS
Oceanfront Bliss with Panoramic Views
Welcome to Poipu Palms 204, where your Hawaiian dream vacation begins! Nestled on a stunning oceanfront bluff, this charming two-bedroom, two-bath ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU PALMS 302 AT KOLOA KAI VACATION RENTALS
Top-Floor Oceanfront Retreat with Unparalleled Views
Escape to Poipu Palms 302, a serene oceanfront getaway perched on the top floor of this intimate community. From sunrise to sunset, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
