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 Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Welcome to your island-style home away from home with tropical-inspired bedrooms, luxe master bath, gourmet kitchen, open living area and lanai with gas grill. You’ll love the convenient dishwasher, air conditioning, ceiling fans, laundry room...
- 3 bedroom
- 2 bathroom
- Sleeps 8
HI> WAIKOLOA
Blissful island living defines this air-conditioned 1,082sf suite with Pacific view from the ground floor lanai. You’ll love the tropical decor, open-style gourmet kitchen for meal ease, washer/dryer, ceiling fans, WiFi, flat screens and roomy...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> WAIKOLOA
With its nautical theme, this laid-back retreat welcomes with living/dining area opening to the gourmet kitchen, plus a lanai, laundry room and comfy master bed/bath combo.
Homey touches include flat screens, air conditioning, ceiling fans and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> WAIKOLOA
Slip into island mode within this air-conditioned retreat with three-seat recliner sofa, open gourmet kitchen with dishwasher, lanai with gas grill and sleigh frame in the tropical master bedroom. Other amenities include high-speed Internet, ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
HI> WAIKOLOA
Covid-19 Notice: Available amenities vary at each Complex, many have restricted use. Please inquire with KCV.
Hali'i Kai unit 16C - Beautiful ocean view condo in Waikoloa Beach Resort. Hali'i Kai is one of only two oceanfront properties in ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa
Halii Kai 13A. Includes Waikoloa Gold Golf Membership Discounts
Please scroll down for detailed information
***PLEASE NOTE: Due to current Covid 19 protocols there may be modified hours for Pool Services, Fitness Rooms or Community/BBQ Areas...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Waikoloa Beach Resort
Halii Kai 11F offers a great combination of space and view. This three bedroom townhouse style villa overlooks the fairway and signature hole of the Waikoloa Beach golf course and has an amazing ocean view. Enjoy watching the surf come in and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Waikoloa
È Komo Mai! We are pleased to welcome and invite you to stay in our oceanfront villa in Waikoloa Beach Resort on the Big Island. You will feel relaxed and inspired in our Hawaiian island retreat. Hali’i Kai is the only true oceanfront ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa
Ground Unit #9B Oceanview condo in Halii Kai, famed newer development near Waikoloa Hilton and Mauna Lani beach homes. Halii Kai has an incredible salt water pool, spa covered workout room, poolside restaurant/bar right on ocean front. The ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa
Covid-19 Notice: Available amenities vary at each Complex, many have restricted use. Please inquire with KCV.
Sitting perched above the surf, Hali'i Kai 15F, provides one of those rare oceanfront views. This 3-bedroom 3-bathroom penthouse has ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Waikoloa
Luxury Hali’i Kai oceanfront homes are located on the Kohala Coast of Hawaii’s Big Island, approximately 18 miles from the Keahole Kona International Airport. Birthplace of the great King Kamehameha I, tales of the favored status of this ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Waikoloa Village
Premium Ocean View
Residential interior by Fine Design Hawaii.
Features:
- Largest floor plan at Hali'i Kai - 1930 square feet including lanais
- Sunset and Ocean views from both lanais
- Directly across from the pool/Ocean Club
- Weber ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Waikoloa
Hali’i Kai oceanfront homes are located on the Kohala Coast of Hawaii’s Big Island, approximately 18 miles from the Keahole Kona International Airport. Birthplace of the great King Kamehameha I, tales of the favored status of this stretch of...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Waikoloa Village
Covid-19 Notice: Available amenities vary at each Complex, many have restricted use. Please inquire with KCV.
Hali'i Kai 10E – Ocean, Golf & Sunset Views, Close to Pool, Garage included
Welcome to Villa 10E. Walk out the lanai to view the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa Village
Do not miss out on this opportunity to stay at one of the premier villas at Hali'i Kai at Waikoloa Beach Resort! Hali'i Kai is just minutes away from sandy beaches, world class golf, hotels and restaurants. One of the wonderful features of ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa
