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 '%Alii Cove%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Welcome to our beautiful Alii Cove Townhome! The location of this unit in the complex offers up some of the best ocean views Alii Cove has to offer! This Gated Community is located on Alii Drive across the street from Hon'ls Beach and a five ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Beautiful, complete, modern three bedroom condo in Kailua-Kona. Our ocean view is spectacular! 3 min. walk to ocean waterfront (amazing sunsets!), easy 10 minute walk to downtown Kona where you stroll along waterfront to many unique ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua-Kona
Hawaii Travel Update: Travel to Hawaii is becoming less restrictive. Beginning September 1, all travelers arriving in Hawaii from out-of-state will be required to get a valid COVID-19 test within 72 hours of their trip, and to show proof of a ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Welcome to our beautiful Alii Cove Townhome! This Gated Community is located on Alii Drive across the street from Hon'ls Beach and a five minute walk to the Village of Kailua-Kona, where you will find numerous restaurants, shopping, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Luxurious top floor condo, Unit H-23 (Sleeps 4 with Futon in Loft for an additional guest) The short walk to town from here offers a variety of restaurants (Outback, Bubba Gumps, Kona Inn and the famous Huggo's for sunset and live music), lots ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
This Alii Cove unit provides spectacular sunset views! This particular unit is very quiet and has an OCEAN VIEW from the main lanai and upper lanai. The townhome comes with anything you could possibly need for an extended stay, including super ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Beautiful High end 3 bedroom condo, only two blocks from town.
180-degree ocean and bay views with golden sunsets with the sound of breaking blue crystal waves on the shore. 1,800 sq' feet Townhouse end unit with 3 bedrooms and 3 full baths ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua-Kona
Located in Kailua-Kona, this 3-bedroom, 3-bathroom vacation home lets you experience it all. Fans of nature and the outdoors will love being 6 minutes by foot from Honl's Beach and 6 minutes by car from Magic Sands Beach. You'll easily make the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Light and spacious 3 bedrooms and 3 full baths + Loft. One of the best locations at Alii Cove. Condo is an end unit and easy walking distance to pool just 1 building over. Easy walk to the beach right across the street. No walking up and down ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua-Kona
Alii Cove is a gated community walking distance to anything you need including Honl's beach, which is right across the street! This 2nd story unit comes with high speed wifi and has a loft that is set up as an office, convenient for working ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Welcome to our beautiful Alii Cove Townhome! This Gated Community is located on Alii Drive across the street from Hon'ls Beach and a five minute walk to the Village of Kailua-Kona, where you will find numerous restaurants, shopping, ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
