select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `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 and b.blocked=0 ) 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, 20
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
Hawaii> 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 10
Hawaii> Kailua-Kona
Located in the Award Winning Resort Community of Hualalai Four Seasons.
3 Bedrooms / 3 Baths
Living Area: 2,657 sqft and 940 sqft of Lanai
Ocean,Golf Course and Mountain Views
Spilt Level Townhouse
2 ~ 4 person Golf ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Located in the Award Winning Resort Community of Hualalai Four Seasons.
Beautiful ocean views from this delightful two bedroom Garden Level Villa. Spacious Lanai with lounge seating extends seamlessly to a large lawn area. Enjoy the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> 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
Hawaii> 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 ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> 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
Hawaii> Kailua-Kona
*Newly Remodeled*
Kahikole 4 bedroom Luxury Villa on the 8th fairway of the Nicklaus designed Hualalai Golf Course. Incredible views over the 8th and 4th greens, ocean views and sunsets.
Kahikole Villa offers 3 bedrooms in the main ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua Kona
WHY IT'S SPECIAL
Picturesque, bedroom island suites with outdoor garden showers, premiere resort location, impressive indoor and outdoor chef's kitchen, and oceanside views from the sprawling lanai.
Completely relish in the ...
- 4 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
Magnificent Garden Level Golf Villa 1101 enjoys golf course and water views from this highly desirable location at Hualalai Resort.
From sunrise to sunset unwind on the spacious lanai- gazing at the verdant green golf course, with the ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Turn moments into memories at this private villa located inside the gated community of the iconic Four Seasons at Hualalai Resort. Experience world-class golf, spa, restaurants, and more. Waiulu Villa, 133C is a three-bedroom, ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua Kona
LIMITED-TIME OFFER: when you reserve today, you will receive one round of golf for up to 8 guests and in villa luau not including the cost of food and beverage) for the ultimate Big Island experience.
WHY IT'S SPECIAL
Every nuance ...
- 5 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
Experience the epitome of island living at this exquisite 3-bedroom, 2-bathroom villa located within the serene and exclusive Hualālai Resort. Offering unparalleled ocean, golf course, and sunset views, this single-level residence provides ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
