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 (city = 'Hanalei' OR postalCode = 'Hanalei' OR region like '%|Hanalei|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Capture the magic of this romantic cottage. Large picture windows, open beam ceilings and sky lights bring views of lush tropical gardens with towering Cook pines. Two covered lanais for pupus, mai-tais and outside dining. After an ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hanalei
Totally remodeled, updated - all new furniture, fixtures, finishes.
TVNC - 5119
This vacation rental is located in Hanalei which is in the tsunami evacuation zone.
The house is in the quintessential beach town of Hanalei. ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hanalei
Just across the street from the sand, Hale Hoku is a 5 bedroom/4 bathroom beach cottage with a sunny and open floor plan. Built up on post and pier, it is as if you are residing in your own private and breezy treehouse!
Three master ...
- 5 bedroom
- 4 bathroom
- Sleeps 12
Hawaii> Hanalei
Welcome to your dream retreat in Hanalei, Kauai, nestled on the iconic Weke Road mere moments away from renowned surf spots like Pinetrees, Hanalei Pavilion, and Hanalei Pier. This two-story haven boasts four bedrooms and three full baths, ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Hanalei
Wake up in a Hanalei postcard setting at this extraordinary 3-bedroom, 2-bathroom beach home.
You may think you’re dreaming when you open your eyes to picture-perfect views of Hanalei’s lush greenery and vivid blue skies outside your...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hanalei
Indulge all your senses from this ocean-view, 3-bedroom, 2-bath Hanalei beach home.
If you were any closer to the waves of Hanalei Bay, you’d need flippers. You’ll hear the surf from every room in this three-bedroom, two-bath beach ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hanalei
Located in the quaint and historic beach community of Hanalei Bay, this comfortable Kauai vacation home offers its guests a spacious and relaxing place to settle in and enjoy beach life. Hale O’Kale is walking distance to all of Hanalei town ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hanalei
Come to the charming town of Hanalei and experience the Jones House, a spacious oceanfront home on spectacular Hanalei Bay. Steps away from the beach with breathtaking views of Hanalei Bay, the Jones House is sure to delight. Swim, surf, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Hanalei
Hokulele Hale is a three-bedroom two bath single family home.
This home is located at the end of a quiet cul-de-sac just 125 yards from the famous Hanalei Bay Beach. The property is set back from the street on a flag lot and is adjacent to ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Hanalei
Upgraded in 2019, Hale Ka Mahina isn't your average vacation rental, Hale Ka Mahina is tucked away on a side street between Hanalei Bay and Hanalei town. Easily walk or ride bikes to the beach and town when you want to check the surf or grab a ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Hanalei
We've provided all the comforts of home on 'The Garden Island' of Kauai, one of the world's most spectacular settings. This charmingly furnished house, with traditional Hawaiiana decor and lanais, is surrounded by a lush tropical garden. The ...
- 5 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Hanalei
Note: Our transient vacation home is located in a Tsunami Evacuation Zone. In an event of a Tidal Wave, evacuate to higher grounds. We also incur occasional bridge closer due to flooding of Hanalei roads. Access to our vacation home may be ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hanalei
If you are staying on Kauai there is not a better location then right in Hanalei. Voted one of the top beaches in the world it truly is one of the most beautiful places you will ever visit. As for homes in Hanalei, you will not find any home ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Hanalei
This newly remodeled beach house is a minute walk to the beach. It revolves around a great room of kitchen, living room and dining room. It's perfect for eating, drinking and hanging out after a day at the beach. There is also a very large ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Hanalei
TVNCU number # 5150
Wailele Ikena (The Simpson House)... Hanalei Bay, Kauai, Hawaii. Hanalei has it all! Breathtaking views of dozens of cascading waterfalls, sheer cliff, ancient volcanic mountains covered with vegetation, and one of the ...
- 5 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Hanalei
*TVR Registration #TVNC 5132 *
*Take a MUST SEE DRONE TOUR for the whole experience... https://youtu.be/9anBVNOjN-A
Welcome to The Guest House at River Estate….
The River Estate...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Hanalei
Mana Kauai Island Vacations Presents Holo Makani!
The gated estate of Holo Makani is located in the sand on a dramatically beautiful and pristine beach on Kauai's North Shore coast line. Enjoy the lull of the ocean and the gentle trade winds...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Hanalei
Exclusive Luxury Beachfront Home located around the point from Tunnel’s Beach.
More secluded not so populated gorgeous beach which is just a beach walk from Tunnels.
Imagine an island, a lush green paradise in the far-off reaches of ...
- 5 bedroom
- 6 bathroom
- Sleeps 12
Hawaii> Hanalei
Mana Kauai Island Vacations presents the charming Palaka Cottage!
Recapturing bygone days, the completely renovated 2 bedroom 1 bath "Palaka" Vacation Cottage draws one back to the days of the sugar industry's plantation cottages. With ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Hanalei
NOTE: This transient vacation home is located in the tsunami evacuation zone.
TVNC-1198 / TMK: 5-5-01002067R-4/ Tax ID# TA-140-845-6704-01/TA-005-027-2256-01/GE-140-845-6704-01/GE-005-027-2256-01: Walking distance to beautiful Hanalei Bay...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hanalei
