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 = 'Keaau' OR postalCode = 'Keaau' OR region like '%|Keaau|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Welcome to Pua Lani, a gated one acre estate two blocks from the ocean that features resort landscaping and large lawn areas that create a very private and serene setting for your stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
Modern furnishings and recently updated with Air Conditioners in every room!! New Kitchen appliances makes meal prep a breeze. Private home is conveniently located 30 minutes from Hilo, 40 minutes from the newest beach in the world, and less ...
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Keaau
Explore the Big Island and use Casa Tortuga as your home base. Only 40 feet from the ocean, this 1400 sq. ft. luxury custom built home has a feeling that brings serenity to all of its guests. It lies on one half acre in a quiet upscale ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Cool tradewinds will carry the sweet fragrance of jasmine and plumeria through the air as you relax in this tropical garden with bananas, pineapples, guavas and papayas. The Paradise Cottage is a charming two bedroom / one bath home on 1/2 acre ...
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Keaau
The Bird's Nest is located in a peaceful neighborhood that is surrounded by lush greenery and bird songs can be heard throughout the day & tree frogs at night. The house sits high above the ground which makes it feel as if you are staying in a ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
Aloha and welcome to my Hawai’i Island cottage!
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Keaau
Private Acre, Entire Home has Air-Conditioning located in a quiet peaceful neighborhood. Easy access to everything the Big Island of Hawaii has to offer. Our home is fully stocked with everything you would need, including beach chairs, beach ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
Welcome to Hale Kailani, the 'Sea and Sky House'. Enjoy breathtaking views of the Pacific Ocean, amazing sunrises, occasional lightning storms, year round turtle and spinner dolphins and seasonal humpback whales right off the back lanai. ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
The Laughing Gecko, a 100% smoke-free property, sits on a peaceful acre near cliffs and black sand beaches on Hawaii's east side. The studio offers a private entrance, queen bed, kitchenette, bath, and lanai. Amenities include a washer/dryer, ...
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Cool trade winds will carry the sweet fragrance of jasmine and plumeria through the air as you relax in this tropical garden with mango, banana and papayas. The property is situated in the residential community of Hawaiian Paradise Park, on one ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Keaau
Enjoy indoor-outdoor living in our bright, open home... Relax on the lanai overlooking lush vegetation and lava outcrops, smell the fragrant Hawaiian air and listen to the waves crash in the distance. Secluded and quiet--a garden oasis far ...
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Keaau
Hawaii STVR Reg. Number: STRV-19-348723
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
Swap the daily grind for luxurious absolute oceanfront tranquility with an escape to Paradise Breeze Retreat in Keaau, Hawaii. Only a 25 minute drive from Hilo Airport but a world away from stress. Stunning panoramic oceanfront views from every ...
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Keaau
Located on the Big Island of Hawaii, this detached studio guest house is fully furnished, very private, with vaulted ceilings on a beautifully landscaped 3 acre privately enclosed property. Property has a gated entrance with private gate code, ...
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Keaau
Kalei’s Hawaiian Hideaway is a well-appointed tropical getaway that is nestled in the lush forests of Puna on Hawaiʻi Island. This 1750sqft custom built cedar home has 3 spacious bedrooms and 2 baths. Our home offers a rustic feel, yet ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
E Komo Mai (welcome) to Hale Hapu’u! Our little Hawaiian hut is perfect for couples seeking an authentic Big Island experience. The Hale is adorned with hand carved art from local artists, an outdoor bath set in the midst of a tropical garden...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
New home and new VRBO listing. House was built in 2020 and has all modern amenities! Get away from the crowds! Experience the best of all worlds; 3 blocks from the ocean on 1 country acre, a gated private home, and only a 45 minute drive to the ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
The Lava Suite.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Beautiful Cottage Home
3 bedroom
2 bathroom
Sleeps 9
Hawaii> Keaau
ALOHA! Welcome!
1 bedroom
3 bathroom
Sleeps 2
Hawaii> Keaau
