select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw WHERE 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%poipu kai%') AND sleeps>=1 order by IF (ISNULL(`bedrooms`), 1,0), `bedrooms` desc LIMIT 0, 15
Beautiful 7 bedrooms (7th is a loft bedroom) for 16 in Poipu Kai is located only 250 yards to Brenneckes Beach and 350 yards to Poipu and Shipwreck Beach! This is 2 villas next door to each other in the same building.
Paved walkway from your ...
7 bedroom
5 bathroom
Sleeps 16
HI> Koloa
***Please inquire before booking. Dates are not confirmed until Owner/ Manager sends confirmation. Calendars are not live or accurate due to multi agents and units.***
***The calendar is not perfect. We encourage guests to make an inquiry first...
7 bedroom
5 bathroom
Sleeps 16
HI> Koloa
Five Bedroom and Three Bathroom custom home in beautiful Lanai Villas in the Poipu Kai Resort. From the home you will find walking paths to Poipu's finest beaches, tennis, Pool and Spa. Start your morning with a short walk to capture the sunrise...
5 bedroom
3 bathroom
Sleeps 10
HI> Koloa
Featuring some of the newest and finest Kauai condos on island. Each fully air conditioned Regency villa has luxurious designer furniture and interiors as well as all the needed modern comforts. Unit 821 is also available as a 2 bedroom Kauai ...
4 bedroom
3 bathroom
Sleeps 8
HI> Koloa
Nestled in flourishing tropical landscape within the Poipu Kai Resort, this spacious 4 bedroom, 3 bath Kauai vacation home is uniquely designed for privacy with a beautifully appointed back yard and covered patio. Central air conditioning, ...
4 bedroom
3 bathroom
Sleeps 8
HI> Koloa
Step through the door of Hale Mahina Kauai and feel the aloha. The great room is the center of the home and opens onto the expansive screened lanai and the tranquil garden in the private back yard beyond. Both the kitchen and master bedroom also...
3 bedroom
2 bathroom
Sleeps 8
HI> Koloa
This luxury Kauai vacation rental is set on the coveted greenbelt in the center of Poipu Kai and overlooks Shipwreck Beach. Hale Maluhia at Poipu Kai blends indoor and outdoor elements into a unique island life experience with custom made ...
3 bedroom
3 bathroom
Sleeps 8
HI> Koloa
Hale Maluhia Maikai is a luxurious greenbelt property in Regency Villas at Poipu Kai with air conditioning on the sunny south shore of Kauai has designer furnishings and tasteful decor. Spacious and bright with two stories and an open loft with ...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
Very comfortable 3 bedroom, 2.5 bath unit in Poipu Kai! The unit has AC which is rare! The unit is a 2nd floor unit which means you get beautiful lanai's to enjoy your morning coffee or an evening drink.
The property comes with pool and hot tub...
3 bedroom
3 bathroom
Sleeps 7
HI> Koloa
E komo mai (welcome) to Regency at Poipu Kai #323, your peaceful Hawaiian home away from home. This south shore Kauai vacation rental condo boasts an ideal location in the heart of sun-drenched Poipu, with easy access to the Garden Island’s ...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
Aloha! Come bring your ohana (family) and friends to sit on our beautiful wrap around patio at our beautiful Pu'uhonua (place of refuge) with breathtaking tropical views. The great room and kitchen are the heart of Pu'uhonua located on the 2nd...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
You and your 'ohana (family) will fall in love with this beautiful and open Hawaiian home. The spacious property is gently cooled by Kauai's trade winds and offers lovely natural light.
With our extensive cleaning protocols we are committed to ...
3 bedroom
3 bathroom
Sleeps 10
HI> Koloa
The Villas at Poipu Kai are one of the newer luxury condominium resorts on the South side of Kauai in Poipu. These spacious and upscale condominiums are part of the Poipu Kai Resort that offers access to additional recreational amenities to all ...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
The light, bright, and airy condo can be your private vacation retreat! A wonderful feeling of home, with fantastic cross-breezes created by the Pacific trade winds, this 1478 square foot unit is located just a short walk to the beach and very ...
3 bedroom
3 bathroom
Sleeps 8
HI> Koloa
The Villas at Poipu Kai is the newest and most luxurious development within Poipu Kai Resort, located on the sunny south shore of Kauai! Unit C110 is a 3 bedroom 3 bathroom ground level luxury villa, boasting over 1,600 square feet of living ...
3 bedroom
3 bathroom
Sleeps 8
HI> Koloa
