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 Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 30
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
Hawaii> Waikoloa
Island touches shine in this upscale two bedroom villa with its lanai, gourmet kitchen and comfy living area. Enjoy homey comforts like a laundry room, stylish master bedroom and bathroom, high-speed Internet and ceiling fans. Amid 29-acres ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> WAIKOLOA
Able to comfortably sleep up to 6, in one king and two twin beds, the unit is tastefully designed with earthy, stained-wood accents to exude vintage island charm. All rooms offer cozy furnishings and boast soft lighting with air-conditioning and...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Waikoloa Village
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
Hawaii> Waikoloa Village
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
Hawaii> 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
Hawaii> Waikoloa
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 13A. Includes Waikoloa Gold Golf Membership Discounts
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa Beach Resort
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
Hawaii> WAIKOLOA
Summary:
Unit D-205. Located across the street from the kamaole beach park. This property has all new furnishings and king size bed. Air conditioning, 65 in tv, full queen mattress wall bed as well as two twin sofa sleepers,, full kitchen ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 10G. Ocean Views!
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Guest ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
Kihei Alii Kai 1BD/1BA Remodeled unit with Gorgeous Ocean Views and Maui Sunsets from the Spacious Lanai.
Beautiful remodeled 1 bedroom, 1 bath fully furnished condo is ready for your Maui getaway. Enjoy dinner on the spacious lanai with ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Spacious No Smoking Condo with Cathedral Ceilings on Top Floor of Beautiful Kihei Alii Kai Complex.
Amazing Kamaole Beach just across the street!
Start your day in paradise with Coffee on the beautiful private lanai with Ocean ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
PROPERTY SUMMARY:
Serene and modern, this spacious 2 bedroom - 2 bath fully remodeled condo is conveniently located steps from an amazing selection of casual restaurants, fine dining, shopping and beach rentals, and all this directly across ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Aloha and Welcome! We are pleased to invite you to stay in our well-maintained, newly furnished villa in Waikoloa Beach Resort on the West Coast of the Big Island. This corner, ground floor unit comfortably sleeps up to 6 people with 3 bedrooms,...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
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
Hawaii> Waikoloa
Style, space and homey perks wow in this 1,930 sq ft island-chic home with master bed/bath combo, laundry room and open gourmet kitchen to prep meals for sharing on the ground floor lanai. Enjoy air conditioning, ceiling fans, HD (high ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> WAIKOLOA
The Princeville Resort is located on the beautiful north shore of Garden Island of Kauai. Surrounded by incredible beauty are the perfect backdrop for world-class golf with outstanding views and challenging play, sightseeing, and all outdoor ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 13E. Ocean Front With Stunning Views!
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Waikoloa Village
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 14B. Ocean Front With Stunning Views!
Please scroll down for detailed information
Waikoloa Beach Resort Gold Membership Golf benefits (discounted tee ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa Village
Hali'i Kai is one of the most sought out places to stay while visiting the Kohala Coast on Big Island. It features meticulous well kept grounds, beautiful and comfortable living spaces along with a luxurious amenity center, the Ocean Club. The ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa
Relax at our Whispering Palms ...home away from home. This is a ground floor, newly updated and furnished, spacious 2 bd, 2 bath that has a luxurious Queen Sleeper Sofa to comfortably sleep a new total of 6 guests!
The condo is located at ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 15H
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Guest Bedroom: 2 ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Waikoloa Beach Resort
***NON-SMOKING CONDO***
Welcome to our little piece of paradise on the Island of Maui. ..a spacious one bedroom condo with everything you need for your vacation in this paradise. Our well maintained home away...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Alii Kai II 12D Kauai condo is located in Princeville on Kauai's beautiful North Shore.
Enjoy beautiful mountan views and sunsets from your Kauai condo facing the "Bali Hai" Na Pali mountain range.
Ground floor, end unit with Bali Hai ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
This completely-remodeled, sun-lit and spacious 2-bedroom, 2-bath condo at The Point at Alii Kai (Unit 5-302) welcomes your group to an unforgettable vacation in Princeville, where you'll face jaw-dropping, direct Pacific Ocean views on Kauai's ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Prepare to shaka in this air-conditioned villa saluting surf through art and attitude! Prep meals and snacks in the gourmet kitchen to enjoy on the lanai. Then retreat to themed-motif bedrooms. Touches include a laundry room and master bath with...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> WAIKOLOA
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 8D.
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: 2 Queen Beds
Please note: ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Village
Say aloha to this island-style home away from home with tropical-inspired bedrooms, luxe master bath, gourmet kitchen, open living area and lanai. You’ll love the convenient dishwasher, air conditioning, ceiling fans, laundry room and ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> WAIKOLOA
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Alii Kai 1201 is a charming, comfortable top floor corner with a wrap-around lanai that offers spectacular mountain views on one side and ocean views on the other. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
