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 '%Keauhou Akahi%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Come enjoy paradise at Keauhou Akahi unit #A408, at this two-bedroom, two-bathroom property. Enjoy your private lanai, with ocean views, and BBQ!
Keauhou Akahi is conveniently located in the Keauhou area of Kona. You are a close drive or ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Floor-to-ceiling windows in every room are open to the gentle ocean and mountain breezes in this bright, airy and beautifully appointed 2nd floor unit on the 9th tee of the Kona Country Club golf course overlooking tranquil Keauhou Bay in the ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Overlooking the 9th fairway of the Kona Country Club Golf Course, this lovely ocean-view condo is a tropical paradise. The path to the door is lined with lush hibiscus, fragrant ginger, magical monstera, and dozens of other Hawaiian plants. As ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Fully Furnished Condo, with easy access. Our remodeled unit features recently upgraded master bedroom suite and full bath. Well maintained and meticulously cared for and just a few steps to the pool. Beautiful, ideal location above Keauhou Bay; ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
In the historic town of Keauhou, the birthplace of King Kamehameha III and a popular hideaway for native Hawaiian royalty, this charming two-bedroom, two-bathroom condominium unit on the complex’s first floor treats you to unobstructed golf ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Welcome to Keauhou Akahi by Gather, a spacious condo with beautiful, sweeping ocean views overlooking the renowned Kona Country Club Golf Course. Appointed with a large lanai, living area, and sleeping accommodations for up to 6 guests, there is...
- 1 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Overlooking the 9th fairway of the Kona Country Club Golf Course, this lovely ocean-view condo is a tropical paradise. The path to the door is lined with lush hibiscus, fragrant ginger, magical monstera, and dozens of other Hawaiian plants. As ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
*SPECIAL SUMMER RATES - BOOK 4 NIGHTS GET THE 5TH FREE*
Nestled in the tranquil settings of Keauhou, our delightful ground-floor condo invites you to indulge in the beauty and relaxing atmosphere of Kona, Hawaii. Unit 103 at Keauhou Akahi ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
