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, 50
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
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
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 ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Enjoy your stay in paradise in the comfort of our spacious, ground floor contemporary, island-style 1 bedroom, 2 bath condo. Whether you’re in the living room, kitchen, dining area, or lanai, our ocean views will enchant you! Enjoy your ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
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
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 a luxury, boutique 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, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Koloa
Comfort meets elegance in this 1,200-sqft one-bedroom Poipu Kapili condo. Perfectly situated to offer breathtaking ocean and pool views, the west-facing lanai puts you on the front row for those mesmerizing Poipu sunsets. With ceiling fans ...
- 2 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Koloa
