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 resort%') 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
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
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
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
Magnificent Garden Level Villa located on the #18 hole of the Hualalai Jack Nicklaus Signature Course. Enjoy white water views and spectacular sunsets from this highly desirable location at Hualalai Resort. Listen to the ocean and whale watch ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
HI> Kailua Kona
STVR-19-357988
https://tours.spaces.808.com/1759448
Luxury living can be found at this spectacular 4 bedroom, 4.5 bath, resort home. Located within the Four Seasons Resort Hualalai on the Big Island of Hawaii.
The gourmet kitchen is...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Kailua-Kona
**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 ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
HI> Kailua Kona
