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 '%Royal Sea Cliff%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Enjoy the Big Island in a lush, oceanfront setting. The Wyndham Royal Sea Cliff is conveniently located a mile and a half from the charming town of Kailua-Kona. Enjoy nearby restaurant dining, or shop at the local farmer’s markets and make ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kailua-Kona
Royal Sea Cliff Resort, Kailua Kona, Hawaii
An architectural masterpiece that cascades down from an ancient lava-flow bluff to a wide swath of the best pristine oceanfront in Hawaii. Horizon Ocean Views of local fisherman, cruise ships, whales ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kailua-Kona
ROYAL SEA CLIFF RESORT, KAILUA KONA
An architectural masterpiece that cascades down from an ancient lava-flow bluff to a wide swath of the best pristine oceanfront in Hawaii. Horizon Ocean Views of Local Fisherman, Cruise Ships, Whales and ...
0 bedroom
1 bathroom
Sleeps 3
HI> Kailua-Kona
ROYAL SEA CLIFF RESORT, KAILUA KONA
An architectural masterpiece that cascades down from an ancient lava-flow bluff to a wide swath of the best pristine oceanfront in Hawaii. Horizon Ocean Views of Local Fisherman, Cruise Ships, Whales and ...
0 bedroom
1 bathroom
Sleeps 3
HI> Kailua-Kona
Aloha and welcome to Wyndham Royal Sea Cliff! This paradise within a paradise is designed to promote all that the Big Island has to offer, making this the perfect location for a family vacation or a romantic getaway. Breathtaking water views are...
2 bedroom
2 bathroom
Sleeps 6
HI> Kailua-Kona
PLEASE SEND AN INQUIRY BY CLICKING THE CONTACT HOST TAB BEFORE REQUESTING TO BOOK!
Enjoy the Big Island in a lush, oceanfront setting. The Wyndham Royal Sea Cliff is conveniently located a mile and a half from the charming town of Kailua-Kona. ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kailua-Kona
Concierge Realty is proud to present luxury villas at this premier location.
ARRIVALS ARE FRIDAY, SATURDAY, SUNDAY AND MONDAY ONLY. RESERVATIONS AVAILABLE IN SEVEN NIGHT INCREMENTS ONLY.
The spacious two-bedroom resort suites comfortably ...
2 bedroom
2 bathroom
Sleeps 6
HI> Kailua Kona
conveniently located a mile and a half from the charming town of Kailua-Kona. Enjoy nearby restaurant dining, or shop at the local farmer’s markets and make yourself at home in your roomy kitchen. Relax by the pool, overlooking the Pacific ...
2 bedroom
2 bathroom
Sleeps 6
HI> Kailua-Kona
Enjoy the Big Island in a lush, oceanfront setting. The Wyndham Royal Sea Cliff is conveniently located a mile and a half from the charming town of Kailua-Kona. Enjoy nearby restaurant dining, or shop at the local farmer’s markets and make ...
2 bedroom
2 bathroom
Sleeps 6
HI> Kailua-Kona
PLEASE SEND AN INQUIRY BY CLICKING THE CONTACT HOST TAB BEFORE BOOKING!
Enjoy the Big Island in a lush, oceanfront setting. The Wyndham Royal Sea Cliff is conveniently located a mile and a half from the charming town of Kailua-Kona. Enjoy ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kailua-Kona
Nearby Pu‘uhonua o Hōnaunau, an ancient sanctuary whose name means "the place of refuge," could easily describe this resort. Oceanfront, modern, and conveniently tucked about 1.5 miles from Kailua-Kona, tranquility reigns. It’s the ideal ...
2 bedroom
2 bathroom
Sleeps 6
HI> Kailua-Kona