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 '%Hapuna Beach%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Location:
Nestled on the scenic Kohala Mountain Ranch Road, Red 'Ohana offers a serene escape on the 'sunny side' of Waimea/Kamuela, perched above Mauna Kea and Hapuna Beach.
Enjoy the quaint charm of Waimea, with its gentle ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kamuela
Location:
Nestled on the scenic Kohala Mountain Ranch Road, Red 'Ohana offers a serene escape on the 'sunny side' of Waimea/Kamuela, perched above Mauna Kea and Hapuna Beach.
Enjoy the quaint charm of Waimea, with its gentle breezes ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kamuela
This extraordinary, oceanfront, newly-constructed, third level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a magnificent chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
This unrivalled, oceanfront, newly-constructed, ground level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a magnificent chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
Relax in the luxurious comfort and privacy of this oceanfront, deluxe studio penthouse, just steps from the white sands of world famous Hapuna Beach. Enjoy spectacular sunset ocean views from the unique, four floor-to-ceiling radius glass ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> KAMUELA
This smart and chic, oceanfront, newly-constructed, second level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAMUELA
This beguiling, oceanfront, newly-constructed, ground level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, an exquisite chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 3 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Kamuela
This divine, oceanfront, newly-constructed, fourth level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a professional-grade chef’s kitchen outfitted with Wolf, Sub-Zero and Miele appliances, and ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This incomparable, oceanfront, newly-constructed, ground level penthouse offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a superb chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> KAMUELA
Enjoy effortless beachfront living at B32, the premier oceanfront 1-bedroom at the newly constructed Hapuna Beach Residences. B32 is located on the third floor and is the largest 1-bedroom available (882 sqft) with bonus square footage and an ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kamuela
This stylish, oceanfront, newly-constructed, third level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This marvelous, oceanfront, newly-constructed, second level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a luxe chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
Rare opportunity to rent an oceanfront condominium within the iconic Mauna Kea Resort. Hapuna Beach Residences C22 is a luxurious 2 BD 2 BA unit + bonus room. Consisting of 1,597 square feet of interior living area, and 1059 square feet of ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kamuela
This immaculate, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek, epicurean kitchen with Wolf and Sub-Zero appliances, wine storage and hand-crafted ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This magnificent, oceanfront, newlyconstructed, island masterpiece offers floortoceiling window walls that create indooroutdoor living space, a sleek, gourmet kitchen with Wolf and SubZero appliances, double ovens, a cappuccino machine, wine ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This sophisticated, oceanfront, island masterpiece offers floortoceiling sliding window walls that create indooroutdoor living space, a splendid kitchen with Wolf and SubZero appliances, wine storage and handcrafted cabinetry, plus a dining ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
New to market, A26 is a modern two-bedroom condo at the Hapuna Beach Residences offering the best beachfront location in Hawaii! Completed in 2022, this luxurious residence provides guests a front-row seat to gorgeous sunrises and the pristine ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kamuela
This superb, oceanfront, newlyconstructed, island masterpiece showcases floortoceiling sliding window walls that create indooroutdoor living space, a magnificent chefs kitchen outfitted with professionalgrade Wolf, SubZero and Miele appliances, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
Enjoy unobstructed beachfront views in this modern, luxurious two-bedroom unit decorated with Restoration Hardware furniture throughout. Overlooking the white sandy beach, the living and master bedrooms feature floor-to-ceiling sliding glass ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Breathtaking oceanfront penthouse on Hawaii's picturesque Hapuna Beach! This modern vacation home in the newly-constructed Hapuna Beach Residences features a coveted floorplan, with triple ocean frontage on the penthouse level where one can take...
- 3 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Kamuela
Stylish, island-modern residence housed within the renowned Mauna Kea Resort area overlooking the beautiful Hapuna Beach. Just steps to the ocean as well as amenities to allow for easy access to play, dine, unwind, and connect. Available access ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
Escape to paradise at Hapuna Beach Residences Unit C34, a luxurious 2-bedroom, 2-bathroom oceanfront condo at Hapuna Beach Resort on Hawaii's Big Island. This high-end, premium beachfront vacation rental features stunning ocean views, designer ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Kamuela
Elevate your Hawaiian island getaway at Hapuna Beach Paradise™, a luxurious beachfront retreat on the Kohala Coast.
This penthouse residence will mesmerize you by its stunning, unobstructed views of Hapuna Beach, the best white sand ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Hawaii County
Welcome to Hapuna Beachfront Bliss at Hapuna Beach Residences brought to you by Paradise in Hawaii Luxury Homes
The shimmery Pacific Ocean beckons you from the moment you wake. Enjoy vibrant sunsets over the ocean each evening. Steps away...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
This island contemporary, oceanfront, newly-constructed, second level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a gourmet kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
Where extraordinary meets island luxury, this newly-constructed, top floor, beachfront residence offers floor-to-ceiling sliding pocket doors that transform the great room into a lanai; an epicurean kitchen with professional-grade Wolf, Sub-Zero...
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kamuela
This magnificent, oceanfront, newly-constructed island masterpiece offers floor-to-ceiling window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele appliances, wine ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAMUELA
This discerning, oceanfront, newly-constructed, ground level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This exceptional, oceanfront, newly-constructed, fourth level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a luxe chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This refined, oceanfront, newly-constructed, third level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAMUELA
This impeccable, oceanfront, newly-constructed, ground level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a true chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> KAMUELA
This delightful, oceanfront, newly-constructed, second level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This island luxe, oceanfront, newly-constructed, ground level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a fantastic chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> KAMUELA
This incomparable, oceanfront, newly-constructed island masterpiece seamlessly fuses the blissful convenience of ground-floor beach access with exceptional privacy and size. This residence showcases floor-to-ceiling sliding window walls that ...
- 5 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kamuela
This contemporary, oceanfront, newly-constructed, fourth level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> KAMUELA
This oceanfront, newlyconstructed, groundlevel masterpiece offers floortoceiling sliding window walls that create indooroutdoor living space, a magnificent chefs kitchen with Wolf, SubZero and Miele appliances, including double ovens and a wine ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> KAMUELA
This exclusive, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek, luxe chef’s kitchen outfitted with professional-grade Wolf and Sub-Zero ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> KAMUELA
This delightful, oceanfront, newly-constructed, top floor studio offers a floor-to-ceiling sliding window wall that creates indoor/outdoor living space, a chef’s kitchen outfitted with premium Wolf and Sub-Zero appliances, wine storage and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This brilliant, oceanfront, newlyconstructed, ground level island masterpiece offers floortoceiling sliding window walls that create indooroutdoor living space, a stateoftheart chefs kitchen outfitted with professionalgrade Wolf, SubZero and ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> KAMUELA
This ultra-refined, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls for indoor/outdoor living, a sleek, luxe chef’s kitchen outfitted with Wolf and Sub-Zero appliances, double ovens, wine storage,...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
This opulent, oceanfront, third level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, an epicurean kitchen outfitted with professional-series Wolf and Sub-Zero appliances, including double...
- 4 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> KAMUELA
This ultraluxe, oceanfront, newlyconstructed, island masterpiece seamlessly fuses the blissful convenience of groundfloor beach access with exceptional privacy and space. This residence showcases floortoceiling sliding window walls, a ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> Kamuela
This magnificent, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, an exquisite kitchen outfitted with professional-series Wolf and Sub-Zero appliances, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This remarkable, oceanfront, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a decadent kitchen outfitted with professional-series Wolf and Sub-Zero appliances, as well as a dining table ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> KAMUELA
This discriminating, oceanfront, second floor, island masterpiece offers floortoceiling sliding window walls that create indooroutdoor living space, an epicurean kitchen with Wolf and SubZero appliances, wine storage and handcrafted cabinetry, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
This sensational, oceanfront, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space and is designed with contemporary decor and colorful artwork throughout., A beautiful galley kitchen is ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kamuela
Hapuna Beach Paradise at Hapuna Beach Residences brought to you by Paradise in Hawaii Luxury Homes
HAPUNA BEACH PARADISE will mesmerize you by its unobstructed views of Hapuna Beach, rated #1 USA beach by Forbes 2021 and Trip Advisor ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kamuela
This oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space a gourmet kitchen with Wolf and Sub-Zero appliances, custom cabinetry and lighting, plus a dining table ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
This marvelous, oceanfront, fourth floor, newly-constructed island masterpiece offers sliding glass pocket doors that create indoor/outdoor living space, a superior kitchen outfitted with professional-grade Wolf and Sub-Zero appliances, a wine ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
