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 = '96778' OR postalCode = '96778' OR region like '%|96778|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Hale "Hā Hā" is a one-bedroom, one-bath, 588 sq. ft. apartment that has separate living quarters with a private entrance and a relaxing lanai. We now have air conditioning! We provide a welcome gift of local Hawaiian products, along with ...
1 bedroom
1 bathroom
Sleeps 4
HI> Pāhoa
Gorgeous and immaculate Four Bedroom, Four and a half bath with saltwater pool and outdoor shower. Ocean views surrounded by plantation. Very private gated off grid property with solar and backup generator. Perfect for family gatherings or ...
4 bedroom
5 bathroom
Sleeps 8
HI> Pāhoa
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
Welcome to this Oceanfront Studio conveniently located between Hilo, Volcano National Park, and black sand beaches. just 30 minutes from Hilo international Airport, Rainbow Falls, and more! When I designed this space, I wished to create an ...
1 bedroom
1 bathroom
Sleeps 2
HI> Pāhoa
Hand crafted getaway in the jungle of East Hawaii. Conveniently located near to Kehena black sands beach along the scenic red road and is ten minutes to the Kalapana lava flow on the magical Puna side of the Big Island. Organically built with ...
1 bedroom
1 bathroom
Sleeps 3
HI> Pāhoa
The Island Hawaii nicknamed The Big Island is a friendly captivating place to call home or to visit. With an array of micro climates, culture and lifestyles, there is truly a piece of paradise for everyone. Come and share in the beauty, and ...
3 bedroom
2 bathroom
Sleeps 6
HI> Pāhoa
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
Garden of the Phoenix is a 2 bed/1 bath, upstairs unit with a lanai facing State Land, 100s of acres of native Ohia Forest, the first tree to sprout and grow on fresh lava. It is jogging distance to the clothing optional black sand beach known ...
2 bedroom
1 bathroom
Sleeps 6
HI> Pāhoa
Aloha, 🛑 IMPORTANT NOTICE 🛑
https://hidot.hawaii.gov/coronavirus/
A Vaccine QR code or Negative covid 19 test QR code from the Airlines ( hawaiicovid19.com safe travels App)will be required Please note, Quarentine is not allowed at the...
0 bedroom
1 bathroom
Sleeps 4
HI> Pāhoa
The Big Island Guest House is a private two-bedroom home including:
-bedroom with a new queen bed
-second bedroom with a Japanese Futon
-full bathroom
-modern kitchen
-large covered lanai (deck or porch) with a bbq
-beautiful gazebo with tiki...
2 bedroom
1 bathroom
Sleeps 4
HI> Pāhoa
In the heart of Hawaii, the newly renovated Lava Forest Retreat sits on an acre surrounded by native Hawaiian rain forest, fruit trees, flowers and birds. The lava eruption of 2018 has left behind a spectacular landscape of some of the world's ...
2 bedroom
2 bathroom
Sleeps 2
HI> Pāhoa
This is a little one bedroom one bath on the bottom floor of the house. There is a sleeper couch, small kitchen, and single bedroom. Small air conditioning and everything you need to stay and have fun on the big Island. The washer and dryer are ...
1 bedroom
1 bathroom
Sleeps 5
HI> Pāhoa
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
