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 '%Nihi Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
One of the best locations in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over 300 yards to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. The plantation style ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
This large 1400 square-foot, two-bedroom, single level, beautifully furnished condo has spectacular ocean views from the front lanai, living room, master bedroom, and kitchen. This third-floor unit has both ocean and mountain views and is a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
NIHI KAI 802 AT KOLOA KAI VACATION RENTALS
Welcome to Nihi Kai 802, a newly remodeled condo with one of the most convenient locations in Poipu. Situated high up on the hill leading to Poipu Crater, this property offers amazing ocean views...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An ideal location in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over five minute walk to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. Unit 403 is just over 300 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Welcome back to paradise!!
This beautiful 2-bedroom 2-bath home is located at Nihi Kai Villas, one of the nicest resorts on Kauai's south shore. Beautifully decorated, immaculately maintained, and offering one of the loveliest garden ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
NIHI KAI 203 AT KOLOA KAI VACATION RENTALS
Welcome to Nihi Kai 203, where convenience meets luxury in the heart of Poipu! Step into this remodeled condo and be transported to a truly immersive Polynesian atmosphere. The stunning vaulted ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Welcome to koolkondo where you will experience luxury vacation rentals at its finest. Our stylish boutique hotel style condos and homes have it all. You will absolutely enjoy your home away from home with VIP concierge style services in modern ...
- 2 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Koloa
NIHI KAI 200 AT KOLOA KAI VACATION RENTALS
Welcome to Nihi Kai Villas 200, where you'll find one of the best locations overlooking the beautiful pool below and the stunning Poipu Coastline. This small island oasis is just steps away from ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Experience the epitome of family-friendly vacationing at Nihi Kai 806, just a stone's throw away from the pristine Poipu Beach and Brennecke's Beach. Embark on a leisurely five-minute stroll to indulge in swimming, snorkeling, and boogie ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Experience Island Paradise at Nihi Kai 507 in Poipu, Kauai!
Relax and unwind in this professionally decorated and remodeled, well-appointed, highly sought-after 2BR/2BA split-level condo. Equipped with AC in both bedrooms and all living...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
The absolute best location on Kauai, with views of South Coastline to Ni?ihau and unforgettable sunsets! This spacious, fully furnished 2BR/2BA apartment located on the majestic South Shore of Kauai is just steps from to shopping, dining, and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Po'ipu
One of the best locations in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over 300 yards to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. The plantation style ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
NIHI KAI 509 AT KOLOA KAI VACATION RENTALS
Nihi Kai is a stunning resort located in the heart of Poipu, offering an unbeatable location just a short walk away from Brennecke's and Poipu's beaches. Spend your days basking in the sun and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
One of the best locations in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over 300 yards to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. The plantation style ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
You will feel right at home in this 2-bedroom ocean view unit located at one of the South Shore’s finest resorts
- Nihi Kai Villas! From the sizable lanai off the living room and dining room, you can see the splash of waves
hitting the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Stellar location just steps from the ocean! Have fun on the ocean view pool deck by day and watch beautiful sunsets in the evening! At Nihi Kai 601, enjoy a bright, warm, and welcoming villa within this oceanfront tropical garden resort. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
NIHI KAI 510 AT KOLOA KAI VACATION RENTALS
Nestled in one of the prime locations of sunny Poipu Beach, Kauai, Nihi Kai Villas Resort offers an idyllic retreat just steps away from the pristine Poipu shoreline. A mere 300 yards from the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
