select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Makahuena%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Welcome to koolkondo where you will experience luxury vacation rentals at its finest. Our stylish boutique hotel style condos and homes have it all. You will absolutely enjoy your home away from home with VIP concierge style services in modern ...
- 3 bedroom
- 5 bathroom
- Sleeps 8
HI> Koloa
Located on the southernmost point of Kauai, this unique oceanfront property is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline from each unit. Incredible ocean views are the dominant...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Located in the "almost always" sunny, resort town of Poipu, on the gorgeous south shore of Kauai, this oceanfront complex is situated on the southernmost point of the island. From the edge of the dramatic cliffs, just above the crashing waves,...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Koloa
Poipu Hana Hou Hale is THE vacation home of your dreams. Situated in sunny Poipu, with first-class views of the sparkling Pacific, this picturesque home is equipped with everything you need for an unforgettable stay on Kauai. Enjoy uninterrupted...
- 5 bedroom
- 3 bathroom
- Sleeps 7
HI> Koloa
“The Makahuena” is located on the southernmost point of Kauai. This unique oceanfront resort is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline. In the winter time you can even ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Makahuena is a small, quiet, privately owned, condominium resort located on Poipu Point with resident manager on site. It is near the Hyatt Regency and Poipu restaurants. Poipu & Shipwreck Beaches are within 10 minute walking distance. The site ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
>
Located on the southernmost point of Kauai, this unique oceanfront property is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline from each unit. Incredible ocean views are the dominant...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Located on the southernmost point of Kauai, this unique oceanfront property is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline from each unit. Incredible ocean views are the dominant...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Our condo faces southeast with an uninhibited view of the mountains, Shipwreck Rock and, of course, the beautiful Pacific Ocean. In the winter months, you can watch whales breach right from our lanai!
The condo is on three levels. The first ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Poipu
“The Makahuena” is located on the southernmost point of Kauai. This unique oceanfront resort is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline. In the winter time you can even ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Koloa
Located on the southernmost point of Kauai, this unique oceanfront property is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline from each unit. Incredible ocean views are the dominant...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Poipu Hana Hou Hale & Ohana is THE vacation home of your dreams. Situated in sunny Poipu, with first-class views of the sparkling Pacific, this picturesque home is equipped with everything you need for an unforgettable stay on Kauai. Enjoy ...
- 7 bedroom
- 4 bathroom
- Sleeps 9
HI> Koloa
Breathtaking ocean views and spacious living accommodations await you at this fabulous 3 bedroom Makahuena Condoin Poipu. Well appointed fully equipped kitchen, accommodating up to 6 people, in a total of 1300 square feet of living space. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Located on the southernmost point of Kauai, this unique oceanfront property is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline from each unit. Incredible ocean views are the dominant...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Koloa
Go with the flow in this tri-level, 1,113sf haven of style sleeping eight in a sofa bed, king, queen and two twins. Enjoy savoring wine and island snacks while gazing at the Pacific from your lanai. Amenities include WiFi, ceiling fans, ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> POIPU
