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 (city = 'Hanalei' OR postalCode = 'Hanalei' OR region like '%|Hanalei|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
All travelers are responsible for investigating all travel restrictions to Kauai. Travelers are advised that travel restrictions to Kauai may be different than travel requirements to the State of Hawaii.
TVNCU-1104 - TA# 154-325-1968-01
...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Hanalei
All travelers are responsible for investigating all travel restrictions to Kauai. Travelers are advised that travel restrictions to Kauai may be different than travel requirements to the State of Hawaii.
TVNCU-1226 TA #189-999-3088-01 ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Hanalei
All travelers are responsible for investigating all travel restrictions to Kauai. Travelers are advised that travel restrictions to Kauai may be different than travel requirements to the State of Hawaii.
TVNCU#1059 - TA# 121-109-0944-01
This ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Hanalei
This beautiful beachfront Hanalei Bay home is located steps from the sand on Kauai's majestic North Shore. Enjoy the best views of the pier, surf, mountains, and sunsets over Mt. Makana the north shore has to offer. This modern updated cottage ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Hanalei
A classic Hawaiian style home, this Haena beach house, known as Hale Pulelehu'a (The Butterfly House), is a wonderful place for your family & friends to experience the island lifestyle on the North Shore of Kauai. Immaculately cared for, Island...
- 4 bedroom
- 3 bathroom
- Sleeps 8
HI> Hanalei
Aloha,
You will love our 3 bedroom 3 bath home located in the heart of Hanalei Bay. Mountains and waterfalls viewed from the four beautiful lanai's take full advantage of the outdoor living space. Our home is a quick neighborhood stroll or bike...
- 3 bedroom
- 5 bathroom
- Sleeps 12
HI> Hanalei
Hanalei Hideaway...TVNCU# 5112
This classic Hawaiian-style beach cottage is located in the heart of picturesque Hanalei Bay, Kauai - only a short walk to the beach and just two blocks to Hanalei Village. This charming pole house offers a front ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Hanalei
Please inquire about The State of Hawai‘i Safe Travels Hawai‘i program and the requirements for traveling to Kauai as it does change.
Recapturing bygone days, the completely renovated 2 bedroom 1 bath "Palaka" Vacation Cottage draws one ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Hanalei
Step back in time in this charming cottage by the beach. Built in the 1920's, this plantation style home will give you a true Hanalei experience. Just steps to the beach and a short stroll or bike ride to many restaurants and shopping. This is ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Hanalei
Hanalei Makai Hale ... TVNCU #1157
Wake up in a Hanalei postcard at this extraordinary 3-bedroom, 2-bathroom beach home.
You may think you’re still dreaming when you open your eyes to picture-perfect views of Hanalei’s lush greenery and ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Hanalei
This modern beachfront home was recently fully renovated. Hale Ekahi (One) is one of two properties on this beachfront lot. Ekahi offers modern beach furnishings to fill this luxurious beachfront home. The home has 3 bedrooms, 2.5 bathrooms, a ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Hanalei
Enjoying a prized location among the closest legal vacation rentals to picturesque Tunnels Beach, Hallor Hale is situated on a quiet cul-de-sac, surrounded by a large lawn and tropical vegetation. Within this tranquil setting on Kauai's ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Hanalei
Hawaiian Island Beachfront cottage with an incredible location on the North Shore of Kauai in between Hanalei and Haena close to Tunnels. The private 3/4 acre property features a large sandy beach point. Some of the islands best snorkeling and ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
HI> Hanalei
Please inquire about The State of Hawai‘i Safe Travels Hawai‘i program and the requirements for traveling to Kauai as it does change.
Umetsu is quite possibly one of the most idyllic cottages in Hanalei. This one bedroom, one and one half ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Hanalei
Hanalei Waterfall House - TVNC-5120
Ideal retreat in the heart of Hanalei. Great for families and groups – whether two families, multiple couples, or a getaway with friends – or even just two or three of you who want room to spread out. ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Hanalei
