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 Crater%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Koloa
POIPU CRATER 12 AT KOLOA KAI VACATION RENTALS
Poipu Crater is a truly unique setting - lush and tropical vegetation nestled in an ancient cinder cone crater, just a few hundred feet from the southernmost tip of the island of Kauai. This...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Koloa
Located on the South Side of Kauai, Poipu Crater 15 is a beautiful, fully remodeled, and refurnished two-story townhouse offering the island experience you’re looking for! The owners spared no expense when they renovated this two-bedroom, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
**Poipu crater pool will be closed for replastering for 4 weeks beginning April 15, 2024**
Poipu Crater Resort is one of the most affordable and surprising accommodations in Poipu, Kauai. This charming and unexpected treasure feels like ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Koloa
POIPU CRATER 20 AT KOLOA KAI VACATION RENTALS
Step out on the private lanai, take a deep breath, and enjoy the beautiful lush tropical gardens with colorful birds chirping in the trees. During your stay at Poipu Crater 20, your ohana ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Welcome to Kauai Birdsong at Poipu Crater, offering a unique blend of seclusion and tropical beauty. This two-bedroom, two-bathroom condo is surrounded by seven-and-a-half acres of gardens and mature palms. With its vaulted ceilings, expansive ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
