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, 50
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, ...
4 bedroom
2 bathroom
Sleeps 7
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
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
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
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
3 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
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,...
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Haena
Hale Hina, Haena
3 bedroom
4 bathroom
Sleeps 7
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 plantation style beachfront home features spectacular ocean views from the Lanai. Experience the leisure of relaxing on the lanai ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Haena
Hawaii Life Presents Hale Haena - Your private oasis on the stunning North Shore of Kauai. Take in awe-inspiring mountain and peekaboo ocean views, and follow a short private path less than 100 steps to one of the most beautiful beaches on ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Haena
Haena Place is a well appointed vacation rental home situated at the edge of the world-famous Napali Coast and some of Kauai's best beaches! Surrounded by lush tropical greenery and turquoise views of the Pacific Ocean!
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Haena
Your personal beachfront oasis on Kauai’s north shore offers you a truly special experience. With unobstructed ocean views from the home, beautiful sunrises, sunsets, and even the frequent rainbow can be enjoyed throughout the year. The beach ...
2 bedroom
2 bathroom
Sleeps 4
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 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
3 bathroom
Sleeps 8
Hawaii> Haena
Enjoy the very best at Hale o' Mahina at Haena (The House of the Moon at Haena) This upgraded home with koa wood and bamboo accents, is nestled on a quiet street with ocean and mountain views. Step outside your door and walk to one of the most ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Haena
Welcome to Hale 'Oli, the House of Happiness. You’ll fall in love with this serene island escape on the north shore, nestled between the ocean and mountains. The absolute perfect location for that romantic getaway or a memorable family ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Haena
Welcome to Hale Haena Kai!
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Haena
