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, 30
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
Oversized 1 bedroom / 2 full bathrooms; ocean views from premium 2nd floor location
Spectacular views and deluxe furnishings will make Poipu Kapili #22 your home away from home. This spacious and comfortable home has an air conditioned ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Poipu Kapili is an oceanfront resort located in one of the most desirable locations on Kaua'i: Poipu. Walking in the door to Poipu Kapili 46, you will be drawn to the lanai overlooking the lush green grass, deep blue pool, and the spectacular ...
- 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
- 2 bathroom
- Sleeps 8
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 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
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Poipu Kapili is home to ideal oceanfront condos. The location of Poipu Kapili Condo #50 is in the midst of the impeccably maintained luxury 52 villa complex.
Upon entering Unit 50 you will feel yourself immediately relax with Island ...
- 1 bedroom
- 2 bathroom
- Sleeps 3
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
