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, 30
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
Puhi Nani Pools will begin replastering and retiling our pool on April 15, 2024. The work will take approximately 4 weeks and the pool will be closed throughout that time.
Poipu Crater ...
- 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 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
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 20 AT KOLOA KAI VACATION RENTALS
Puhi Nani Pools will begin replastering and retiling our pool on April 15, 2024. The work will take approximately 4 weeks and the pool will be closed throughout that time.
Step out on ...
- 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
