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, 50
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
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
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
Welcome to the Sunrise House, your ocean front split-level oasis in Aliomanu, 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...
2 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
**Christmas Week Discounted Nearly 50% Off Normal Rate!!**
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Anahola
(Rate $1,490.00/night + Taxes, VRBO fees & cleaning, total daily rate is higher varies with the number of nights booked)
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.
3 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.
6 bedroom
4 bathroom
Sleeps 11
Hawaii> Moloaa
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,...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Anahola
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
This exquisite, fully remodeled four-bedroom home is regally perched as close to the edge of the ocean as a house in Hawaii can be. Featuring a private staircase with 11 steps leading directly to the water’s edge, this is beachfront living at ...
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Anahola
This beautiful ocean front Hawaiian-style beach house commands a spectacular 220 degree view of the ocean from every room and especially from its spacious ocean front lanai. This is Hawaii at it's best. Your own secluded beach spectacular ocean ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Anahola
Experience Authentic Hawaiian Beachfront Living
5 bedroom
3 bathroom
Sleeps 12
Hawaii> Anahola
Looking for an EXPERIENCE different than what the resorts provide? Woodrose Plantation includes a breathtaking home nestled on two and one-half acres of lush, botanical, and fruit gardens in pristine Moloaa Bay. Some of the varieties of fruits ...
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Anahola
TV-NCU-4258 (VRBO # 1700669)
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Anahola
Hale Awapuhi 2F is among the most luxurious oceanfront properties in Hawaii. Exclusively gated, this boutique 9-unit project offers panoramic ocean views and beach access just steps from the front door.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapa'a
Located northeastern side of the Kauai on Anahola Bay, this is the perfect beach spot for a gathering of family and friends. Mornings start with oceanfront sunrises and your day continues with beach activities right outside your door. Evenings...
5 bedroom
5 bathroom
Sleeps 12
Hawaii> Anahola
The soothing crash of waves and a laid-back lifestyle await upon booking this dreamy Kauai home! Hosting 2 bedrooms, 2 bathrooms, and an awe-inspiring view of Anahola Beach, this vacation rental provides everything needed for a memorable ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Anahola
