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 ...
- 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
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
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
SPECIAL SUMMER RATES New Listing! Luxury Ground Floor Condo with Breathtaking Sunsets -Steps to Ocean and Pool!
Experience the ultimate Kauai getaway at this exquisite one bedroom and two bathroom ground floor condo in the premium Poipu ...
- 2 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 ...
- 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
Beautifully and advantageously designed, Poipu Kapili 58 is a spacious 1800 square foot 2 bedroom, 3 bathroom townhouse style condo. As you enter this lovely island home, you are immediately enthralled by the expanse and glorious view of the ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
This beautifully updated 1837 square foot, ocean-view condo will take your breath away! The luxurious furnishings, open floor plan, and coffered ceilings blend perfectly into the tropical surroundings. Equipped with portable air conditioning in ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Enjoy our breathtaking oceanfront resort style home, in Poipu Beach Kauai. Featuring 1837sqft of spacious condo living, with spectacular ocean, pool, and sunset views. With its classic Hawaii plantation style architecture and design, ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
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
