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 '%kauai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Paradise awaits you in this beautiful ground floor, one-bedroom, two-full-bath Lae Nani Condo, Unit #317 on the beautiful Garden Isle of Kauai!
Located on Kauai’s beautiful Coconut Coast (East side), this Condo has all the amenities you...
- 1 bedroom
- 4 bathroom
- Sleeps 4
Hawaii> Kapaa
Discover the ultimate Kauai getaway at Ka Pua Hale, our vacation rental also known as "Blossom House." Nestled strategically in Princeville, Kauai, our rental provides unmatched convenience:
- Close proximity to North Shore beaches, golf ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Paradise Found..! Surrounded by Lush Tropical Landscape ~ This Spacious 2 Bedroom 2 Bathroom, Ground Level Condo, Opens Up to a Sprawling Green Courtyard with a Partial & Distant Ocean View
Beautiful & Newly Decorated with High End ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
May we welcome you to Alihi Lani at Poipu. Our beautiful, condominiums located in the heart of Kauai's sunny south shore! Alihi Lani. or 'Beautiful Horizon Views', aptly describes the magnificent ocean panorama of Hawaiian sunsets, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Poipu
Located on a picturesque lava bluff, our top floor condo offers privacy, cooling trade winds and panoramic ocean vistas which a camera can't begin to capture.
Crashing waves, migrating whales and sunrise/sunset views are what you'll see as...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
At the forefront of East Shore accommodation is the Waipouli Beach Resort. This World Class Resort provides luxury at your fingertips and offers opulent amenities for a variety of occasions.
With a crisp and classy Hawaiian feel, this ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
With breathtaking vistas of one of Kauai’s most active bays, revel in the majestic scenery of paradise from your private and pristine, seaside perch. Watch cruise ships sail into the harbor, whales spout and breech in the distance, dolphins ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lihue
A rare combination of amenities exists at Lei Aloha, a hidden retreat. Gates lead into a private courtyard, completely enclosed by a stucco wall and tropical plants, including plumeria, heliconia, gardenia, and torch ginger. In the center of the...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Princeville
Live like a local and be just steps from Poipu Beach. Relax in this multi family location and enjoy the comradery of this old time beach neighborhood and fantastic sunsets over Poipu. Updated recently with all the comforts of home this cozy ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
One of the best locations in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over 300 yards to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. The plantation style ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Enjoy island living with friends and family while seeing the ocean views and feeling the ocean breezes from the open and spacious second level great room of Hale Aloha Kai. This home is conveniently located just a few steps away from the beach ...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Koloa
Our luxurious Kauai vacation rentals at Poipu Sands offer spacious layouts, large covered lanais, a private heated resort swimming pool, community gas BBQs, on-site tennis courts, and more.
Centrally located in Poipu, adjacent to the ...
- 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
Perched on the edge of the magnificent Poipu coastline, this intimate resort is home to just twelve oceanfront Kauai condos and features spectacular views as well as a dramatic coastline swimming pool, sundeck and BBQ. Located in the heart of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
One of the most luxurious oceanfront Kauai vacation rental resorts in Poipu, Whaler’s Cove offers serenity and intimacy. Set on a point overlooking Koloa Landing, the resort features commanding 180 degree views of the coastline. The sounds of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
One of the best locations in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over 300 yards to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. The plantation style ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Koloa
