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 '%Hualalai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
**COVID 19 Restrictions: The State of Hawaii has updated their Safe Travels program. Please refer to https://hawaiicovid19.com/travel/faqs/ for the latest information about traveling to Hawaii and how to bypass the mandatory 10 day quarantine ...
4 bedroom
5 bathroom
Sleeps 8
HI> Kailua Kona
On the Kona side, safe and away from the volcanic activity, the Cottage awaits you.
The perfect, peaceful getaway for those on a honeymoon or looking for a relaxing, romantic experience. This warm, welcoming, newly-built and finely crafted ...
1 bedroom
1 bathroom
Sleeps 2
HI> Kailua-Kona
Please note that Chairman's Club Events will take place at Hualalai Resort from November 30th - December 4th, 2021, May 3rd - May 7th, 2022, and November 29th - December 3rd, 2022. These events will result in limited access to resort amenities....
3 bedroom
3 bathroom
Sleeps 6
HI> Kailua Kona
Hillside 1102 is nestled near the shore in the exclusive 5 Star Four Seasons Hualalai Resort. From the lanai, listen to the rhythmic waves on the shore, just a short walk from the Villa. Fully receding pocket glass, screen and shutter doors ...
2 bedroom
2 bathroom
Sleeps 4
HI> Kailua-Kona
Please note that Chairman's Club Events will take place at Hualalai Resort from November 30th - December 4th, 2021, May 3rd - May 7th, 2022, and November 29th - December 3rd, 2022. These events will result in limited access to resort amenities....
2 bedroom
2 bathroom
Sleeps 4
HI> Kailua Kona
Whale watch while listening to the breaking waves from the shoreline from the Master bedroom's private lanai with unobstructed ocean views! This beautifully appointed three bedroom villa has over 2,500 square feet of living area. Each of the ...
3 bedroom
4 bathroom
Sleeps 6
HI> Kailua Kona
Built in 2009, Haliipua Villas are the newest generation of multi-family villas at Hualalai offering an array of designer finishes throughout the home. Special features include luxurious mahogany finishes, island-inspired ceiling treatments, ...
3 bedroom
4 bathroom
Sleeps 6
HI> Kailua-Kona
Beautifully maintained, this villa offers a welcomed amount of privacy from both the upper and lower levels without compromising stunning panoramic view of the ocean and the 4th fairway of the Jack Nicklaus signature Hualalai Golf Course. The ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kailua-Kona
Located in the Award Winning Resort Community of Hualalai Four Seasons.
Enjoy the sounds of the ocean serenade, the magic of sunsets, and whale watching during the winter months, from this recently updated garden level Hillside Villa.
...
3 bedroom
3 bathroom
Sleeps 6
HI> Kailua Kona
Please note that Chairman's Club Events will take place at Hualalai Resort from November 30th - December 4th, 2021, May 3rd - May 7th, 2022, and November 29th - December 3rd, 2022. These events will result in limited access to resort amenities....
3 bedroom
4 bathroom
Sleeps 6
HI> Kailua Kona
Enjoy the quintessential Hawaiian vacation relaxing at the Four Seasons in Hualalai. Optional full access to the incredible array of luxury amenities offered at the Four Seasons including pools, dining, golfing, and a full service spa to soothe...
2 bedroom
2 bathroom
Sleeps 4
HI> Kailua Kona
This garden level 2 bedroom, 2 bath Fairway Villa overlooks the 15th Fairway of the Hualalai Golf Course with views of the ocean beyond. Located within the master-planned resort community of Hualalai, guests have the option of amenity use ...
2 bedroom
2 bathroom
Sleeps 4
HI> Kailua Kona
Gorgeous, Contemporary, Newer Home Exemplifies Luxury and ease of Living in our Island Paradise. This 5-bedroom, 5 1/2 bath pod-style estate home is exceptionally appointed and designed to take full advantage of the temperate climate and island ...
5 bedroom
6 bathroom
Sleeps 8
HI> Kailua-Kona
This beautiful and meticulously maintained home situated on the 9th hole of Hualalai's Jack Nicklaus golf course with Pacific Ocean views is available for private rentals. Perfectly located, the home is just a short walk or golf cart ride (two ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kailua-Kona
Please note that Chairman's Club Events will take place at Hualalai Resort from November 30th - December 4th, 2021, May 3rd - May 7th, 2022, and November 29th - December 3rd, 2022. These events will result in limited access to resort amenities....
3 bedroom
3 bathroom
Sleeps 6
HI> Kailua Kona
