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 (`propertyName` like '%Bali Hai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Welcome to the North Shore Oasis!
Like the nicest hotels, the Oasis has a magical indoor/outdoor Hawaiian vibe. The open-air home let's you live among the tropical breeze. For your comfort, all three bedrooms have A/C.
The Oasis ...
- 3 bedroom
- 4 bathroom
- Sleeps 7
Hawaii> Princeville
Welcome. From the moment you enter the gate and are surrounded by the garden and pool you may never want to leave.
Walk inside your home away from home and enjoy. Take in the mountain view from upstairs from Namolokama unobstructed to ...
- 5 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Princeville
ALOHA! WELCOM TO KAUAI.
Tranquil and tropical setting on the edge of Hanalei Ridge. Three bedroom Duplex, that sleeps six in a small peaceful complex. All brand new, fully remodeled, clean, fresh, bright, and welcoming filled with the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Our Oceanfront, Mountain and Sunset view is THE most famous on all of Kaua'i!!!
Newly renovated with A/C throughout unit. 4 split Mitsubishi units: one in each bedroom , one in kitchen/dining area and one in living room/atrium ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Welcome to Hale Mahina! Long hidden as a luxury private residence, this Princeville pearl is now yours to enjoy. All one level, Hale Mahina offers unobstructed expansive views of the bejeweled Nanolokama Mountains and Bali Hai across Lake Mahina...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Princeville
1 Bedroom Condo with King in Master, Sleeper Sofa in Living Room. Full Kitchen/Balcony/Deck Washer/Dryer/Wireless Internet. No A/C - ceiling fans only
Located in the leading resort community of Princeville, Kauai, Wyndham Bali Hai Villas ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
1 Bedroom Condo with King in Master, Sleeper Sofa in Living Room. Full Kitchen/Balcony/Deck Washer/Dryer/Wireless Internet. No A/C - ceiling fans only
Located in the leading resort community of Princeville, Kauai, Wyndham Bali Hai Villas ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
1 Bedroom Condo with King in Master, Sleeper Sofa in Living Room. Full Kitchen/Balcony/Deck Washer/Dryer/Wireless Internet. No A/C - ceiling fans only
Located in the leading resort community of Princeville, Kauai, Wyndham Bali Hai Villas...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Welcome to Hale Honu Aloha ("Home of Turtle Love") at Puu Poa, an updated two-bedroom, two-bath condo on the Puu Poa property in Princeville. Overlooking the Pacific Ocean and directly adjacent to the Princeville Resort, it is the perfect ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Near the edge of a bluff overlooking the ocean, this ground-floor condominium offers fabulous ocean and coastline views. Bali Hai, the landmark mountain made famous in "South Pacific," is just one of the awesome sights that guests enjoy here. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
Welcome to Hale `Ikena Kai ("home of mystical ocean") at Pali Ke Kua, a unique home vacation experience on Kauai’s stunning North Shore!
This intimate one-bedroom condo at Pali Ke Kua is perched high on the Princeville ocean bluff and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
This Luxury Pool & Spa Estate has it all! Panoramic Views of Hanalei Bay, Lumahai Beach, Bali Hai, Mountains, Waterfalls, Rainbows, backing to hundreds of tropical acres and fruit trees! 5 Bedrooms, 5.5 Baths, Separate Living Room with 85" ...
- 6 bedroom
- 7 bathroom
- Sleeps 14
Hawaii> Princeville
Experience spectacular ocean views and dazzling sunsets, in your private luxury vacation residence. This condo is on the second level six steps down with ocean and mountain views. A/C in all rooms, both bedrooms and the open concept kitchen ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
(Please note that we refurnished our home in November 2023. These pictures reflect the new furnishings and decor. Also please note that we changed the beds in the guest bedroom from a queen to two twin-xl beds. This allows greater ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Kauai’s Most Breathtaking Views at Hanalei Bay Resort
Welcome to your Kauai paradise. Unit 9202 is located in the renowned Bamboo building which features the most stunning views of Hanalei Bay and Bali Hai.
This large fully air ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Kauai’s Most Breathtaking Views at Hanalei Bay Resort
Welcome to our North Shore paradise. Unit 9201/9202/9203 is located in the renowned Bamboo building which features the most stunning views of Hanalei Bay and Bali Hai.
This ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> 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 7
Hawaii> Princeville
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Upcoming Availability:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lovely and romantic, this popular honeymoon destination condo overlooks Bali Hai and Hanalei Bay. The room boasts a ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Princeville
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Upcoming Availability:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From the moment you walk into 4222 and see Bali Hai and Hanalei Bay directly in front of you, you will know you are in paradise. ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
From the moment you walk into 4221/4222 and see Bali Hai and Hanalei Bay directly in front of you, you will know you are in paradise. Enjoy the sunsets from the slate covered lanai and hear the waves of Hanalei Bay below. This 2 bedroom, 2 bath,...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
