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, 50
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
Ke Alaula Villa 217B at Hualalai Resort
Experience unparalleled luxury and tranquility at Ke Alaula Villa 217B, located within the prestigious Hualalai Resort. This exceptional property is situated within one of Hawaii’s most renowned ...
- 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
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
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 4 bedroom
- 5 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
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kailua Kona
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
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
Welcome to Hillside Villas, your serene escape nestled in the heart of paradise. Experience the perfect blend of luxury and tranquility in our beautifully appointed villas, where breathtaking views and world-class amenities await. Whether you're...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
The Annual Chairman's Club Event at Hualalai Resort will take place from Monday December 01 through Saturday December 06, 2025. This event will result in modified access to select resort amenities.
The annual Mitsubishi Electric ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua Kona
