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, rewritten_description,
(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
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 a new kitchen, bath and flooring. It has a a king sized bed and hide-a-bed in the living room for
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
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
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
Unit#116 at Alii Villas is a newly remodeled beautiful unit. This adorable condo offers an inviting blend of modern updates and island living.
Recently refreshed with stylish upgrades, including new appliances and fixtures, this unit is
- 2 bedroom
- 1 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
Lokahi at Alii Villas - Kona vacation rental on oceanfront property w/pool. UPDATE: This condo now has AC! It is equipped with a 12,000BTU unit located in the living room.
Welcome to Alii Villas 123, a beautifully upgraded
- 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
Welcome to your private paradise at Ali'i Villas 326, where ocean views greet you from every angle—whether you're relaxing on the lanai, lounging by the pool, or even parking your car! This airy, spacious third-floor condo offers a front-row
- 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
Oceanfront Paradise at Alii Villas – A Serene Retreat
Nestled along the scenic Alii Drive, this beautifully maintained oceanfront condo in Alii Villas offers a tranquil escape with stunning views. Relax by the saltwater swimming pool,
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua 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
Oceanfront Luxury 2 bedroom, 2 bathroom Condo at Alii Villas – Your Perfect Hawaii Getaway. Unit#135 is an incredible unit located in the southern-most oceanfront building and is unique in that it has south-facing coastline views and
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAILUA KONA
