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 (city = '96746' OR postalCode = '96746' OR region like '%|96746|%') 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
Aloha and welcome to our tropical paradise in beautiful Kauai, Hawaii!
Indulge in an extraordinary couples' getaway at our top-floor ocean and beachfront studio condo in Kapa'a. Just 150 steps from our condo's front door, the beach ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
Aloha and welcome to our tropical paradise in beautiful Kauai, Hawaii!
Indulge in an extraordinary couples' getaway at our top-floor ocean view studio condo in Kapa'a. Just 160 steps from our condo's front door, the beach awaits! ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
Welcome to the oceanfront paradise on Kauai at Waipouli Beach Resort Unit F-204!
This unit is a 2 Master Suite bedroom with 3 full bathroom floorplan. Each Master Suite comes with a smart flat screen television, balcony, dresser and ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Newly renovated condo with 2 TV's, WIFI, Washer & Dryer, Dishwasher, Stove, Fridge, Keurig Coffee Machine, Nespresso Machine and Microwave. One bedroom has a king size bed and the other has a queen size bed. 55" Flat screen smart TV in Living ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
Enjoy stunning ocean views from all the bedrooms and living area of the condo. Greet every morning on your bed enjoying the views and sounds from the ocean!
*** OCEANFRONT overlooking greenbelt, quiet and private, no noise from pool or ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Stylish Oceanfront 2nd Floor 2 Bedroom | 2 Bathroom
Lanikai is centrally located on the Island yet 'tucked away'. This elegant second floor unit has sweeping Ocean views. This luxurious condo comes with a fully-equipped kitchen area ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
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
Gorgeious Oceanview 2 Bedroom, 3 Bathroom Condo at First Class Waipouli Beachfront Resort & Spa on Beautiful Kauai.
There is Central AC in this condo as well as a full size washer and dryer for laundry!
This exceptionally, well ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapaa
Oceanfront! Live the Dream! Ultra Luxury "Beachfront" Ground Floor Unit A104 at Waipouli Beach Resort & Spa offering stunning full oceanfront views from every room, with watercolor sunrises, whale watching and romantic dinners on the lanai with...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Gorgeous Oceanfront Waipouli Beach Resort G302, one of Waipouli Beach Resort's most desirable Ocean Front suites, offers spectacular 180 degree unobstructed ocean and sandy beach views. You can see the ocean from every room! Fall asleep to the ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Oceanfront! Exquisite and Tranquil #1 Rated Oceanfront 2 bedroom, 3 bath condo at the beautiful #1 Ranked Waipouli Beach Resort & Spa on the island of Kauai.
There is Central AC in this condo as well as a full size washer and dryer for ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapaa
Royal Oceanfront G101, an Exquisite Ground Floor Oceanfront Villa! Privacy, "Expansive Lawn".
LAST MINUTE APRIL 10 to 17 2024 SPECIAL ! ! !
Lanai Furniture by Kingsley Bate!
Paradise ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Oceanfront! Resort Living at its Best in this Ultimate Luxury Condo Waipouli Beach Resort Oceanfront. Extraordinary Oceanfront 2 bedroom 3 bath condo at the beautiful Waipouli Beach Resort & Spa on the island of Kauai named Hawaii’s #1 ...
- 2 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Kapaa
Kauai Calls! Premier Luxury Gorgeous Oceanview! One of the most gorgeous views of the resorts tropical grounds, the beach and the ocean during the day; and, at night the fire glow of the tiki torches reflecting in the pool and the moon ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapaa
Oceanview! Beautiful! Luxury Gorgeous Oceanview from Living Room, Dining Area and Lanai. Located in Coveted Oceanfront H Building, a one bedroom, two bath condominium at the "New" Waipouli Beach Resort on Kauai. This 2nd floor, oceanview with ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Unit #324 is third floor (Top Floor) DIRECT oceanfront and is in a perfect location in the complex. It has panoramic ocean views of the stunning Pacific Ocean. Being that it is on the top floor, you have the great views and optimal privacy. ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
Our Most Popular Garden View! This well decorated, Luxury condo is ideal for Families! A two Bedroom/three Bath condo located on the ground floor with easy pool, beach and garden access and a view of the beautiful mountains. Enjoy your evening ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapaa
Experience a Vacation of your Dreams in Exquisite Luxury Oceanview Waipouli Beach Resort & Spa H204 offering one of the most spectacular views of the sparkling blue azure Pacific Ocean as it washes over the white sand beachfront just beyond the ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Oceanfront! Priceless and Tranquil Waipouli Beach Resort an Oceanfront 2 bedroom, 3 bath condo at the beautiful #1 Ranked Waipouli Beach Resort & Spa on the island of Kauai.
There is Central AC in this condo as well as a full size washer...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapaa
