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 '%kauai beach resort%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Capture the tranquility and beauty of the Garden Isle from this charming, centrally located condotel unit at the Kauai Beach Resort. From your private lanai, take in Kauai's lush landscape and majestic mountain views, while enjoying the island's...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> Lihue
KAUAI BEACH RESORT
My unit is a hotel room located in the Kauai Beach Resort and Spa about 2 miles from Lihue Airport. It is centrally located and midway between the North Shore and the South Shore. It is just a few miles to Lydgate Park, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Lihue
This is a lovely third floor unit with a view of the lagoon. This resort is absolutely gorgeous and meticulously well kept. You will love the lush vegetation, tropical palm trees and emerald green grass surrounding 4 breathtaking swimming pools....
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Lihue
Gorgeous Tropical 4 Star Resort on the Ocean Front 5 minutes from the airport centrally located several pools and spas Beautiful white sand beaches and lush Tropical garden grounds view great view of the mountains all at 1 resort....
- 0 bedroom
- 1 bathroom
- Sleeps 4
HI> Lihue
This is a beautiful second floor unit with a view of the ocean and park. Comfortable king bed with chaise lounge and balcony.
A beautiful Hawaiian plantation-style resort located on 25 acres of lush Garden Isle, centrally located between ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Lihue
This is a quiet, fifth floor unit with mountain views. 2511 has two queen beds for more family or friends to join you. It is about a three minute walk from your room to the beach. Stay at this beautiful resort at a low nightly rate. Kauai Beach ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lihue
This is a Second Floor Air-Conditioned Unit with beautiful Mountain and Lagoon views. Two Queen Beds, Bathroom, Coffee Maker, in-Room Safe and Small Refrigerator all for your comfort and convenience. Step out onto your Private Lanai/Patio to get...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lihue
Your family will be close to everything when you stay at this centrally located place. Full-service oceanfront resort with a variety of amenities. This Unit is a private location with just your hotel around you and beautiful coastline....
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lihue
