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 = 'Keaau' OR postalCode = 'Keaau' OR region like '%|Keaau|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
1.17 acres planted with palms and fruits trees, free parking free wifi.
guests can walk around house. Barbecue place next to ...
4 bedroom
4 bathroom
Sleeps 8
HI> 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
HI> Keaau
You can stay just minutes from the sunrise cliff views at Maku'u in a recently remodeled respite home in a charming community with local flair. This stunning 2 story space, is perfect for a single or multi-family getaway with an upstairs Queen ...
3 bedroom
2 bathroom
Sleeps 8
HI> KEAAU
You can stay just minutes from the sunrise cliff views at Maku'u in a private recently remodeled ~300 sq ft studio "Ohana" in a charming community with local flair. This stunning space, is perfect for a single, working vacation or couple "get ...
1 bedroom
1 bathroom
Sleeps 2
HI> KEAAU
Welcome to The Coqui Shack! This beautifully decorated 2-bedroom, 2-bath home is the perfect locale for sightseeing the east side of The Big Island!
The Coqui Shack is centrally located in the Hawaiian Paradise Park subdivision between the ...
2 bedroom
2 bathroom
Sleeps 4
HI> Keaau
My cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with Wi-Fi, self check-in with keyless entry smart lock keypad, and free parking right in front of your unit. During your stay, you can also enjoy ...
1 bedroom
1 bathroom
Sleeps 2
HI> Keaau
Enjoy indoor-outdoor living in our bright, open home... Relax on 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 from ...
2 bedroom
2 bathroom
Sleeps 5
HI> Keaau
Aloha and e komo mai to the Cliff House,
The Cliff House is a two story structure with furnished studio suites on each floor, directly on the oceanfront. We only host one party at a time (e.g. if the Paradise Suite is occupied, no one will be ...
2 bedroom
2 bathroom
Sleeps 4
HI> Keaau
Quiet setting to relax in a paradise living with spacious living room, new queen beds and your private full bath, appliances in the kitchen, and laundry. The house is surrounded with coconut trees and tropical fruits. We are conveniently located...
2 bedroom
1 bathroom
Sleeps 5
HI> Keaau
Aloha and e komo mai to the Paradise Suite at the Cliff House.
The Paradise Suite is a coveted favorite at the Cliff House on Kaloli Point. With an expansive ocean view, this second/upper floor studio combines luxury and comfort. The Paradise ...
0 bedroom
1 bathroom
Sleeps 2
HI> Keaau
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.
The second story home features 2 bedrooms and 2 baths ...
2 bedroom
2 bathroom
Sleeps 4
HI> Keaau
Swap the daily grind for luxurious oceanfront tranquility with an escape to Paradise Breeze Retreat in Keaau, Hawaii. Only a 30 minute drive from Hilo Airport but a world away from stress. Stunning panoramic ocean views from every room and the ...
2 bedroom
1 bathroom
Sleeps 4
HI> Keaau
Uli Kai
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Escape to paradise at this sunny and secluded, ocean view home in Keeau. Inside this beautiful, ...
4 bedroom
3 bathroom
Sleeps 8
HI> 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
HI> Keaau
• Comfortable and modern construction that can sleep up to 4 guests, on a fully landscaped acre.
• Spacious bedrooms (King and a queen-sized sofa bed) with seating areas, with linen and cooking implements. There is also a bathroom with a ...
2 bedroom
1 bathroom
Sleeps 4
HI> Keaau
