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
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
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
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
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 24 AT KOLOA KAI VACATION RENTALS
Experience the epitome of authentic island-style living at this unique property, where banana trees reach onto your private lanai amid acres of lush, tropical foliage. This charming enclave of...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU CRATER 23 AT KOLOA KAI VACATION RENTALS
Experience authentic island-style living at its finest in this unique property, where banana trees reach onto your private lanai amid acres of lush, tropical foliage. Resembling the charm of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU CRATER OHANA AT KOLOA KAI VACATION RENTALS
Welcome to Poipu Crater Ohana, a unique and charming retreat that combines the best of Poipu Crater 23 and Poipu Crater 24. Nestled in the crater of an extinct volcano, this property offers...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Koloa
Sea Garden (kīhāpai kai), is a designer renovated cottage, one of only 15 split-bungalows privately nestled inside the ancient Poipu Crater. The Crater is a garden of Eden, a true Shangri-la on 7.5 private acres, full of lush tropical flower ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
