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 (`headline` like '%Hanalei Bay Resort%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Amazing tropical ocean views on a private, ground floor unit at the Hanalai Bay Resort. Come experience this magical spot on Kauai’s North Shore on a 22 acre resort with Bali Hai ocean and sunset views. Hanalei Bay Resort brings the best of ...
2 bedroom
2 bathroom
Sleeps 6
HI> Princeville
Amazing tropical ocean views on a private, ground floor unit at the Hanalai Bay Resort. Come experience this magical spot on Kauai’s North Shore on a 22 acre resort with Bali Hai ocean and sunset views. Hanalei Bay Resort brings the best of ...
1 bedroom
1 bathroom
Sleeps 4
HI> Princeville
One of the best locations on Kauai’s lush North Shore fronting the white sands of Hanalei Bay with amazing views of tropical sunsets, lush green mountains and the sparkling blue waters of the Pacific Ocean. These Kauai condos are located on ...
1 bedroom
1 bathroom
Sleeps 2
HI> Princeville
Amazing tropical ocean views on a private, ground floor unit at the Hanalai Bay Resort. Come experience this magical spot on Kauai’s North Shore on a 22 acre resort with Bali Hai ocean and sunset views. Hanalei Bay Resort brings the best of ...
1 bedroom
1 bathroom
Sleeps 3
HI> Princeville
Covid-19 Guidelines:
Negative Covid test 72 hours prior to departure or upload your vaccinagion card and you are good to go! Please follow guidelines on Safe Travels Hawaii. There you will enter in your information and be directed to the ...
1 bedroom
2 bathroom
Sleeps 4
HI> Princeville
One of the best locations on Kauai’s lush North Shore fronting the white sands of Hanalei Bay with amazing views of tropical sunsets, lush green mountains and the sparkling blue waters of the Pacific Ocean. These Kauai condos are located on ...
3 bedroom
2 bathroom
Sleeps 6
HI> Princeville
Welcome to your Hanalei Bay Hideaway, at one of the best locations Hanalei Bay Resort has to offer. This suite includes amazing views of the tropical sunsets, lush green mountains and the sparkling blue waters of the Pacific Ocean. These condo's...
3 bedroom
3 bathroom
Sleeps 8
HI> Princeville
Luxury Vacation Rental, Three Bedroom + loft/3 bathrooms with Hanalei Bay View
Available in four configurations as a 1 bedroom main unit w/loft (4302) or as a 2 bedroom unit w/loft (43012 or 43023) or as a 3 bedroom unit w/loft (430123) for ...
3 bedroom
3 bathroom
Sleeps 10
HI> Princeville
One of the best locations on Kauai’s lush North Shore fronting the white sands of Hanalei Bay with amazing views of tropical sunsets, lush green mountains and the sparkling blue waters of the Pacific Ocean. These Kauai condos are located on ...
2 bedroom
2 bathroom
Sleeps 4
HI> Princeville
Hanalei Bay Resort unit 7101-2 is a privately owned and cared for two bedroom suite featuring countless amenities to please every guest. The condo features 1,324 square feet of spacious living area, with quality furnishings, with the living room...
2 bedroom
2 bathroom
Sleeps 6
HI> Princeville
NEWLY REMODELED Studio!!!
One of the best locations on Kauai’s lush North Shore fronting the white sands of Hanalei Bay with amazing views of tropical sunsets, lush green mountains and the sparkling blue waters of the Pacific Ocean.
These ...
0 bedroom
1 bathroom
Sleeps 2
HI> Princeville
NEWLY REMODELED Studio!!!
One of the best locations on Kauai’s lush North Shore fronting the white sands of Hanalei Bay with amazing views of tropical sunsets, lush green mountains and the sparkling blue waters of the Pacific Ocean.
These ...
0 bedroom
1 bathroom
Sleeps 2
HI> Princeville
Newly renovated in 2015!! 2 King Bedrooms plus a LOFT with 2 queen (new 2018) beds make it perfect for large families who want to get away. The living and dining areas are spacious and breezy so your whole family will feel comfortable. New ...
2 bedroom
2 bathroom
Sleeps 8
HI> Princeville
One of two full oceanfront views of Hanalei Bay with private lanais and grass lawn looking over the breathtaking views of Hanalei Bay and Bali Hai. Enjoy an amazing sunrise breakfast and cup of coffee as you overlook the bay as the sun wakes up...
3 bedroom
3 bathroom
Sleeps 8
HI> Princeville
One of the best locations on Kauai’s lush North Shore fronting the white sands of Hanalei Bay with amazing views of tropical sunsets, lush green mountains and the sparkling blue waters of the Pacific Ocean. These Kauai condos are located on ...
2 bedroom
1 bathroom
Sleeps 6
HI> Princeville
