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 = '96704' OR postalCode = '96704' OR region like '%|96704|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Please check out our cottage on the same property for smaller groups: Vrbo 953957
This Unique property showcases a 153 foot ocean facing deck with luxurious indoor/outdoor Hawaiian style living. Everything is completely redone and ...
2 bedroom
3 bathroom
Sleeps 6
HI> Captain Cook
Welcome to WhaleSpirit Sanctuary, a sacred vacation and rejuvenation destination. This eco-friendly sanctuary is located within minutes of some of the island’s best waters to swim and see whales, dolphins, turtles and many other amazing marine...
1 bedroom
1 bathroom
Sleeps 2
HI> Captain Cook
Welcome to WhaleSpirit Sanctuary, a sacred vacation and rejuvenation destination. This eco-friendly sanctuary is located within minutes of some of the island’s best waters to swim and see whales, dolphins, turtles and many other amazing marine...
1 bedroom
1 bathroom
Sleeps 2
HI> Captain Cook
This home has just had a full renovation of the exterior and grounds, it is beautiful.
A first floor full contained 250 + square foot home. It has its own kitchenette and bathroom with a shower. There is a beautiful puka stone path to the ...
1 bedroom
1 bathroom
Sleeps 3
HI> Ocean View
Kona recalls the Beach Boys playing their pop hits by the pool. The band left but the good vibrations linger. Situated on 9.5 gated acres, gated for with an amazing Hawaiian plantation-style home with separate sleeping quarters and a guest ...
5 bedroom
5 bathroom
Sleeps 14
HI> Captain Cook
The Beautiful Edge of the World is a very special place. Peaceful and secluded but close to many fine (and reasonable) restaurants, Not far from the Kona airport, the location is great for snorkeling, hiking, swimming with the dolphins, manta ...
1 bedroom
1 bathroom
Sleeps 4
HI> CAPTAIN COOK
Covid update 6-17-20: 14 day Quarantine in effect for mainland visitors through 7-31-20. Local residents, please message for availability!
Kona Mauna Kai is a working farm on the slopes of Mauna Loa on the Big Island of Hawaii. Mauna Kai means ...
1 bedroom
1 bathroom
Sleeps 2
HI> Captain Cook
On the shores of Kealakekua Bay, a Protected Marine Conservation area, is an enchanting, 4 bedroom/3 bath, water front house. This open and airy private home has all the amenities one would desire for a tropical vacation. The property is ...
4 bedroom
3 bathroom
Sleeps 11
HI> Captain Cook
The Milolii Whale House offers breathtaking, 270 degree views of the Pacific Ocean! Located 100 yards from the waves crashing on the cliff side, the Whale House offers the best views in the area. Relax on one of the 2 spacious lanai's while ...
3 bedroom
3 bathroom
Sleeps 5
HI> Captain Cook
DUE TO OUR AGE & MEDICAL HISTORY WE ARE ONLY ACCEPTING FULLY COVID 19 VACCINATED GUESTS! We have both had 3 shots of the Phizer vaccine, wear masks when in public and have kept our little coffee farm a safe place. We will not accept ...
1 bedroom
1 bathroom
Sleeps 2
HI> Captain Cook
Amazing ocean/sunset view, secluded and quiet but very close to restaurants, supermarket, fantastic snorkeling, hiking, historical sites. King bed, private bath, fridge, free wifi.
The Beautiful Edge of the World BnB, Kona has been in operation...
1 bedroom
1 bathroom
Sleeps 4
HI> CAPTAIN COOK
This listing is just for the Main Building that sleeps 18. We have 6 additional cottages available on property and we can accommodate up to 32 total.
WhaleSpirit Sanctuary is a sacred-retreat rental destination. We are an eco-friendly ...
6 bedroom
5 bathroom
Sleeps 22
HI> Captain Cook
Welcome to WhaleSpirit Sanctuary, a sacred vacation and rejuvenation destination. This eco-friendly sanctuary is located within minutes of some of the island’s best waters to swim and see whales, dolphins, turtles and many other amazing marine...
1 bedroom
1 bathroom
Sleeps 5
HI> Captain Cook
We can help you with KAYAKS! As a means to provide protection to the marine wildlife, only permitted Kayaking is allowed in Kealakekua Bay. As part of our vacation home rental you are provided complimentary use of our kayaks and launch with ...
3 bedroom
3 bathroom
Sleeps 10
HI> Captain Cook
One of three villas on a private, beautiful 5-acre orchard retreat in South Kona. Due to the elevation, the area is considered to have the best views in Kona displaying a combination of sloping hillside, shoreline and open ocean sunsets.
...
2 bedroom
2 bathroom
Sleeps 4
HI> Captain Cook
