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, 20
Open the door Hali'i Kai15B and feel the Aloha greeting of the crashing blue waves! Relax in the serenity of your luxury retreat-the private lanai is on the water's edge-perfect for alfresco dining and the most perfect view of Hawaiian ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Village
Don’t miss out on this opportunity to enjoy the best view in the Waikoloa Beach Resort. This villa has the view that is pictured on all the Waikoloa Beach Resort publications and offers amazing sunset views. Look out over the signature hole ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
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
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
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
