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 (city = '96746' OR postalCode = '96746' OR region like '%|96746|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
WHY STAY HERE
Kauai Coast Resort at the Beachboy is the perfect beachfront location in Kapaa next to the Coconut Marketplace. The spacious suites are decorated to let the Hawaiian culture be seen throughout the resort. Filled with amenities, ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
HI> Kappa
KrellersGetaway has been renting units at the Islander on the Beach complex since 2005! Just listed each on VRBO, so for 270+ reviews collected over the years, please see https://www.homeaway.com/vacation-rental/p960067.
Also, if not all of ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
HI> Kapaʻa
WHY STAY HERE? Packed with Amenities + Location! Location! Location!
This 2 BR/2 Bath Condo is steps from the Ocean & Beach.
Bathrooms:
1 Bathroom w) granite counter-top & double-sinks, shelving
2nd Bathroom w) granite counter-top, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kapaʻa
KrellersGetaway has been renting units at the Islander on the Beach complex since 2005! Just listed each on VRBO, so for 300+ reviews collected over the years, please see HomeAway 960067.
Also, if not all of your dates are open for this unit ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
HI> Kapaa
AVAILABLE DATES:
July 12-21, 2022 (9 nights)
August 8-19, 2022 (11 nights)
WHY STAY HERE?
Two previous guests say it best! They provided unbiased VRBO REVIEWS below:
Deb ~ Thu, Dec 23 – Sat, Jan 1, 2022
VRBO ~ 5/5 Stars
AWESOME!!
We ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kapaʻa
GOOD NEWS! Hawaii is open for travelers! Make sure you get a negative covid test within 72 hours of arrival and check all of the details on the Hawaii.gov website so you can have a safe an successful trip to Kaua'i.
PROPERTY DESCRIPTION
You've...
- 2 bedroom
- 1 bathroom
- Sleeps 6
HI> Kapaa
COVID-19 UPDATE: The state of Hawaii has very strict Arrival requirements. All travelers must register with Safe Travels Hawaii program. You are responsible for complying with the State of Hawaii and County of Kauai regulations. For more ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
HI> Kapaa
Welcome to 'Wailua Hale Lani'; our beautiful, Kauai, one bedroom, one bath condominium at Wailua Bayview. The condominium is on the second floor (#201) of a three-story, 45 unit complex. Continually updated and meticulously maintained, the unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kapaa
AVAILABLE DATE: Stay 9 NIGHTS with TWO FULL WEEKENDS!
DECEMBER 23, 2021 - JANUARY 1, 2021
WHY STAY HERE:
This beautiful King Suite Condo has a lanais w) two sets of sliding glass doors and is just steps from the Beach.
...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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 and ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
HI> Waipouli
"Surf's Up!" at Islander on the Beach
New to Our Rental Program! Newly Decorated for Your enjoyment!
Gorgeous Ocean Front View!
"Surf's Up!" at Island on the Beach 327 has GOOD VIBES. From the moment you step into this prime, third floor ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> Kapaa
Aloha! Welcome to the luxury Waipouli Beach Resort unit D 308, the biggest 1500
square foot unit with the most central location and breathtaking ocean view from every room!
Let Waipouli Beach Resort D 308 become your Kauai home away from home!...
- 2 bedroom
- 3 bathroom
- Sleeps 9
HI> Kapaʻa
"Simply Paradise" at this Oceanfront Condo Suite at Lae Nani 123!
E komo mai i kēia Lae Nani! Welcome to this beautiful point, a peaceful and lovely haven on the shores of Wailua, Kauaʻi
Lae Nani 123 is a 1 bedroom, 1.5 bath condo suite is...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kapaa
This condo is beautifully decorated, very close to the ocean and
on the ground floor so you can just walk to your beach.
You can relax on the lanai with our beautiful, manicured garden and an ocean view.
You may cook your own delicious ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kapaʻa
PLEASE CHECK HAWAII and KAUAI TOURISM AUTHORITY - COVID POLICY websites to confirm if regulations can be met before booking.
Welcome to the paradise in Kauai, Waipouli Beach Resort. G-205 with 2 suite bedrooms and 3 full bath is equipped with ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
HI> Kapaʻa
