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 (`propertyName` like '%Nihi Kai%') AND sleeps>=1 order by IF (ISNULL(`bedrooms`), 1,0), `bedrooms` desc LIMIT 0, 15
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
- 3 bathroom
- Sleeps 6
HI> 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
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
This bright and airy single level end unit is the largest Nihi Kai condo (1808 sf under roof) with 20 foot open beam, fan cooled ceilings in every room. New remodeled kitchen completed Sept. 2014. Bathrooms remodeled 2016. New hardwood ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
PLEASE NOTE: The Nihi Kai Villas will be under going construction with each building having the roof replaced. Our building will be closed August 30th through October 29th, 2021. Please submit your dates for details of construction during your ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Ocean and garden view 2Br 2Ba condo across from Poipu Beach in Kauai, Hawaii. Some of the best and safest swimming, snorkeling, surfing, and boogie boarding on the island. Restaurants and shopping within walking distance.
This is unit #809 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
