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 Kapili%') AND sleeps>=1 order by rating desc LIMIT 0, 20
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
Located in one of the finest resorts on Kauai, with only 60 units on five beautifully-landscaped acres of Poipu Kapili, this is the perfect home for your family vacation or couples' retreat. The entire home is airconditioned, but also is ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Just renovated! Enjoy ocean waves and sweeping views of the Pacific in this stunning Hawaiian style townhome in Poipu Beach, Kauai. Creature comforts: Newly Installed central air for a good nights sleep; do not lie awake to the sound of ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Aloha and E Komo Mai’. Poipu Kapili #29 is the perfect island home for your vacation getaway, anniversary, or romantic honeymoon! Enjoy your stay in paradise in the comfort of our spacious, ground floor contemporary, island-style 1 bedroom, 2 ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Amidst the luxurious oceanfront Poipu Kapili Resort, is an exquisite 1 bedroom, 2 bath remodeled single level, second floor holiday home endeared as Poipu Kapili 30. Perfectly manicured lawns, immense palm trees along with fragrant and vibrant ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Welcome to our exquisite 1100-square-foot luxury condominium, a jewel nestled in the vibrant heart of Poipu on Kauai's South Shore. Enjoy the beautiful pool with the Ocean as your picturesque backdrop. Enjoy nightly sunsets and the convenience ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
