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 = '96703' OR postalCode = '96703' OR region like '%|96703|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Hale Aloha… On The Sand TVNC-4185
6 bedroom
7 bathroom
Sleeps 12
Hawaii> Anahola
Hale Aliomanu- a Private Beachfront Estate Home TVNC-4245
6 bedroom
6 bathroom
Sleeps 12
Hawaii> Anahola
Mahinani's Farm offers a unique experience for visitors to stay in a peaceful location tucked directly between the mountains and the ocean.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Anahola
ONLY A DREAM AWAY...OLD HAWAII'S GIFT OF ALOHA!
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Anahola
HALEKAI is privately owned by a Native Hawaiian family. We offer reasonably priced beach front accommodations. Our guests have exclusive use of the property assuring privacy.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Anahola
A Classic Kauai Cottage on Acres of Beautiful Grounds. Away from developments and the public. More Island Artsy Than Corporate. Two ceiling fan's in living room and one ceiling fan in bedroom. One floor fan. Nice, natural breeze ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Anahola
Formerly "Hale Houston" Moloaa Beach Hideaway is a 2 bedroom, 2 bath home situated just steps away from beautiful Moloaa Bay on the northeast shore of Kauai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Anahola
At the south end of the property near the entrance is a detached separate 2nd story guest house consisting of 994 square feet of air-conditioned living area with 2 bedrooms plus a loft and 1 bath. On your return from the beach, shower off in the...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Anahola
Hale Makai Beachfront Home located in Anahola on Kaua'i's north-east shore. Convenietly located between the towns of Kilauea to the north and Kapa'a to the east! Step out onto a sandy beach from your back door lanai in Anahola Bay, Kaua'i. We ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Anahola
Sandcastles TVNCU#1305
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Anahola
And not only secluded, but only a four and a half minute walk to a gorgeous and private beach! (I know VRBO wrote “six minute walk” in the title, but I measured it once!). Gilligan's Beach Cottage is not like one of the houses packed into ...
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Anahola
Legally licensed short term vacation rental, TVNC#4308
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Anahola
This quintessential tropical beachfront home with 4 bedrooms and 4 baths with air conditioning throughout is surrounded by lush tropical foliage and swaying palm trees is located on one of Kauai's most beautiful beaches. The expansive stretch...
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Anahola
The Anahola Beach Home is like the summer place of your grandparents. A grand old home, that as you enter, you smile, as you know the relaxing will be easy. A comfortable, rejuvenating, and soul restoring jewel. The Anahola Beach Home resides ...
6 bedroom
6 bathroom
Sleeps 25
Hawaii> Anahola
"Hibiscus Hula Beach Cottage". Kauai Vacation Rental, Anahola, Kaua`i, Hawaii. TAT ID#: TA-092-402-4832-01, TVNC#: 5067. On-Island Manager, Garden Island Properties LLC, Kapa'a Kaua'i.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Anahola
This impressive home consists of three bedrooms, three baths, and 3,289 Sq Ft. of living space siting on pristine 2.5 acres of white sand beach frontage area. Moloa'a Bay was the site location for the TV pilot of "Gilligan's Island". Here is ...
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Moloaa
Welcome to Moloa'a Bay on the northeast shore of Kaua'i. This is a large, secluded, pristine white sand beach that lends itself to coastal hiking, snorkeling, body boarding, surfing, shelling, swimming and sunbathing.
5 bedroom
4 bathroom
Sleeps 11
Hawaii> Moloaa
This spacious, 3200-square foot home is just steps from the beach . . .
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Anini
Carved into a mountain and Perched on a bluff with Stunning 280 degree Beach front Amazing Views. This locations is Unique, one of thr best on the island. With Unspoiled Beauty with a Raw Organic feel to it. Just a few Steps away to a ...
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Anahola
Gorgeous Honeymoon Cottage located in Aliomanu... a short walk to the sand. Recently remodeled in March of 2020, the cottage now has a full kitchen and A/C. Thoughtful upgrades include a new larger kitchen area with new appliances, new windows,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Anahola
