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 (`uniturl` like '%a2b476ed-3eb3-496c-ac24-476df95390a6%' ) AND sleeps>=1 order by rating desc LIMIT 0, 15
TVNC 1258
Haena Beach House offers a once-in-a-lifetime vacation experience, providing contemporary luxury in a private beachfront setting. Ideal for large groups, the home showcases breathtaking panoramic views of the Pacific from its living spaces and each of its bedrooms.
The stylish living area, dining room, and gourmet kitchen at Haena Beach House feature fine furnishings and quality modern finishes. Each of the sumptuous bedrooms has its own full bath for optimal privacy, and two of the suites boast wet bars and lounge areas. Amenities such as wireless internet ensure that guests enjoy all the comforts of home during their stay. This property is fully equipped with an exterior home surveillance system for your safety and security.
A lovely lanai off the dining room creates a perfect spot for sipping your morning coffee while watching the waves roll in. An additional covered lanai downstairs provides a dining area for leisurely open-air meals, as well as comfortable outdoor sofas where you can plunge into a good book. Lounge chairs on the upstairs lanai also offer the opportunity to soak in the warm Hawaii sun.
From this exceptional Haena home, guests can easily access many north Kauai attractions, such as hiking the Kalalau Trail, snorkeling at Tunnels Beach, and taking in the stunning scenery at Lumahai Beach. A short drive also leads to magical Hanalei Bay, opportunities for kayaking and stand-up paddling on the Hanalei ...
- 5 bedroom
- 5 bathroom
- Sleeps 10
HI> Hanalei
