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 (`headline` like '%Kamalii%') AND sleeps>=1 order by rating desc LIMIT 0, 30
This bright, spacious three-bedroom, three-bathroom townhome is located at Villas of Kamalii, in the prestigious resort community of Princevile. Perched on the highest part of world-famous Makai Golf Course, enjoy stunning mountain and waterfall...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Nestled in the prestigious Villas of Kamalii within Kauai's renowned North Shore, this exquisite 3-bedroom, 3-bath villa offers unrivaled proximity to some of the island's most cherished attractions. Just moments away from the iconic Hanalei Bay...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Enjoy relaxed elegance and serene mountain views at the lovely Villa of Kamalii #35! This spacious three-bedroom, three-bath townhouse-style condo in Villas of Kamalii provides the perfect setting for a tranquil Hawaiian getaway.
Villa ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
The Villas of Kamali`i at Princeville is a luxury townhouse style resort located at Princeville Resort on Kauai’s lush North Shore. Very similar in design to the Villas on the Prince, these two and three bedroom townhouse style units are ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Designed for comfort and peace. Take in the peaceful, private views of MULTIPLE WATERFALLS & RAINBOWS. Outfitted with a fully-equipped kitchen and nearby Gas Grills. Steps away from the Heated Pool and Delightful Jacuzzi. A fun floorplan: 2 ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
The Villas of Kamali`i at Princeville is a luxury townhouse style resort located at Princeville Resort on Kauai’s lush North Shore. Very similar in design to the Villas on the Prince, these two and three bedroom townhouse style units are ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
The Villas of Kamali`i at Princeville is a luxury townhouse style resort located at Princeville Resort on Kauai’s lush North Shore. Very similar in design to the Villas on the Prince, these two and three bedroom townhouse style units are ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Tucked in the resort community of Princeville are the Villas of Kamalii, a gated complex located across from the Makai Golf Club. Enjoy lush garden, mountain and waterfall views from Villa #23, an end unit, split-level townhouse with over 1,800 ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Welcome to our delightful 2-bedroom townhouse located in the Villas of Kamalii, a Kauai vacation rental nestled in the center of scenic Princeville. This split-level unit offers a spacious and comfortable retreat, with charming features and a ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Princeville
