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 '%Hapuna Beach%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Aloha! The Red 'Ohana 3 bedroom is located on the beautifully scenic Kohala Mountain Ranch Road on the 'sunny side' of Waimea/Kamuela, above Mauna Kea and Hapuna Beach. Waimea is a quaint town with breezes and milder temperatures that provide an...
- 3 bedroom
- 2 bathroom
- Sleeps 8
HI> Kamuela
Aloha nui Loa! Minutes from Mauna Kea and Hapuna beaches, the Red 'Ohana 2 bedroom is located in Waimea is a quaint town with breezes and milder temperatures that provide an idyllic Hawaiian environment.
The ultimate in comfort and classic ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Kamuela
Located in an intimate gated community in the heart of Mauna Kea Resort, this oversized townhome boasts sweeping ocean views and easy walking distance to both the Mauna Kea and Hapuna Beach Prince Hotels and their incomparable white sand ...
- 4 bedroom
- 5 bathroom
- Sleeps 10
HI> Waimea
Enjoy luxurious beachfront living at B47, a gorgeous oceanfront 1-bedroom in one of the choicest locations at Hapuna Beach Residences. Located above the Big Island's nicest white sand beach, this newly constructed 1-bedroom offers privacy and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kamuela