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 = 'haena' OR postalCode = 'haena' OR region like '%|haena|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Kauai's North Shore has been a frequent backdrop for countless movies due to its incredibly lush beauty and phenomenal beaches. Nestled within this gorgeous, pristine expanse, lies Haena Kai. Set back just thirty paces from a soft, deserted, ...
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Haena
This is a legal permitted vacation rental. Permit #TVNC-5151.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Haena
Hale Koolau Estate (Main House + Treehouse), Haena
4 bedroom
4 bathroom
Sleeps 14
Hawaii> Haena
Makana Akua is approximately 1,700 square feet with over 1,000 square feet of decks. Beachfront near Tunnels Beach and Tunnels Reef in Haena, it is truly a tropical rainforest. This home offers two master suites with private granite bathrooms, ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Haena
This sleek and modern, beautifully appointed 2 bedroom beachfront home is located on one of the most beautiful beaches in the world. Impeccable decor, combined with expansive panoramic ocean views make this home a sanctuary for enjoying Kauai's ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Haena
Kolohala in Haena is a peaceful place to enjoy your tropical vacation. Great for honeymooners! It faces a beautiful meadow with horses and cows. You can walk to a string of white sand beaches in 5 mins. Close to Ke'e beach, Tunnels beach, ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Haena
Elegant and spacious King Hale, set beachfront in a lush tropical jungle, is located in the serene community of Haena, just 4 miles north of Hanalei. Here you will discover beautiful stretches of white sand beaches and the most breathtaking of ...
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Haena
This cozy and comfortable Hawaiian style redwood home is just a short walk to miles of sandy beach. The house is surrounded by lush, tropical gardens. Two lanais let you enjoy paradise in comfort and privacy. A wonderful place to get away and ...
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Haena
Live the ultimate Kauai experience in this luxe and spacious 3-bedroom, 2-bath airy tropical house.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Haena
Enjoy the very best at Ha'ena Place, one of the North Shores hidden gems! Tucked away on a quiet cul-de-sac, and a short walk to Kauai's best beaches, this private home has everything you need for the perfect island escape! Iconic surf and ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Haena
A short walk to world-class, white sand beaches stretching for miles in both directions. Experience the beauty of a magical area nestled between emerald mountains and azure seas. Stroll in the sand on the closest beach, Naue. Walk or drive one...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Haena
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Haena
On Kauai's North Shore located in Ha'ena. As with all properties from Hanalei to the end of the road at Ke'e Beach, this property is in a tsunami evacuation zone This all redwood home designed by world class yacht racer and sailing enthusiast,...
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Haena
Hale Ko'olau, Haena -- Permitted Vacation Rental Property - Permit ID: TVNCU #1017
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Haena
Hale Ko'olau Treehouse, Haena
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Haena
Come experience the beautiful Garden Island of Kauai at Hale Kahakai; which means House by the Seashore. This beachfront home features spectacular ocean views from the Lanai. Experience the leisure of relaxing on the lanai listening to the sound...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Haena
Lush, tropical foliage and spacious grounds surround this delightful, beachfront home. It exemplifies the tranquillity for which Kauai's north shore has long been valued. Vaulted ceilings add a spacious feel to the large living area which offers...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> HAENA
Hale Hina, Haena
3 bedroom
4 bathroom
Sleeps 7
Hawaii> Haena
Hale Alealea Beach House, is one house back from Kepuhi Beach. This home is situated on a large, private street-to-street lot in Haena's prime neighborhood made up of unique, well manicured beach front/adjacent homes. Our 15000 square foot yard ...
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Haena
TVNC #4291
4 bedroom
5 bathroom
Sleeps 9
Hawaii> Haena
