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 (`headline` like '%Kona Bali Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Your home away from home in Kailua-Kona with 2 gorgeous bedrooms featuring 2 king beds. This fabulous 3rd floor oceanfront condo offers all the essentials for a comfortable stay, including WiFi, AC, and full-size washer/Dryer. The cool vibe of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Welcome to Bali Kai 411, an oceanfront condo offering a peaceful retreat with panoramic views of the Pacific Ocean. Located on the beautiful Big Island of Hawaii, this spacious 2-bedroom, 2-bathroom unit is the ideal getaway for families, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Imagine yourself with a cool tropical drink in hand, relaxing on your private lanai just 30 feet from the ocean . . . the warm ocean breeze blowing through your hair, and the calming sound of the waves washing ashore . . . this doesn't have to ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Absolute oceanfront top floor condo with a big lanai (balcony) boasting an even bigger view. Located on the top floor of a four-story building, this oceanfront condo boasts first class views as well as excellent proximity to Kona Town and all ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Aloha! Come enjoy our expansive, nearly 180-degree, west-facing oceanfront condo located along a charming 2-lane road with plentiful coffee shops, restaurants, and art galleries nearby. We are friendly to all, and look forward to you enjoying ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Summary:
Oceanfront 1-bedroom, 2-bath condo in Kailua-Kona, Ali’i Drive location. Sleeps 2, with master bedroom ocean sounds. Stunning ocean views from kitchen, living room, and master bedroom. Lanai overlooks tidal pools and surf break. ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Lovely Oceanfront 3rd floor 2bd/2bth Beautifully maintained unit in Kona Bali Kai, one of Kona's most popular oceanfront destinations. New mini split level A/C system keeps the unit cool. Watch the dolphins, whales (during winter season) ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Knutson & Associates is proud to be representing Kona Bali Kai#204, an incredible DIRECT OCEANFRONT 2 bedroom, 2 bath condominium in the Kona Bali Kai Development.
This spectacular property has an amazing lanai with incredible ocean ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Kona Bali Kai #234
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
As part of the exclusive Kona Bali Kai Resort, this updated studio features...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Breath in that Beautiful Ocean Sprayfilled Air at this Oceanfront 2nd Floor Amazing Condo with Lanai Boasting Stunning Sunsets & Vews of the Kona Coast. Excellent proximity to Kona Town, golf, Ali'i drive beaches. Whales, surfers, spinner ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Enjoy Ocean views from the private lanai when you settle into this newly renovated Kona Bali Kai condominium with a sofa bed and a fully equipped kitchen. Just walking distances to beaches. This condo is also located on Ali i drive!
What s ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Welcome to Kona Bali Kai #332! Enjoy ocean views from your lanai at this studio. There is a futon, a window air conditioning unit and a kitchenette. You can walk across the street to the beach or enjoy the pool, hot tub, and BBQ ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Kona Bali Kai 167: Explore downtown Kailua-Kona easily from this comfortable ocean-view location. Upon entry, guests will see the crisp blue views from the floor-to-ceiling glass doors. Tile flooring and charming light rattan furniture ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Kona Bali Kai 263: Spend your Kona days lounging in this air-conditioned 630 square foot studio decorated in calming earth tones. Here guests will enjoy the sliding glass doors that open onto a private lanai and features partial ocean views. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Discover the perfect blend of island living in this gorgeous fully remodeled studio unit at Kona Bali Kai with Ocean views located on the desirable ground floor. This beautiful unit offers just what you need for your Vacation: Ocean Views, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
