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, 50
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
POIPU CRATER 20 AT KOLOA KAI VACATION RENTALS
A Unique Hawaiian Retreat Inside a Tropical Crater
Welcome to Poipu Crater 20, a charming and secluded two-bedroom condo nestled within a dormant volcanic crater. Surrounded by lush ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU CRATER 24 AT KOLOA KAI VACATION RENTALS
Your Tropical Oasis in the Heart of Poipu
Immerse yourself in the beauty of authentic island-style living at Poipu Crater 24, a tranquil retreat located in a unique enclave of 15 duplex...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU CRATER 23 AT KOLOA KAI VACATION RENTALS
A Tropical Retreat in an Extinct Volcano Crater
Discover the unique charm of Poipu Crater 23, nestled amidst acres of lush tropical foliage in the heart of Poipu. This two-bedroom ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Nestled in the heart of Poipu, this charming 2-bedroom , 2 bath vacation home offers the perfect escape for your Hawaiian getaway. Imagine yourself relaxing by the sparkling outdoor pool, soaking up the sun on the beach loungers, or exploring ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
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, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
POIPU CRATER 12 AT KOLOA KAI VACATION RENTALS
A Secluded Tropical Oasis in Poipu
Escape to Poipu Crater 12, a charming 2-bedroom, 2-bathroom condo nestled in the lush and tropical surroundings of an ancient cinder cone crater. This...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Koloa
POIPU CRATER OHANA AT KOLOA KAI VACATION RENTALS
A Unique and Spacious Retreat in the Heart of Poipu
Welcome to Poipu Crater Ohana, a combination of Poipu Crater 23 and Poipu Crater 24, offering a perfect retreat for families or ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Koloa
