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 '%Waikomo%') AND sleeps>=1 order by rating desc LIMIT 0, 30
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
Hawaii> Koloa
Sansei Hale (Third Generation House) is a luxury Hawaiiana vacation home. It overlooks the beautiful and private Waikomo Stream as it meets the ocean in secluded Turtle Cove, Poipu. Sansei Hale has three stream- and ocean-view bedrooms, and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Koloa
E komo mai, welcome to Waikomo Stream Villas 302!
This 1-bd/1-ba condo is on the ground floor with landscaped views in the heart of sunny Poipu. The fully equipped kitchen has been renovated with new cabinets, countertops, and stainless ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
WAIKOMO STREAMS 530 AT KOLOA KAI VACATION RENTALS
Welcome to Waikomo Stream Villas 530, where you can immerse yourself in the beauty of nature and the tranquil surroundings. This spacious condo overlooks lush greenery, beautiful old ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Strolling the grounds of Waikomo Streams Villa in Poipu is like walking through a fairy tale. A bamboo forest, brightly colored flowers, and a waterfall spilling into a serene pond where multi-colored koi swim are all waiting to be discovered on...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Koloa
WAIKOMO STREAMS 332 AT KOLOA KAI VACATION RENTALS
Welcome to this stunning two-bedroom, two-bath condo with distant ocean views and air conditioning in the downstairs bedroom and living room. Step into a beautifully designed space with ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
E komo mai, welcome to Waikomo Stream Villas 321.
This 1-bd/1-ba condo is on the second floor with landscaped views in the heart of sunny Poipu, minutes walk to nearby Kukui'ula Shopping Village.
Explore the tranquil grounds to see the ...
- 1 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> 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
Hawaii> 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
Hawaii> 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
Hawaii> 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
Hawaii> 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 offers gorgeous garden views.
With 1,050 square feet of ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
This warm and welcoming suite is a stylish accommodation with tropical appeal. Ocean-inspired decor and hardwood accents complete the open floor plan, while the cozy lanai offers gorgeous garden views.
With 1,050 square feet of ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
About this space
Waikomo Stream Villas is an intimate 3.9 acre resort consisting of beautiful tropical plants, a pool, and wading pool. Waikomo Stream Villas is located near beaches, dining, shopping and golfing.
Our condo is on the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> 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
Hawaii> 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
Hawaii> 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
Hawaii> 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
Hawaii> 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 6
Hawaii> 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
Hawaii> 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
Hawaii> Koloa
One bedroom with Cal King bed, new Queen sleeper sofa, new split AC, new washer and dryer, new paint and luxury vinyl flooring, full living area and kitchen private lanai space.
Recently updated with fresh paint and new luxury vinyl ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
This charming one-bedroom, plantation-style condo is a tropical retreat in sunny Poipu on the garden island of Kauai. Located near many of Hawaii's best beaches, restaurants and shopping, come stay and relax in paradise.
Indulge in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
WAIKOMO STREAM VILLAS 503 AT KOLOA KAI VACATION RENTALS
Waikomo Stream Villas is the perfect home away from home. Offering privacy and beautiful tropical views from every room, it is an oasis of lush gardens, meandering streams, and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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
Hawaii> 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
Hawaii> Koloa