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, 30
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
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 2
Hawaii> Pahoa
~ ALOHA! ~ Come enjoy a Magic, Memorable Vacation, here at Pele's Feet, on some of the newest land on Earth ~ See the Volcano from your bedside! Come experience this supercharged Power Spot -- with TuTu PELE, the Goddess of ...
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
Gorgeous and immaculate Four Bedroom, Four and a half bath with saltwater pool and outdoor shower. Three queen beds sleep six. Master suite Fourth bedroom/bath with King bed for additional guests negotiable. Ocean views surrounded by papaya ...
4 bedroom
5 bathroom
Sleeps 6
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, open to gorgeous skies and breezes from the north and 5...
2 bedroom
2 bathroom
Sleeps 2
Hawaii> Pahoa
Kaimu Lani translate as the the house of the "Heavenly Gathering at the Seaā ā a name that serves to capture the essence of this magnificent vacation home. The house is located on the remote windward side of the Big Island, adjacent to 500 ...
3 bedroom
3 bathroom
Sleeps 10
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
Come enjoy warmth and rainforest jungle surroundings at Hawaii's best clothing optional resort! We are located away from the big city, yet minutes from Volcano National Park and Volcano Town. Our multi acre hosted eco resort consists of 3 ...
1 bedroom
3 bathroom
Sleeps 4
Hawaii> Fern Forest
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
Puni Mauka | 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 three miles from the ocean, and just a few ...
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Pahoa
~* ALOHA! Welcome to the Hakuma House! This OFF GRID, cozy home has awesome ocean & volcano views. Here in Kalapana, the Wind, Sky, New Earth, Pacific Ocean and vast vistas of Hawaii Volcanoes National Park ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahoa
Prime location for a paradise packed Big Island experience! Enjoy peaceful, relaxing nights in Seaview Estates, a quiet place for 1, a romantic getaway or perfect for the entire family. This luxurious Ohana unit has a private entrance, one ...
0 bedroom
2 bathroom
Sleeps 5
Hawaii> Pahoa
High quality rental with Hawaiian Style home feeling. Upgraded kitchen. Fresh and clean. Relax and enjoy. PLEASE NOTE: Our prices include TAT/GET tax - Perfect for 1-5 people. This is a fully furnished guest 2 bedroom/1 bath house w/ ...
2 bedroom
0 bathroom
Sleeps 5
Hawaii> Pahoa
Live in beauty with this botanical garden inspired Villa. Enjoy the views of three gorgeous lush tropical acres from the comfort of a captivating all glass house! Walls of windows offer fantastic stargazing at night while the days are filled ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Pahoa
Located 5 minute drive to the ocean, 30 minutes from Hilo airport. Cottage is ideal for a couples, however if you have a family and wish to stay, we can provide air mattresses for the kids for up to two more people. Queen size bed in unit, WIFI,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahoa
Beautiful, peaceful, garden atmosphere. 5 minute Drive to Kahakai Park with Gorgeous Ovean Vista View. Ideal for singles or couples. However we have two single air mattresses and extra bedding available for up to two children. Queen Size Bed and...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Pahoa