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%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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 ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
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, enhanced ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua-Kona
This spacious 3 bedroom, 3.5 bath single-level villa is centrally located on the Resortโs popular Waiulu Street overlooking beautiful Punawai Pond, the 5th Hole of the Memberโs Ke`olu Golf Course and the ocean beyond. With 2642 square feet ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua-Kona
One of the largest floor plans of any villa at Hualalai, this spacious and centrally located 3 bedroom, 3 bath villa is ideal for your Hawaii getaway. Extra bedding available with a "bonus" living room on the first level. The down-stairs suite ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Whale watch while listening to the breaking waves from the shoreline from the Primary 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
Hawaii> Kailua Kona
The spacious floor plan includes a downstairs primary bedroom suite with king bed and luxury spa-like bath. Just off the master is a large living room with queen sized sofa pull out that opens to the downstairs lanai, leading to the lawn and ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> 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
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
Newly renovated villa from top to bottom. A 3 bedroom, 3.5 bath luxury villa conveniently located in the heart of Hualalai Resort overlooking the 15th hole of the Jack Nicklaus designed Hualalai Golf Course. Ground floor single level floor plan ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua-Kona
**THANKSGIVING SPECIAL ** Book 6 nights or more and enjoy a complimentary housekeeping service** Ke Alaula Villa 217B is located in the Award Winning Resort Community of Hualalai Four Seasons. 3 Bedrooms / 3 Baths Living Area: ...
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
Please note that the annual Chairman's Club Event will take place at Four Seasons Resort at Hualalai from Tuesday, December 02 through Saturday, December 07, 2024. This event will result in modified access to select resort ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to Paradise! Enjoy Hawaii's Big Island from your private condo (with an ocean view)! Your condo within the Vista Waikoloa Complex features: ๐ด Ocean View of Anaeho'omalu Bay (A/Bay for short!) from Lanai, Living ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
One of only seven Hualalai Penthouse Villas. Upper-level Villa featuring single level interior with over 4,200 sq feet of living and lanai space. Hear the sounds of the Pacific Ocean while enjoying the beautiful close unobstructed ocean and ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Please note that the annual Chairman's Club Event will take place at Four Seasons Resort at Hualalai from Tuesday, December 02 through Saturday, December 07, 2024. This event will result in modified access to select resort ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Exclusive Special ~ $1000 off per night from April 8 - November 22, 2024 7-night minimum stays Welcome to the exquisite Waiulu Estate Villa at Four Seasons Hualalai Resort. This estate offers a private and serene setting with close ...
5 bedroom
6 bathroom
Sleeps 10
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