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 Villas%') AND sleeps>=1 order by rating desc LIMIT 0, 50
NEW STVR -19-359761
Aloha! Our beautiful condo is a favorite with couples and we assure you, our amazingly low nightly rate is NOT too good to be true. You can be one of the many to check out and rate our immaculate, comfortable, one ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Come enjoy a fabulous time in an ocean front property in Kailua-Kona Hawaii. This unit is a 2 bedroom, 2 bath home at a premier location at Ali'i Villas. The décor of this unit has been done in cool coastal and has just been remodeled top to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Fabulous 2nd story condo with an oceanfront view! Located on the desirable Kailua Bay side of Alii Villas Condominiums. Beautifully decorated and well appointed for your comfort while on the Big Island. Fully furnished with everything you need ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Experience breathtaking ocean views from our bright and spacious condo. This ground-floor unit features 2 bedrooms and 2 baths, just steps from the ocean—perfect for unwinding after a day of island adventures. Located conveniently in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Brand new to the rental market! This newly remodeled ground floor one bedroom condo is perfect for your island getaway. Comfortably furnished and well equipped, just bring your suitcase Short walk to your parking space and pool area.Perfect for...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Welcome to Alii Villa Unit #333, your perfect vacation rental in Kailua Kona! This exceptional corner unit boasts stunning oceanfront views, providing a breathtaking panorama of Kailua Bay and the picturesque Kona coast.
Location: ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
This cute one bedroom condo is on the third floor with pool and ocean views at this ocean front complex. It has been completely renovated with new kitchen, bath and flooring. It has a a king sized bed and fold out couch in the living room ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
This is one of the most stunning oceanfront condos in Alii Villas.
OUR RESPONSE TIME is less than one hour on inquiries!!
Totally upgraded and set up for your comfort. Sleeps 4 people comfortably with walk in showers in both bathrooms, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
This is one of the most stunning oceanfront condos in Alii Villas.
Totally upgraded and set up for your comfort. Sleeps 4 people comfortably in 2 queen beds, walk in showers in both bathrooms, high end appliances and fully equipped kitchen. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Alii Villas#127: Exceptional, Direct Oceanfront Living at Popular Alii Villas Condominium Complex!
Experience ultimate island living in this completely updated oceanfront two-bedroom condo. Conveniently located on the first floor, unit 127 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Alii Villas 123 is a ground floor one bedroom / one bath unit with easy access from the designated parking stall. This island style condo features a fully stocked kitchen with Wi-Fi, Smart TVs, King size bed in living room and pull out Queen in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Ocean views from the lanai, the bedroom, the pool, the BBQ, and even from the parking spot! Welcome to our Ocean Oasis in Kona, where you have a private box seat to watch the spinner dolphins, humpback whales, outrigger canoe races, or the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
WOW! Gorgeous Kailua Bay views from this 2 bedroom, 2 bathroom fully furnished oceanfront condo located on Ali'i Drive in Kailua-Kona!
Spinner dolphins are visible from the spacious lanai while sipping Kona coffee or cocktail! This is a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Located in lovely maintained oceanfront resort complex called Alii Villas on Alii Drive, less than one mile south of downtown Kona 30 NIGHT MINIMUM!, $2400.00 per month . Oceanview swimming pool and barbeque area to relax and work on your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kialua-Kona
This top floor Alii Villas condo, Unit #340, has NEW owners and a NEW look. It welcomes visitors with a bright, clean, open air floor plan that boasts a large lanai facing the ocean. Watch the beautiful western evening sky from your balcony or ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
vrKCV - Alii Villas 107 - Professionally Managed by Kona Coast Vacations
Alii Villas 107 in Kona, Hawaii: Your Tropical Paradise Awaits!
Welcome to Alii Villas 107, your perfect escape to paradise in Kona, Hawaii! This ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Upgraded garden view unit with a peek of the ocean. A short 2 minute walk to the waterfront seating area.
OUR RESPONSE TIME is less than one hour on inquiries!!
Top floor. well equipped condo with everything you need for a comfortable ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Totally updated top floor condo with an ocean view! #322 boasts a mix of Tropical and Balinese style furnishings. This is not your typical one bedroom unit in Alii Villas. Fully equipped Kitchen, separate laundry room, and lots of extras
Lush...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Alii Villas, Unit#304 is a fantastic option for your Hawaiian Adventures.
Discover your perfect island retreat in this charming top floor garden view condo nestled in the heart of Alii Drive. With lush tropical landscapes right outside your ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> KAILUA KONA
