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, rewritten_description,
(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
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 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
Your home away from home in Poipu with 2 bedrooms. This fantastic townhome features a cozy queen bed and a luxurious king bed, perfect for a good night's sleep. With amenities like a hair dryer, washer & dryer, WiFi, and AC, you'll have
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
