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 '%Pali Ke Kua%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Beautiful Princeville Pali Ke Kua condo perched on a cliff overlooking Bali Hai, with unobstructed views of the ocean, sunsets, waterfalls, and rainbows. Turtles can be seen swimming in the cove below. December through April whales can be seen ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Escape to Pali Ke Kua 108 and enjoy ocean views and a relaxing environment in this ground floor condo.This bright and sunny condo has an island style, open concept living room, dining room and kitchen, all with beautiful panoramic ocean views. ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Welcome to Pali Ke Kua 207 by Gather in Princeville, located on the beautiful North Shore of Kauai. This 2-bedroom, 2-bathroom condo, ideal for couples and small families, offers a comfortable and recently updated stay with breathtaking views of...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Absolutely the best location in Princeville...can you say Paradise! Stunning Ocean views!
The condo is beautifully decorated with high end furnishings to help you relax as you take in the outrageous views and crashing surf below. This ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
1 bedroom, 1 bathroom corner unit offering a cozy bamboo motif that resonates with old Hawaii and when times were simple. The idyllic getaway from the fast-paced crowds, and ultra-modern world of hotel rooms. Ocean views from every room. Curl up...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
Pali Ke Kua #141, Princeville
Panoramic ocean and mountain views await you at Pali Ke Kua #141.
Spectacular Bali Hai sunsets and seasonal whale watching are all steps away from this 2-bedroom, 2-bath ground-floor unit. The ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Pali Ke Kua #242, Princeville
Ideally located above the edge of the striking ocean bluffs of Princeville, Pali Ke Kua #242 has all of the features you could want in your island getaway with the benefit of stunning ocean views.
The...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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
Pali Ke Kua #222, Princeville
Ideally located above the edge of the striking ocean bluffs of Princeville, Pali Ke Kua #222 has all of the features you could want in your island getaway with the benefit of stunning ocean views.
You...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Near the edge of a bluff overlooking the ocean, this special condominium is a favorite with many guests who return each year. Comments in the guest book reflect how much they enjoy vacationing here in their "home away from home" and some have ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
Location and luxury with a spacious interior (renovated in 2021), high-end furnishing/appliances and large bedrooms/bathrooms.
Well-equipped kitchen and additional amenities, including high-speed internet, 80" TV, and air conditioning in ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
This beautiful ground floor condo has undergone a COMPLETE REMODEL!
The beautiful remodel features Quartzite counters. All new cabinets, Kitchen Aid stainless steel oven and dishwasher, along with a Café refrigerator. Beautiful new ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
This ocean cliff condo has views of the ocean from both patios off of the living room and the master bedroom. The dining room is surrounded by three windows with views of the ocean and boasts a fully equipped kitchen and laundry facilities. The ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Pali Ke Kua #102
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
We invite you to be our guests in our “Iki Māhele O Ka Lani…Little Slice...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Pali Ke Kua #12, Princeville
Pali Ke Kua #12 is a 2 bedroom/2 bath condo, located in a park-like setting on the tropical grounds of the Pali Ke Kua complex in the residential and prestigious resort community of Princeville.
Open ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Pali Ke Kua 113
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
This is what you think of when you think of Kauai: beautiful sunsets over the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
Perched on an ocean bluff near Hanalei Bay, Pali Ke Kua Unit #241 is a one-bedroom + loft, two-bath, second-floor unit with sweeping ocean views.
As of July 2021, there are 3 A/C portable units in the condo. Please note this condo does ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
This two-bedroom condo is located in the resort community of Princeville which overlooks the Pacific Ocean. The vaulted ceiling in the living area lends a spacious and airy feel to this nicely appointed condo.
You can take in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Pali Ke Kua is a 22-acre resort condominium project located on the lush north shore of Kauai, nestled in the upscale and private neighborhood of Princeville. It sits on a bluff 200 feet above the ocean and has breathtaking views of the north ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Pali Ke Kua 201 is an oceanfront condo with a master bedroom plus loft suite. The condo will be newly renovated beginning February 2025!
LOCATION! LOCATION! LOCATION! Welcome to Pali Ke Kua #201, a stunning corner unit which showcases ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Stunning panoramic views of the Pacific ocean and Mt. Makana (“Bali Hai”) at this spacious condo on the Northshore of Kaua'i in Princeville. This island condo is located near all the attractions the famous north shore offers. Enjoy the pool ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
