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
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
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
Tucked into the Lanai Villas section of the tropical Poipu Kai Resort, Maikalani is a Kauai vacation rental home exquisitely decorated and outfitted with all the necessities of home, including zoned air conditioning. Located on the lush green ...
- 2 bedroom
- 2 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 ...
- 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
Located in the quaint and historic beach community of Hanalei Bay, this comfortable Kauai vacation home offers its guests a spacious and relaxing place to settle in and enjoy beach life. Hale O’Kale is walking distance to all of Hanalei town ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hanalei
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
Castle Kaha Lani Resort #102 - Energy flows from the tropical decor living area onto the lanai and across expansive lawns of this 739 sq. ft. ground floor villa with beautiful ocean view. Dine in with meals prepped in the full kitchen. Other ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> LIHUE
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kappa
Enjoy the best of island life at one of Poipu’s finest Kauai oceanfront rentals. The 2 bedroom, 2 bathroom coastline gem is in a prime location for family or friends who love to live by the water. Its design takes full advantage of the million...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
This extraordinary 4 bedroom, 4 bathroom luxury home is one of the finest Kauai oceanfront rentals on the island. The grand entrance opens to expansive cathedral ceilings showcasing the magnificent oceanfront living areas surrounded by tropical ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Koloa
Amazing Kauai oceanfront rentals from Parrish Kauai feature this beautiful home at sunny Poipu Beach. Highlights include Hawaii plantation style design and décor and all the must have comforts, including spectacular ocean views from almost ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Koloa
Enjoy the luxury and comfort of our newest Elite home centrally located in Poipu Beach Estates. This custom home takes full advantage of many views and is also positioned to enjoy the fabulous trade wind breezes. The spacious and open great room...
- 4 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Poipu
New home construction next door started in Jan 2022
This luxury Kauai vacation rental is located in the luxury resort community of Kukuiula. This fabulous new home has lovely distant ocean views from both the main living area and the ...
- 4 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Koloa
An architectural masterpiece, Hale Makai is a one-of-a-kind oceanfront Kauai oceanfront rentals that offers its guests a distinguished Kauai vacation rental. This magnificent Poipu Beach residence boasts an oceanfront master bedroom suite with ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
One of the island’s finest Kauai oceanfront rentals set on the dramatic Poipu Beach coastline. Remodeled to perfection, this home has ocean and coastline views from almost every room and offers the perfect setting for the vacation of a ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Welcome to Hale Kokio, a well loved getaway, designed for two. This is the place to stay on North Shore Kauai for relaxation, privacy and island living. Enjoy a quiet setting and a convenient location. The newly updated cottage is designed with...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kilauea
This premium and affordable Sealodge vacation rental has one of the most spectacular views in all of Princeville, Kauai! It is perched on an oceanfront cliff with expansive views of the Pacific Ocean, Kauai north shore coastline, Anini reef and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
RiverSong is nestled in two tropical acres alongside Kilauea River. The river sings below and waterfalls splash beneath your private, secluded lanai. Our gardens abound with color from red ginger, gardenia, orange and yellow heliconia, purple ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kilauea
