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, 50
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
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Please note: Construction is ongoing at Waikomo Streams as they replace water lines throughout the property. Although most of the loud noise has diminished, excavators will still be working outside the units. The work is expected to continue for...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Welcome to Waikomo Stream Villas unit 100, your tropical gem in the heart of Poipu! Nestled in lush greenery and surrounded by vibrant flora, our one-bedroom condo is the perfect escape for those seeking tranquility and natural Hawaiian ...
- 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
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
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
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
Escape to paradise with our limited-time offer! Book 5 nights and enjoy the 6th night absolutely FREE! Valid for new reservations from 11/8/24 - 12/14/2024.
Welcome to Waikomo Stream Villas 422! Escape to your very own tropical paradise ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Recently updated with fresh paint and new luxury vinyl flooring. New photos coming soon!!!
Waikomo Stream Villas is a quaint little resort located between the Kukuiula Shopping Center and Koloa Landing in the heart of Poipu.
It has ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Welcome to Waikomo Streams 121 by Gather. 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 ...
- 2 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
Waikomo Stream Villas 322
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Find island-inspired luxury, every modern comfort, and a gorgeous ...
- 3 bedroom
- 1 bathroom
- Sleeps 7
Hawaii> Koloa
WAIKOMO STREAMS VILLAS 103 AT KOLOA KAI VACATION RENTALS
Waikomo Stream Villas, one of the best-kept secrets in the Poipu Beach Resort area, offers a serene and lush garden setting with 60 charming Kauai condos. Located on the sunny side ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Conveniently located on the South Shore, this two-bedroom, two-bath, third-floor suite has lush garden views and is just a short walk to beaches, shops, and restaurants. A great value located in sunny Poipu!
This two-bedroom, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Poipu
