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 (`headline` like '%Waikomo%') AND sleeps>=1 order by rating desc LIMIT 0, 15
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
This newly listed gem offers privacy and beautiful tropical views from every room. Waikomo Stream Villas is an oasis of lush gardens, meandering streams, and waterfalls. The perfect home base to enjoy walking access to some of the best ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
Waikomo Stream Villas is the best kept secret in the Poipu area. This lush garden resort on the sunny side of Kauai is in the most convenient location. Within walking distance you will find the best beaches, shopping, and multiple dining ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
This warm and welcoming suite is a stylish accommodation with family appeal. Ocean-inspired decor and hardwood accents complete the open floor plan, while the cozy lanai (porch) offers gorgeous garden views.
With 1,050 square feet of ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Poipu
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa