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 '%Kiahuna Plantation%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This 3rd-floor, 880sf tropical retreat for four offers a spacious living area and WiFi Internet. Its large, furnished lanai overlooks beautiful gardens, ponds, and the glittering beaches of Poipu.
Fringing Poipu Beach, this plantation-style...
1 bedroom
1 bathroom
Sleeps 4
HI> KOLOA
Embrace casual living in this 690sf suite with a furnished lanai flowing onto the landscaped lawn. Touches include a full kitchen and dining table that make at home meals a breeze, ceiling fans, WiFi, wall-mounted flat screen with DVD and ...
1 bedroom
1 bathroom
Sleeps 4
HI> KOLOA
You’ll love the glass block walk-in shower of this 690sf villa where the ocean peaks above a canopy of trees off the lanai. Upgraded with wood cabinets and granite countertops, the kitchen flows into a living area with dining table, ceiling ...
1 bedroom
1 bathroom
Sleeps 4
HI> KOLOA
You’ve arrived! This comfy 640sf island-style retreat sleeping four welcomes you to laid back Poipu living. Laze the day away overlooking the grassy green lawns and tropical flowers. Dine at home with meals cooked in your full kitchen. Then ...
1 bedroom
1 bathroom
Sleeps 4
HI> KOLOA
Just say “ahh” to a casual lifestyle in this air conditioned 640sf retreat sleeping four! Enjoy breakfast on your lanai. Stroll the beach. Prep lunch and dinner in your full kitchen with granite countertops. Then unwind in the spacious ...
1 bedroom
1 bathroom
Sleeps 4
HI> KOLOA
You may never want to leave this beautiful island home! Enhanced with tropical décor and modern touches, this 880sf heaven of taste sleeps four in a king and sofabed. Walk the beach, refresh under the rain showerhead, prep a meal in the gourmet...
1 bedroom
1 bathroom
Sleeps 4
HI> KOLOA
Enjoy the ultimate Poipu vacation experience at Kiahuna Plantation Hale, located on the fourth fairway of the Kiahuna Plantation Golf Course in lovely Wainani at Poipu. This stunning home boasts gorgeous mountain views, a split air conditioning ...
3 bedroom
4 bathroom
Sleeps 6
HI> Koloa
The spirit of aloha lives in our tropical two-bedroom, ocean view condominium, and it awaits your arrival! Situated on a small rise, the view of the ocean is as delightful as the trade winds that greet you when you slide open the plantation ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
This is the perfect tropical resort in Kauai nestled among the palm trees on 35 acres of lush lawns and fragrant flowers. You get beautiful ocean views and sunsets from the upstairs lanai (rare). Condo #9 is remodeled and has 1 bedrm, 1 bath1, ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
Guest to pay onsite management direct at check in for required cleaning. Full mid-stay cleaning is 95.00 plus 17.962% tax and check out Cleaning Fee is $225.00 plus 17.962% tax. Mid stay cleaning is required for each booking of 10 nights or ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
Kiahuna Plantation is the only beachfront condo complex in Poipu Beach. Situated on 35 acres with low density, plantation style buildings, luscious grass areas and beautiful tropical vegetation, Kiahuna is an ideal vacation spot. The ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa, Kauai
Hale Kamaluhia Poipu (House of Serenity at Poipu: please feel free to check out the condo website using the Hawaiian name) at Kiahuna Plantation (Unit 53 in building 10) is a newly renovated one bedroom, one bath condo, upstairs corner unit, in ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
Paradise is at your doorstep from this beautifully furnished, completely renovated condominium with a wide-open ocean view and just steps across a manicured lawn to the beach. Tropical colors dazzle both outside and within this 640 sqf suite ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
One-bedroom garden-view condominium at Kiahuna Plantation in Poipu Beach, Koloa, Kauai. On the sunny side of Kauai. Unit number 428 in building 41 (second level of a three-level 18-unit building). Beautiful mahogany trim throughout, matched ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
Welcome to the famed Kiahuna Plantation Resort, located on the very desirable Poipu Beach. The Kiahuna sits on 35 acres of lush, tropical gardens, with orchids and lily ponds and boasts an expansive beachfront lawn surrounded by palm trees. The ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
