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 = '96778' OR postalCode = '96778' OR region like '%|96778|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Private cottage, gorgeous 720 sq ft one bedroom, full kitchen, bathroom, spacious living/dining area. Covered lanai with gas grill, patio seating, covered gazebo area with hammocks and hanging chairs. Conveniently located right off famous ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 10
Hawaii> Pahoa
Spectacular ocean views await you from every level of this professionally decorated vacation home. Located on the Eastern side of the Big Island, The Jewel Box is a traditional Hawaiian bungalow remodeled with your comfort and luxury in mind ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Spacious Custom built newly remodeled 3bd 2ba home with 2,144+ sqft of living space that has been beautifully remodeled/renovated for enjoying Island living.
This home features vaulted ceilings, natural light with French Doors opening on ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
Come celebrate your Life & new beginnings at the acclaimed Phoenix House!
Featured in countless media around the world, this epic, off-grid tiny home creation at the feet of an active Volcano has won the hearts of countless ...
- 2 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Pahoa
Experience an unforgettable stay at the Magical Lava Temple, a 3BR/1BA off-grid sanctuary located 4 miles from the base of the world’s most active volcano. Surrounded by lava fields and breathtaking views, this open-air loft offers a unique ...
- 3 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Pahoa
We are THE perfect luxury listing on the east side of the Big Island! A quick distance to black sand beaches, waterfalls, the volcano and charming Hilo town.
ALOHA! Welcome to Hale Kakahi or Hawaiian for “unique, outstanding, ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> Pahoa
Enchanted cottage with outdoor clawfoot bathtub and private yard. Airy open space design with veranda and garden seating. Additional bath house with rainhead shower and toilet. Listen to the sounds of the forest and birds, and the waves at ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> 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 ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Pahoa
Aloha & welcome to our lush 11 acre OFF GRiD Paradise!
This home operates completely off the grid meaning we gain 100% of our power from solar energy, our water is catchment (rainwater), and we have propane to power any gas devices. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
There is a black sand beach nearby to explore (Kehena), and a lava tube hidden in a kipuka surrounded by a lava field, and geothermally heated ponds, forest trails, farmers’ markets, yoga classes, chocolate and mushroom farms, dances, ...
- 4 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Pahoa
Thank you for reading about our home & we look forward to your stay! We share the islands secret places & gems with you & supply personalized itineraries!
Your vacation get-a-way will take place on a
landscape of lush gardens ...
- 5 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Pahoa
Immerse yourself in an exceptional once-in-a-lifetime experience on the Eastern tip of the Big Island, in this newly renovated lava themed suite. Surrounded on three sides by native forest, like a natural lei, and open to gorgeous skies and ...
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Pahoa
You'll feel well-cared for and cozy in this new two-bedroom home. High-end design and luxury finishes bring art, comfort and beauty indoors; wrapping you in a jewel box of tranquility. The large open deck offers plenty of outdoor living space. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Right off 130 surrounded by lush jungle. We are ready to help you have the best adventure this side of Hawaii! Tours available by Host living in guest house on site. We just added a full size 7 person 58 jet Hot tub!! (Shared with host but we ...
- 3 bedroom
- 2 bathroom
- Sleeps 12
Hawaii> Pahoa
Tropical Dolphin B | 31-Day Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Discover a dreamy tropical retreat at this two-bedroom, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Experience the Big Island while staying in this unique converted Hawaii trolley! The Hawaii trolleys are used to shuttle visitors from all over the world around the sights and sounds of many of our beautiful islands. Now you can enjoy a stay in ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Pahoa
Puni Mauka A | 31-Day Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Located less than five miles from Pāhoa for convenient ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
Welcome to our beautifully designed Lava House. Situated on 2 private acres of pristine beauty and tranquility. A long winding driveway leads you to our very private home which backs up to the most spectacular lava field. Our home is surrounded ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
WE ARE LOCATED CLOSE TO THE ONLY NATURAL HOT SPRINGS IN HAWAII! Come bathe in lava heated spring ponds to rejuvenate and explore THE untouched Beauty of Hawaii. THIS is truly a RETREAT. If touristy Hawaii is not for you, and quiet connection ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Pahoa
