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 '%alii cove%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This 3 bedroom/3 bath luxury townhome is located in the gated Alii Cove Condominium complex and is a short walking distance ΒΌ mile south of the town of Kailua-Kona where you will find fine ocean front dining, shopping, and coffee shops for that...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> 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 (Papa Kona, Kai, Kona Inn, Royal Kona and the famous Huggo's Restaurants for sunset and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Alii Cove#G-3 is an excellent beautifully furnished option for your Vacation. This spacious 2 bedroom, 2 bath unit has fantastic features that many guests enjoy. A perfect option for up to 6 people.
~No stairs! Walk right into the unit, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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 8
Hawaii> Kailua-Kona
Light and spacious 3 bedrooms and 3 full baths + Loft with 1815 square feet of interior living space.
Alii Cove one of Kona's best locations. Featuring a secure gated community with second entrance. Townhouse is an end unit private and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Alii Cove J-4 - Beautiful high-end 3 bedroom 3 bath townhouse, only two blocks from town. This condo is located at Alii Cove upper drive, top of the complex in historic Kailua Village and offers the best Kona Coast ocean views you can get from ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Alii Cove C-4 Townhome is beautifully decorated, comfortable, and is set up for entertaining large groups of people with that homey beach house feel. The Primary suite is very spacious and gives off a very glamorous and luxurious feel with the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Alii Cove#S-1 is an excellent beautifully furnished option for your Vacation. This VERY spacious, 2 story 3 bedroom, 3 bath unit has fantastic features that many guests enjoy. A perfect option for up to 8 people!
~Largest Floor Plan in ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> KAILUA KONA
Alii Cove I-4 Townhome is beautifully decorated, comfortable and set up for entertaining with a Hawaiian ambiance. It has a nice, large kitchen and great room. The Townhome features tile flooring on the main level, a Large Lanai off the Living ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Alii Cove F-1 has reduced its nightly rates for the summer and fall! Come check out this roomy 3/3 townhome at the best prices since before COVID! perfect size for families and entertaining!
Alii Cove F-1 is a beautifully appointed ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Alii Cove townhome unit KK4 has beautiful ocean views from the lanais on both the first & second levels. Located across the street from Honl"s Beach, a well known surfer's beach. The condo is a 5 minute walk to Historical downtown Kailua/Kona & ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Alii Cove#U-2 is an excellent beautifully furnished option for your Vacation. This spacious 2 bedroom, 2 bath unit has fantastic features that many guests enjoy. A perfect option for up to 6 people.
~Ocean views from this unit!
~No ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Alii Cove#Q-3 is an excellent beautifully furnished option for your Vacation. This spacious 2 bedroom, 2 bath unit has fantastic features that many guests enjoy. A perfect option for up to 6 people.
~No stairs! Beautiful Tile and new ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Escape to paradise at Ali'i Cove U23! This exquisite vacation rental offers a minimum 30-night stay, ensuring ample time to soak in all the beauty and tranquility this stunning island retreat has to offer.
Nestled in a quiet complex, this...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
