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 '%poipu palms%') AND sleeps>=1 order by rating desc LIMIT 0, 15
POIPU PALMS 204
*UNDER NEW MANAGEMENT*
(Please excuse the lack of reviews - we are currently helping our guests become acquainted with the property and the new reviews will follow shortly. Mahalo!)
Step out on the private lanai, take a deep...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Perched on the edge of the magnificent Poipu coastline, this intimate resort is home to just twelve oceanfront Kauai condos and features spectacular views as well as a dramatic coastline swimming pool, sundeck and BBQ. Located in the heart of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
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 mode....
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
This magnificent end unit Penthouse is completely remodeled with everything new! 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 are in season! Enjoy the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Perched on the edge of the magnificent Poipu coastline, this intimate resort is home to just twelve oceanfront Kauai condos and features spectacular views as well as a dramatic coastline swimming pool, sundeck and BBQ. Located in the heart of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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 spectacular ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Poipu
Perched on the edge of the magnificent Poipu coastline, this intimate resort is home to just twelve oceanfront Kauai condos and features spectacular views as well as a dramatic coastline swimming pool, sundeck and BBQ. Located in the heart of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Perched on the edge of the magnificent Poipu coastline, this intimate resort is home to just twelve oceanfront Kauai condos and features spectacular views as well as a dramatic coastline swimming pool, sundeck and BBQ. Located in the heart of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa