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 = 'Captain Cook' OR postalCode = 'Captain Cook' OR region like '%|Captain Cook|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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
Hawaii> Captain Cook
Our 700 sq. foot 2 story home, Miloli'i Beach Bungalow, is located 33 miles south of Kona in Miloli'i. If you are looking to get away from the hustle and bustle of the city, you've come to the right place. Our little house in paradise, ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Captain Cook
Ask about 10 day specials/ 2 person stays and 8th night free if you book a 7th night stay!!!! https://www.youtube.com/watch?v=VigSB3M4z3w The above is the link to a video of my house. Makai Hideaway is quiet and peaceful. Unwind and ...
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Captain Cook
South Kona Hideaway is near some of the best snorkeling Hawaii has to offer. We are situated between Honaunau (Place of Refuge) and Kealakekua Bay at an elevation of 900 ft. Your beautifully decorated suite has a living room with a queen sofa...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Captain Cook
HAWAII LAW COMPLIANT !! We received the original and the renewals of the highly sought after short term vacation rental seal of approval/certificate from the County of Hawaii meeting all their stringent requirements. When you rent our home ...
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Captain Cook
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 12
Hawaii> Captain Cook
Aloha & Welcome to Maka Nui. The name means Big Veiw which you will see as soon as you enter thru the gate to our paradise. The home sits on the cliff overlooking Kealakeakua Bay which is a marine preserve with wonderful snorkeling & kayaking. ...
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Captain Cook
Beautiful island style cottage with sunset views and tropical garden. If you want to experience true Hawaiian living in an eco-friendly natural, non smoking environment this is your place. Have a soak in the tub, pick fresh herbs, veggies and ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Captain Cook
Your hosts Tammy and Joe are committed to providing a 5-star experience. The home is maintained in immaculate condition with the highest levels of spotless cleaning before you check in. The property is very private with secure gated ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Captain Cook
If you are adventurous and free spirited, but still require all the amenities a luxury rental home has to offer then Pu`uhonua ('Place of Refuge') is the place for you. Experience tropical Hawaii at it’s finest in this unique pole house ...
4 bedroom
3 bathroom
Sleeps 10
Hawaii> 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 ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> 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
Hawaii> Captain Cook
This rustic wooden mountain cabin is situated next to a forest, in a secluded spot away from the main house, on a 3.5 acre farm. At an elevation of 2,100 feet, it avoids the summer heat at sea level ('Cool and peaceful..' - Teri), yet is close ...
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Captain Cook
STVR 19-360891, NUC 19-1179 From home to ocean in under a minute! This newly built custom home is just 200 feet from the ocean and located in the heart of authentic Old Hawaii - Captain Cook locale on the Big Island of Hawaii. An ...
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Captain Cook
Front row views of Kona sunsets Our two-story oceanfront home has views from every room and tropical breeze flowing from roof top cupola through every window and door. The main living area is upstairs with 4 bedrooms and 3 ...
4 bedroom
4 bathroom
Sleeps 9
Hawaii> Captain Cook
Special rates are available for Summer and Fall! Book now and enjoy an incredible adventure on beautiful Hawaii Island! Manini Point House is the perfect location for friends and family of all ages. Manini Point House is located in a village ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Captain Cook
Everything you might need to enjoy an exciting or relaxing vacation right by the ocean at the beautiful and world famous, "Kealakekua Bay'. This private location is perfect for adventuring around our quaint neighborhood, with quiet walks, ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
'HALE GEDIMINAS' vacation rental is in a lush tropical setting on sunny Kona Coast. Spectacular sunsets on private lanai with ocean view overlooking Ili-Ili beach, which is 5 min down by car. The unit is studio like with private entrance, has a...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
Please check out the main house on the same property for larger groups: vrbo 1542506 This cottage is perfect for a couple looking to save money but still have a million dollar view. In Kealakekua Bay, just 3 minutes from some of the ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
Welcome to PonoMana Oasis. A perfect and secluded destination if you are coming to Hawaii for a personal retreat, vacation or much-needed rejuvenation. Our eco-friendly oasis is within minutes of some of the island’s best waters to ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook