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 '%The Cliffs%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Waipio Heaven is a spectacular resort oasis perched atop the dramatic cliffs of the Hamukua coast with breathtaking views of Waipio Valley throughout the property and yard. Nestled in a gated and lush 10 acre private compound, this one of a ...
5 bedroom
5 bathroom
Sleeps 10
HI> Honokaa
Hale Kai
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Hale Kai means Ocean House and it's easy to see where this home gets its name. At this oceanfront...
3 bedroom
2 bathroom
Sleeps 4
HI> Keaau
Cozy air conditioned ground floor studio apartment in building 8 at The Cliffs. This is our newest unit, we own two other properties at The Cliffs please see reviews at vrbo/4652532ha and vrbo/1580336.
Mountain views from lanai, community ...
1 bedroom
1 bathroom
Sleeps 2
HI> Princeville
Luxurious, top-floor, two-level, end unit with all the essentials and amenities you want on your restful, fun, and memorable vacation. Beautiful hardwood floors in main living areas, new tile flooring in all bedrooms, and clean and spacious ...
2 bedroom
2 bathroom
Sleeps 6
HI> Princeville
Large 1 bedroom / 2 full bath ground-floor condo
in Princeville, on Kauai's North Shore at The Cliffs Resort. No need to worry about struggling up stairs with your bags to get to this easily accessible condo. Check out our:
1. Queen sleep ...
1 bedroom
2 bathroom
Sleeps 4
HI> Princeville
Cliffs Resort at Princeville Unit 7302, North Shore of Kauai Vacation Rentals.
Ocean bluff, 2 Bedroom, 2 Bath Kauai vacation condo.
We have implemented enhanced cleaning and disinfecting guidelines as well as infection prevention advice ...
2 bedroom
2 bathroom
Sleeps 6
HI> Princeville
Experience stunning panoramic views of the Pacific ocean and Mt. Makana (“Bali Hai”) at this upgraded private & spacious condo in Princeville located on a bluff with distant ocean views. The Cliffs has everything you need for the perfect ...
1 bedroom
2 bathroom
Sleeps 4
HI> Princeville
Enjoy your own island oasis at this ocean view premier condo in Princeville! Feel at home in paradise while enjoying beautiful garden and ocean views. Situated on the illustrious North Shore, this island retreat is located near all the Garden ...
1 bedroom
2 bathroom
Sleeps 4
HI> Princeville
Perched on the hills of Kauai that overlook the island’s majestic north shore, this resort is a secluded getaway in the upscale Princeville community. Explore its 22-acre tropical garden by bike, hit the tennis courts, or lounge with a book by...
1 bedroom
2 bathroom
Sleeps 4
HI> Princeville
Aloha! Do you love paradise? We do! Welcome to the Garden Isle! My family and I love love Kauai and you will too! Frugal or all-in budget, there is something for everyone! Nestled on a secluded bluff overlooking the Majestic North Shore of ...
1 bedroom
2 bathroom
Sleeps 4
HI> Princeville
Only dates available are July 1-5!!
1 Bedroom Deluxe suite with full Kitchen overlooking the pacific ocean in magnificent Princeville, Kauii, Hawaii. Watch the dolphins and whales swim in front of you while you melt into the Hawaiin sunset! ...
1 bedroom
1 bathroom
Sleeps 4
HI> Princeville
