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 = 'Pahoa' OR postalCode = 'Pahoa' OR region like '%|Pahoa|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Faris' Forest offers a private & magical get away for your Big Island retreat. We're located on a 3 acre homestead between Volcano National Parks, Historic Pahoa town, and Hilo in a peaceful Ohi'a forest. Our land is home to 100's of plant ...
1 bedroom
2 bathroom
Sleeps 2
HI> Pahoa
I have an amazing 3 bedroom 3 bath home on 5 landscaped acres with a pool. The river rock bottom saltwater pool is a refreshing place to cool down after hiking the incredible volcanic crater only a few hundred yards away. The master bedroom ...
3 bedroom
3 bathroom
Sleeps 14
HI> Pahoa
Welcome to Hawaiian Naturist Park. Come enjoy warmth and rainforest jungle surroundings at Hawaii's best clothing optional eco resort! We are just minutes from Volcano National Park and the quaint town of Volcano. Our multi acre hosted resort ...
1 bedroom
3 bathroom
Sleeps 4
HI> Pahoa
Aloha! Welcome to this oceanfront suite with private entrance conveniently located between Hilo, Volcano National Park, and black sand beaches. 30 minutes from Hilo international Airport, Rainbow Falls, and more! This space was created to be an ...
1 bedroom
1 bathroom
Sleeps 2
HI> Pahoa
Pahoa Paradise
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
You'll love coming home to this quiet home located in beautiful Pahoa. You'll love enjoying...
3 bedroom
2 bathroom
Sleeps 7
HI> Pahoa
Enjoy the entire main floor of our off-grid home, accessible by a private entrance complete with two bedrooms, soaking bath tub and shower, outdoor shower, full laundry with heavy duty washer and dryer, living room and gourmet kitchen. Lounge ...
2 bedroom
1 bathroom
Sleeps 4
HI> Pahoa
WELCOME to KAI MALOLO (quiet seas)
YOUR BIG ISLAND ADVENTURE STARTS HERE. 5/13/18 THIS AREA RIGHT NOW IS SAFE and STILL OPEN FOR YOUR STAY.. IT is beautiful here. JUST INSTALLED in the living room AIR CONDITIONING!!!
Spectacular ...
3 bedroom
2 bathroom
Sleeps 6
HI> Pahoa
Welcome to Big Island Sanctuary - in Leilani Estates Near Fissure 8! This sweet home made it through the recent eruption and is a brand new vacation rental with Hawaii Life Vacations! It's the perfect retreat a couple or someone who wants a ...
1 bedroom
1 bathroom
Sleeps 2
HI> Pahoa
Nice, comfortable home on large lot with spacious lanai and fenced back yard. Use our home as a base for your Hawaiian adventure. Cook meals in our new kitchen with fresh, delicious and healthy fruits and veggies available at local markets. ...
3 bedroom
2 bathroom
Sleeps 8
HI> Pahoa
Located on the rugged and picturesque Puna coast of the Big Island, Hale Mohalu Guesthouse is a garden residence in paradise. The lush and verdant district of Puna provides the idyllic setting for this unique botanical retreat.
Situated on ...
3 bedroom
2 bathroom
Sleeps 7
HI> Pahoa
Hale Aikalani
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The open waters of the Pacific Ocean are right in your backyard at this modern and ...
3 bedroom
3 bathroom
Sleeps 6
HI> Pahoa
Tucked away in the lush and beautiful Hawaiian Beaches neighborhood, you’ll find Hale O Nani Mala Pua – House of the Beautiful Gardens.
This three bedroom, two 1/2 bath home is located at the very end of an peaceful street. Wonderfully ...
3 bedroom
3 bathroom
Sleeps 6
HI> Pahoa
Hale O Lalakea Big Island Vacation Rental
Stay in this Peaceful, Private & Comfortable, 2 Bed/1.5 Bath Home Near Beaches, Art Galleries and Adventure!
This fun, updated 2 bedroom/ 1.5 bath home is ready for you and your family!
Hawaii ...
2 bedroom
2 bathroom
Sleeps 7
HI> Pahoa
Hale Balleja
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Designed by a noted San Francisco architect and once featured in The Huffington Post's "11 ...
1 bedroom
2 bathroom
Sleeps 2
HI> Pahoa
Hawaii STVR# 19-346909, NUC #19-319
Note- On Oct. 15, 2020 any trans-atlantic visitor meeting the State approved pre-travel testing requirement (a negative test from a CLIA certified lab, using the NAAT test, within 72 hours of travel) and ...
3 bedroom
3 bathroom
Sleeps 6
HI> Pahoa
