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 '%Wai\'ula\'ula%') AND sleeps>=1 order by rating desc LIMIT 0, 20
BEST LOCATION AND VIEWS!
Wai'ula'ula A101 is located near the exclusive Amenity Center and offers panoramic and unobstructed views of the ocean, golf course and sunset.
This unit is located on the ground floor and tastefully furnished & ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waimea
This corner, groundlevel, oceanview residence offers a great room with sliding glass pocket doors for indooroutdoor island living, a chefs kitchen outfitted with SubZero, Fisher Paykel and Wolf appliancesgas range, dual master suites with ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
This exceptional corner, ground-level, oceanview residence offers a great room with sliding glass pocket doors for indoor/outdoor island living, a chef’s kitchen outfitted with stainless steel gas range/appliances, a master suite with ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
The Ridge at Wai'ula'ula - Unit G102 FREE resort access to both the Mauna Kea Beach Hotel and The Westin Hapuna Beach Resort This first floor 3 bedroom, 3 bath condominium home has attractive island appointments with full ocean and Kawaihae ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waimea
This ground-level, corner, oceanview residence offers a great room with sliding glass pocket doors for indoor/outdoor island living, a chef’s kitchen outfitted with Sub-Zero and Wolf appliances/gas range and dual master suites. Additional ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
The Ridge at Wai'ula'ula on the Mauna Kea Resort- Unit J-202 FREE resort access to both the Mauna Kea Beach Hotel and The Westin Hapuna Beach Resort This large and luxurious 3 bedroom 3 bath condominium located at the beautiful Wai'ula'ula ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
This professionallydesigned, groundlevel, oceanview residence offers a great room with sliding glass pocket doors for indooroutdoor island living, a chefs kitchen outfitted with professionalseries, stainless steel appliancesgas range, dual ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
Enjoy the world renowned Mauna Kea Resort from Wai'ula'ula Estate 421, a spacious four bedroom private residence with a heated pool, spa and unobstructed ocean views. Built as the developers model residence, the home is perched over the 13th ...
- 4 bedroom
- 5 bathroom
- Sleeps 12
Hawaii> Kamuela
Beautiful resort villa with a private pool and spectacular ocean views at the Mauna Kea Resort. Villa 327 is a beautifully decorated 3 bedroom, 3.5 bath paired home that offers a spacious floorpan, a private pool deck, spill-over hot tub and ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kamuela
Wai'ula'ula Ridge H201 is a 3 bedroom, 3 bathroom ocean view condo with many upgrades. There are expansive 180 degree ocean views from the lanai and living room. Enjoy beautiful outdoor spaces with great seating and a built-in Wolf BBQ and bar ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
This chic and sophisticated, upcountry oceanview residence offers a great room with sliding glass pocket doors for indooroutdoor island living, an epicurean kitchen outfitted with Wolf appliances, including a gas range, double ovens and wine ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> KAMUELA
Your visit to our vacation home will capture the essence of Hawaii. We are located along the Big Islands exclusive golden Kohala Coast. Play golf on the emerald fairways of the renowned Mauna Kea Resort courses. Experience Kaunaoa and Hapuna, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
This luxe, upcountry, oceanview residence offers a great room with sliding glass pocket doors for indooroutdoor island living, a chefs kitchen outfitted with Wolf and SubZero appliances, including a double oven and wine refrigerator, dual master...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waimea
The Ridge at Wai'ula'ula - Unit G201 FREE resort access to both the Mauna Kea Beach Hotel and The Westin Hapuna Beach Resort This upstairs 3 bedroom, 3 bath condominium home has attractive island/asian appointments with a north to south full ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
This private, groundlevel, oceanview residence offers a great room with sliding glass pocket doors for indooroutdoor island living, a chefs kitchen outfitted with SubZero, Fisher Paykel and Wolf appliancesgas range and dual master suites. ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
The Ridge at Wai'ula'ula L101 Sleeps: 8 The Ridge L101 is a well-appointed, discerning residence featuring three bedrooms and three and one-half bathrooms in 2,588 square feet with a 626 sq. ft. wrap-around lanai. Highlights of this ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
This lavish, upcountry, oceanview residence is finished in classic Hawaiian dcor and offers a great room with sliding glass pocket doors for indooroutdoor island living, a chefs kitchen outfitted with Wolf gas range and double ovens, a wine ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Waimea
This beautifullyrenovated, groundlevel residence is adjacent to Hapuna Golf Courses 11th fairway and showcases breathtaking, unobstructed ocean and mountains views within an ultraspacious, corner layout sleeps 10. Reimagined by renowned designer...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> KAMUELA
This delightful, secondlevel, oceanview residence offers a great room with sliding glass pocket doors for indooroutdoor island living, a chefs kitchen outfitted with professionalseries, stainless steel appliancesgas range, and three bedrooms two...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kamuela
This handsome, upperlevel, oceanview residence offers a great room with sliding glass pocket doors for indooroutdoor island living, a chefs kitchen outfitted with Wolf, SubZero, Fisher and Paykel appliances, and dual guest suites, each with a ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
