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 '%Kona Bali Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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
HI> 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 with ...
- 2 bedroom
- 2 bathroom
- Sleeps 2
HI> 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 6
HI> 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 6
HI> 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
HI> Kailua Kona
Come to kona and rent this beautiful condo in a great central location close to downtown, Magic sands beach, Kahalu'u bay.
It's right across the street from the ocean, Banyans surf spot and outside BBQ .
There is also one of my favorite ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> 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
HI> 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 penthouse boasts first class views as well as excellent proximity to Kona Town and ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
HI> Kailua-Kona
Knutson & Associates is proud to be newly 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
HI> Kailua Kona
Lovely Oceanfront 3rd floor 2bd/2bth Beautifully maintained unit in Kona Bali Kai, one of Kona's most popular oceanfront destinations. Watch the dolphins, whales (during winter season) surfers, and those magnificent Kona Sunsets right from your ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
Spend your Kona days lounging in this air-conditioned 630 square foot studio with tasteful décor, WiFi, flat screen TV and private lanai. The fully-equipped kitchen includes a 4-top stove with oven, microwave, and all the dishware and cookware ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
