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 '%Sunset Kahili%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Watch stunning sunsets and the ever-changing ocean from our recently refurbished, bright, and airy unit at Sunset Kahili. This clean and comfortable open-area corner unit has an upgraded kitchen and bathroom with granite counter-tops; ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 304
Sunset Kahili 101 is a remarkable two bedroom condo located in the heart of Kauai's premier resort destination, the stunning south shore of Poipu. Nestled in close proximity to world-class beaches, this nearly oceanfront property offers an ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
A spectacular ocean view from the living room, bedroom, balcony, air conditioning and a recently upgraded kitchen are a few of the features that you enjoy while staying in your paradise getaway. The fully-equipped kitchen was just renovated in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
This gorgeous Sunny South Shore home is tastefully remodeled with a spectacular ocean view. You'll want to soak in the breathtaking sights... and simply exhale.
Along with a lovely, high end condominium community, Sunset Kahili offers a ...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Koloa
This spacious two bedroom Sunset Kahili condo with an expansive ocean view is decorated with tasteful island decor throughout. Bedrooms are comfortably furnished and condo includes a full kitchen complete with stove, oven, refrigerator, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Poipu
Nestled in a quiet neighborhood, this boutique condo offers breathtaking ocean views. Sunset Kahili is the perfect retreat in Poipu on the beautiful island of Kaua'i.
This stunning unit boasts thoughtfully designed tropical décor and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa