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, 20
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
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
Direct oceanfront, 2 bedroom, 2 bath, sleeps 6, This direct oceanfront unit on the 3rd floor (with elevator) overlooks a large lava tide pool that is frequented by turtles and colorful Hawaiian reef fish. From the lanai watch surfers tackle the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Our studio is the perfect place to sit back, relax, and enjoy in an intimate setting in Hawaii! Planning on exploring different beaches and local stores? There is a Trolley that goes along Ali'i Drive and travels the scenic routes through Kona ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
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
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Book 5 nights and enjoy the 6th night free or a Free Late Check Out! Valid for new reservations from 8/6/24 for stays between 8/6/24 - 11/21/24.
Welcome to Kona Bali Kai #332! Enjoy ocean views from your lanai at this studio. There is a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Kona Bali Kai unit #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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
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 ...
- 1 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
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
