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 Crater%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Poipu Crater is our slice of paradise AND it's literally located inside of an extinct volcanic crater. Nestled into the hillside, this condo is shaded from the afternoon sun keeping it comfortable and cool.
This is our home away from home. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Offering one of the best examples of authentic island-style living with banana trees reaching onto your private lanai amid acres of lush, tropical foliage. This property of 15 duplex bungalows resembles the South Pacific and old Hawaii. Your ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Poipu Crater is a truly unique setting - lush and tropical vegetation nestled an ancient cinder cone crater, just a few hundred feet from the southern most tip of the island of Kauai. This 2 bed 2 bathroom condo boasts vaulted ceilings and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Book this beautiful Hawaiian Cottage and experience authentic island-style living. Perched on top of an extinct volcano, leaving behind a unique 7 acre crater which surrounds the cottage with lush, tropical vegetation, including flowers, trees, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Perched on the highest tier of Poipu Crater to catch the tradewinds, surrounded by Plumeria, Areca and Coconut Palm trees providing lush, green island serenity and a sense of tropical privacy. A few footsteps from "the golden sands of the ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Koloa
As you drive down from the rim to the basin of this extinct volcano, you will notice and feel the unique charm of the Poipu Crater Resort. There is space, comfort, and privacy in this 1094 square foot, 2 bedroom/2 bath townhouse. The kitchen is ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Offering one of the best examples of authentic island-style living with banana trees reaching onto your private lanai amid acres of lush, tropical foliage. This property of 15 duplex bungalows resembles the South Pacific and old Hawaii. Your ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Tucked away in the heart of an extinct volcano, Poipu Crater Resort offers lush tropical gardens, privacy and comfort. Perched on the upper rim of the crater, perfectly situated to enjoy sunny mornings and cool tradewinds in the afternoon. This...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Poipu Crater Resort is one of the most affordable and surprising accommodations in Poipu, Kauai. This charming and unexpected treasure feels like you're at home, only you're surrounded by a tropical botanical garden in the crater of an extinct ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Offering one of the best examples of authentic island-style living with banana trees reaching onto your private lanai amid acres of lush, tropical foliage. This property of 15 duplex bungalows resembles the South Pacific and old Hawaii. Your ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
