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
Relax in Paradise with 1 acre of Tropical Jungle to explore and the sounds of the Ocean,Birds & Frogs. 5 Min short walk to Paradise Cliffs & Parks. COME SEE THE ONLY HAWAIIAN ISLAND WITH AN ACTIVE VOLCANO. 15 min Drive to Mackenzie State Park. ...
3 bedroom
2 bathroom
Sleeps 6
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
Tropical island paradise! Breathe in the ocean air from your private balcony. Step out 100 yards on to Magnificent Oceanfront.
1 bedroom
1 bathroom
Sleeps 2
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, is located on a one acre lot, on the east side of Hawaii island, in a more quiet, remote area, in close proximity to the cliffs and black sand beaches.
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 4
Hawaii> Keaau
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
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
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Keaau
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Keaau
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
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
Enjoy our incredible custom home has a charming warm Hawaiian feel. With a world class custom kitchen with huge island that opens to a super comfy living room. 2 huge bathrooms, the master with jacuzzi tub and royal shower. You will be ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
