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 = '96754' OR postalCode = '96754' OR region like '%|96754|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Kai Hale... TVNCU #1381
Beachfront at beautiful Anini Beach, this fabulous home is truly a paradise. Perfectly designed to take full advantage of its premier location, Kai Hale is surrounded by a lush, Hawaiian garden and meticulously manicured...
3 bedroom
3 bathroom
Sleeps 6
HI> Kilauea
Beach front unit is perfect for watching the humpback whales play during the winter months and each evening as the Maui sun sets off in the distance. The long stretch of beach below is the Sugar Beach, which is perfect for morning or evening ...
1 bedroom
1 bathroom
Sleeps 2
HI> Kihei
Our Anini Beach rentals include this romantic home that fronts Anini Beach with its two-mile stretch of soft white sand and the largest living coral reef on Kauai. Near the end of the sandy road, finding a private spot on the sparsely populated ...
2 bedroom
2 bathroom
Sleeps 4
HI> Kilauea
Our Anini Beach rentals include this beach cottage that is located just across the road from Anini Beach with its two-mile stretch of soft white sand and the largest living coral reef on Kauai. This home is just steps away from the beach access...
3 bedroom
2 bathroom
Sleeps 6
HI> Kilauea
Between the mystical mountains and renowned Secret Beach, The Dali Hale Estate is more than a home: you’ll feel the magic the moment you arrive. With more than 500 feet of ocean frontage, the Dali Hale is the perfect place to gather for grand ...
8 bedroom
8 bathroom
Sleeps 12
HI> Kilauea
One of the more sought after aspects of a vacation rental (or home for that matter) on Kauai is to be one of a kind; to have a view that can only be cherished by those privy to the specific angles and perspectives a particular property provides....
3 bedroom
3 bathroom
Sleeps 6
HI> Kilauea
Please inquire about The State of Hawai‘i Safe Travels Hawai‘i program and the requirements for traveling to Kauai as it does change.
Newly remodeled north shore beach house with beach toys.
Ask the locals! Locals know that Kalihiwai ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kilauea
Located in a wildlife refuge on its own private lake on the island of Kauai, Kilauea Lakeside Estate is a beautiful, secluded Hawaiian retreat, perfect for honeymoons and family vacations. This totally private resort has been described as the ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kilauea
This spacious home is Located in the Seacliff gated community, adjacent to the Kilauea Point National Wildlife Refuge and the Kilauea Lighthouse.
You will enjoy large panoramic windows throughout the home with extensive mountain and bay views. ...
3 bedroom
3 bathroom
Sleeps 4
HI> Kilauea
*** COVID 19 UPDATE - please be sure to familiarize yourself with our state and county Covid 19 travel protocols BEFORE making a reservation. It is the travelers responsibility to meet requirements put forth by the State of Hawaii and County of...
1 bedroom
1 bathroom
Sleeps 2
HI> Kilauea
GE-033-843-7120-01
TA-033-843-7120-01
NEWLY REMODELED TOP TO BOTTOM -3 Primary Bedrooms, 3 1/2 Baths, Sofa Sleeper, Front Lanai, Back Screened Lanai, Fruit Grove, Ocean View
Anini Kai, is a luxury beach home located on the North Shore of Kaua'i...
3 bedroom
4 bathroom
Sleeps 8
HI> Kilauea
GE/TA 125-285-3760-01
TA#W90869281-01
Welcome to Jungle Bee!
Please watch our video in the picture window
video tab above...
Jungle Bee Farms is an organic chocolate (cacao) farm overlooking the northern most point in the Hawaiian Island ...
4 bedroom
4 bathroom
Sleeps 8
HI> Kilauea
Licensed Rental: TVR#4284/SP-2001-15
Located on the bluffs of Kauai's Kilauea Farms, Hideaway Bay boasts stunning, unobstructed ocean views from every room in the house. The home is completely private and surrounded by three acres of citrus ...
2 bedroom
4 bathroom
Sleeps 6
HI> Kilauea
**THIS RENTAL IS EXCLUSIVE TO VRBO - DO NOT RENT OFF CRAIG'S LIST OR ANY OTHER WEB SITE**
If you are looking for the ultimate Kauai experience, then look no further! Located on the North Shore, the most desirable area of Kauai, known for its ...
2 bedroom
1 bathroom
Sleeps 2
HI> Kilauea
IF YOU'RE PINING FOR A LUSH, LAZY, LANGUID ESCAPE...A BAREFOOT TRANQUILITY...A BALMY, PALMY, SEA-SCENTED ADVENTURE...LOOK NO FURTHER!!
Rated #1 Because...
1. AS SEEN ON TV - The Discovery Channel recently filmed our home, Hale Mana, for the ...
4 bedroom
3 bathroom
Sleeps 8
HI> Kilauea
