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 '%Wai\'ula\'ula%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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
HI> Kamuela
This 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 Sub-Zero, Fisher & Paykel and Wolf appliances/gas range, dual master suites ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> 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 6
HI> Waimea
The Ridge at Wai'ula'ula - Unit F102 FREE resort access to both the Mauna Kea Beach Hotel and The Westin Hapuna Beach Resort Welcome to the beautiful Hale Hinano at Wai'ula'ula on the majestic Mauna Kea Beach Resort- with breath taking views of ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> 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
HI> Kamuela
Welcome to Wai‘ula‘ula at Mauna Kea---a private, gated community on the Hapuna Golf Course.
Relax in our 3-bedroom, 3-bathroom, 2,100 sqft. condominium and enjoy the many upgrades we’ve added to our home, including hardwood floors, new ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Waimea
**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 ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Waimea
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
HI> 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
HI> Kamuela
Welcome to Wai'ula'ula at the Mauna Kea Resort! Your vacation will truly be one to remember in this beautifully decorated condominium in the gated community of Wai'ula'ula, located on the Hapuna Golf Course. This recently built luxury ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Waimea
Located only a short drive away from the Big Island's best sandy beaches, Wai'ula'ula Ridge N102 is an upgraded 3 bedroom, 3 bathroom ground-floor condo that offers privacy and easy access to the best sights on the Kohala Coast. This modern ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kamuela
This private, ground-level, oceanview residence offers a great room with sliding glass pocket doors for indoor/outdoor island living, a chef’s kitchen outfitted with Sub-Zero, Fisher & Paykel and Wolf appliances/gas range and dual master ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> 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
HI> 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
HI> Waimea
This luxe, ‘upcountry’, oceanview residence offers a great room with sliding glass pocket doors for indoor/outdoor island living, a chef’s kitchen outfitted with Wolf and Sub-Zero appliances, including a double oven and wine refrigerator, ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Waimea
