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 (`headline` like '%kealakekua bay%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Exquisite beauty and luxury in a private and stunning setting perched above Kealakekua Bay. This two bedroom, two bath home is part of a quiet, peaceful private estate at a perfect setting overlooking Kealakekua Bay with custom architectural ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Captain Cook
'Okika Lani sits at the south end of Kealakekua Bay on a private road with spectacular views of the Pacific Ocean, the Captain Cook Monument, and crescent shaped, white sand Ke'ei Beach.
Enter the gate at 'Okika Lani and walk into an inner ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Captain Cook
The Pagoda House is a beautiful, traditional Hawai'ian-style screen home with a private pool and garden overlooking Kealakekua Bay Marine Sanctuary - widely considered some of the best snorkeling in all of Hawai'i. The home's open-air design ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Captain Cook
The property is a one-acre lot with a small mango orchard (30 trees), fruit trees (lime, lemon, orange and avocado) and a beautifully manicured landscape. The cottage is separate from the main house, so your privacy is assured. There is a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Captain Cook
Newly built LUXURY designer home including a 6-person HOT TUB and an amazing view of the ocean. The living space on the top floor is 2300 sq. ft. including a covered lanai of 1150 sq. ft. Bedrooms with attached bathrooms are 30 ft. apart with ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Captain Cook
Hale Maluhia La‘a' - Home of Sacred Peace
Review all 99% of our wonderful
5 Star reviews on our home
Please contact the owner with any questions.
This is our beautiful 4-bdrm; 4th bdrm/Great Room home
It's spacious, airy, light filled and a ...
- 4 bedroom
- 2 bathroom
- Sleeps 14
HI> Capt Cook
Kealakekua Bay (which means Pathway to the Gods) has one of the Big Island's most spectacular coral reefs. Because it is a Marine Life Sanctuary, the fish are plentiful and swarm over the well developed reef that slopes from the shores into the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Captain Cook
I swim with dolphins at my front yard almost every week. If you ask, I might take you with me:) This is my primary residence. I rent the "Ohana"-guest apartment, on the ground floor. You can walk across the street to enter Kealakekua Bay (aka ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Captain Cook
A true Hawaiian-style vacation rental makes you feel welcome in this fully equipped family home. Just steps to the magical blue waters of Kealakekua Bay, you'll be able to swim, snorkel, and paddle practically in your front yard. With a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Captain Cook
