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, 20
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
Hawaii> Koloa
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
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
Hawaii> Koloa
POIPU PALMS 204 AT KOLOA KAI VACATION RENTALS
Step out on the private lanai, take a deep breath, and soak in the million-dollar view – your Hawaiian vacation has officially begun! During your stay at Poipu Palms 204, your ohana – ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
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
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
Hawaii> Koloa
POIPU PALMS 302 AT KOLOA KAI VACATION RENTALS
Step into your own slice of paradise at this oceanfront condo, where the sunsets are truly spectacular. The top-floor condo offers stunning views of the ocean, perfect for watching sea ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
