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, 30
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
Imagine - warm tropical climate, gentle sounds of waves washing ashore, palm trees swaying in the breeze, panoramic views, and the perfect accommodations - is there anyway else to experience Hawaii?
This two-bedroom, two-bath ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Imagine - warm tropical climate, gentle sounds of waves washing ashore, palm trees swaying in the breeze, panoramic views, and the perfect accommodations - is there anyway else to experience Hawaii?
This two-bedroom, two-bath condominium ...
- 2 bedroom
- 2 bathroom
- Sleeps 2
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
From the comfort of your lanai enjoy watching amazing surf action, beautiful ocean views, and our world class sunsets. Well equipped kitchen with everything you need to prepare delicious meals.
Master bedroom has a king bed and it's own ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
From the comfort of your lanai enjoy watching amazing surf action, beautiful ocean views, and our world class sunsets. Well equipped kitchen with everything you need to prepare delicious meals.
Master bedroom has a king bed and it's own ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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 ...
- 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
Kona Bali Kai unit #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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
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 kitchentte. 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 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
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
