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, custom_url, ai_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 (city = 'Waikoloa Village' OR postalCode = 'Waikoloa Village' OR region like '%|Waikoloa Village|%') AND sleeps>=1 AND (
(postalCode NOT IN('96701','96706','96707','96712','6717','96730','96731','96734','96744','96782','96786','96789','96792','96826','96795','96797','96813','96814','96815','96816','96817','96818','96819','96821','96822','96825')
AND city NOT IN('Aiea','Ewa Beach','Haleiwa','Hauula','Hawaii Kai','Honolulu','Kaaawa','Kahala','Kahuku','Kailua','Kaneohe','Kapolei','Laie','Manoa','Makaha','Mililani','Nanakuli','Pearl City','Wahiawa','Waialua','Waianae','Waikiki','Waimanalo','Waipahu','Lanikai','Maili')
) OR (registration_number like '%TA-%' or registration_number like '%TA %')
) order by rating desc LIMIT 100, 50
Aloha! Welcome to Hali'i Kai, named the #1 resort on the Big Island, located in Waikoloa on Kohala's Gold Coast, renowned for its 350 days of sunshine annually. This gated resort offers privacy and exclusivity, featuring a private restaurant and bar, and an Oceanview Club with a tiered pool, fitness center, tennis courts, and more, exclusively for guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Escape to paradise at Waikoloa Colony Villas, a gated community within the renowned Waikoloa Beach Resort. This beautifully furnished 2-bedroom, 2-bath condo offers the perfect setting for a relaxing Hawaiian vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to this spacious and beautifully decorated vacation home that combines Hawaiian charm with Southeast Asian influences. The house offers stunning sunset views from the lanai, perfect for relaxing evenings. Inside, enjoy new tile floors and a tiled shower in the master suite, creating a modern and comfortable atmosphere.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa Village
This beautifully maintained vacation rental at Halii Kai 13E offers an exceptional oceanfront experience in the Waikoloa Beach Resort on Hawaii's Big Island. Managed professionally by ResorticaHawaii, this condo features a spacious layout with a master bedroom equipped with a king bed, two guest bedrooms—one with a queen bed and the other with two twin beds—and a comfortable living room with a sleeper sofa, accommodating up to eight guests.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa Village
The Waikoloa Colony Villas 304 offers a spacious, townhouse-style accommodation nestled within a secure gated community on the stunning Kohala Coast of the Big Island. This professionally managed property provides guests with a comfortable and convenient base for exploring Hawaii's beautiful landscapes and vibrant local culture.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
This beautifully maintained ground-floor condo at Waikoloa Beach Villas offers a perfect retreat on the Big Island. Managed professionally by ResorticaHawaiiWaikoloa, this property combines comfort, convenience, and luxury.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Kolea 2F offers a fully furnished and beautifully decorated penthouse designed by local interior designer Mealani Morgan, situated within the exclusive Waikoloa Beach Resort. This upscale condo features custom stone floors, Sochi artwork, and Tommy Bahama ceiling fans, creating a sophisticated and relaxing atmosphere.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover paradise in this beautifully furnished one-bedroom, one-bath condo at Waikoloa Villas. Situated on the ground floor, enjoy breathtaking views of the Waikoloa golf course and the ocean from your spacious lanai, perfect for relaxing mornings or sunset cocktails amidst lush tropical grounds.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Experience the ultimate getaway at Halii Kai 14B, a beautifully appointed oceanfront condo nestled within the gated Waikoloa Beach Resort on Hawaii's Kohala Coast. This spacious unit offers breathtaking views of the Pacific Ocean, creating a perfect setting for relaxation and adventure.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Welcome to Waikoloa Beach Villas K22, a beautifully located 2-bedroom, 2-bathroom condo nestled on the 3rd Fairway of the Lakes Course. This inviting unit features a spacious lanai and a BBQ grill, perfect for outdoor dining and relaxing after a day exploring the Big Island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
5-bathroom townhome in Waikoloa Village, perfect for families and friends seeking a tropical retreat. The property offers a spacious, naturally lit interior with modern comforts and outdoor living spaces. Inside, you'll find a fully equipped kitchen with stainless steel appliances, a cozy dining area, and smart TVs for entertainment.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover the perfect Hawaii retreat at this stunning Waikoloa Beach townhome. This 2-bedroom, 2-bath property offers access to community pools, hot tub, tennis and pickleball courts, and a fully equipped kitchen. Relax on the private lanai with outdoor dining or unwind on the furnished balcony.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the perfect getaway at our beautiful Big Island Hawaii home, ideally located on the central west coast near stunning white sand beaches. Wake up to breathtaking ocean views from the front porch while sipping your morning coffee, or unwind in the evening with sunsets from the patio, grilling your favorite meals.
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the beauty of Hawaii at Fairway Villas Waikoloa Beach Resort K22, a spacious and well-appointed 2-bedroom, 2-bath condo located on the second floor with elevator access. This home away from home offers a blend of comfort and luxury, featuring a modern kitchen with new stainless steel appliances installed in 2022, luxury linens, and contemporary furnishings.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome to our tropical paradise! This private, quiet end unit condo is nestled on the Robert Trent Jones Jr. golf course in Waikoloa Village, offering stunning views and a peaceful retreat. Spanning 1210 sq ft, the two-story condo features an open living space with 18-foot wood ceilings, creating a spacious and airy atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Discover your perfect Hawaiian retreat at our spacious 2-bedroom, 2-bathroom villa located in the peaceful "B" building of Waikoloa Fairway Complex. Situated on the ground floor, this condo offers easy access to the lush grassy area, ideal for outdoor games or relaxing evenings star gazing.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Welcome to Kailana, a charming and well-maintained one-bedroom condo offering breathtaking views of the Pacific Ocean, Maui, and Kohala Mountain. Located right on the Village Golf Course at Waikoloa, this private retreat is ideal for couples seeking a peaceful escape.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waikoloa Village
Discover this beautiful ocean-view condo nestled in a private gated community on the Kohala Coast. Featuring two bedrooms and two bathrooms, this home offers everything needed for a relaxing getaway. The fully equipped kitchen makes meal prep effortless, while resort-style beds and plush furniture in the living area and on the private wrap-around lanai provide comfort and space to unwind.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Escape to this luxurious condo nestled within the exclusive Waikoloa Beach Villas at the Waikoloa Beach Resort. This private gated community offers a perfect blend of comfort, style, and convenience, just steps from top resort amenities and stunning beaches.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to this newly listed, tastefully decorated spacious studio perfect for a relaxing getaway. Featuring air conditioning and modern amenities, it comfortably sleeps up to 3 guests with an additional sleeping option on a fold-out futon. Strategically located just 15 minutes from renowned beaches and five minutes from shopping centers, you'll have easy access to a variety of activities and attractions.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waikoloa Village
Escape to Paniolo Greens at Waikoloa C212, a recently renovated corner condo offering a perfect blend of comfort and island charm. This spacious, well-appointed retreat features colorful artwork, warm wood finishes, and stunning views of volcanoes and golf courses from the shaded, covered lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
This inviting ground floor end-unit condo offers easy access and a comfortable layout for a relaxing Hawaiian vacation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Escape to our charming island-themed townhouse in Waikoloa Village, offering a spacious 1100 sq ft end unit with endless windows and stunning views. The condo is situated on the second floor, accessible via 10-12 stairs, and provides a comfortable, open living space designed to make you feel at home.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
This charming ground-level villa offers a prime location with direct access to a large, open grassy area from the lanai, making it perfect for children and those seeking a peaceful, scenic setting.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Why choose this condo on the Big Island? Located in North Kohala, about 30 miles from Kona and 100 miles from Volcano, this bright corner unit offers spectacular views of mountains, ocean, and lush terrain. Its superb central location near Waikoloa Village provides quick access to top sandy beaches and local attractions.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the best of Hawaii from this charming Waikoloa condo, built with over 20 years of personal island experience. Located close to some of the finest beaches and renowned scuba diving spots, this unit offers an authentic and comfortable stay.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Experience the perfect family vacation at Waikoloa Beach Villas E1, an spacious end-unit condo located in the heart of Waikoloa Beach Resort on the Big Island of Hawaii. Professionally managed by ResorticaHawaiiWaikoloa Beach Villas E1, this residence offers a comfortable haven with modern amenities and easy access to all resort features.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Experience the luxury of this spacious two-bedroom, two-bath condo located in the heart of Waikoloa Village. Situated just across from the renowned Hilton Waikoloa and near the Mauni Lani and Fairmont Orchid Resorts, this condo offers an ideal Hawaiian getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the perfect Hawaiian getaway at Waikoloa Beach Villa F23, located in the renowned Waikoloa Beach Resort on the stunning Kohala Coast. This beautifully upgraded 2-bedroom, 2-bath villa offers a blend of comfort, elegance, and Hawaiian charm.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to Shores at Waikoloa 328, a beautifully appointed condo nestled in the heart of the Waikoloa Beach Resort on the Big Island of Hawaii. This third-floor unit in the Banyan Tower offers a perfect blend of comfort, convenience, and paradise settings for your Hawaiian holiday.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to your dream vacation at Hali'i Kai, a luxurious 29-acre oceanfront resort nestled within Waikoloa Beach Resort on the beautiful Big Island. This spacious condo features two bedrooms—one with a king-sized bed and another with twin beds or a king, based on your preference—perfect for families or groups seeking comfort and style.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Experience the perfect Hawaiian getaway in Kolea 14A, a spacious and luxurious ground floor condo located in the gated Waikoloa Beach Resort on the Kohala Coast. This professionally managed unit offers a comfortable and stylish retreat, ideal for families or groups seeking a relaxing vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome to your Hawaiian retreat at Waikoloa Hills, a vibrant community offering a pool, hot tub, and discounted golf across from Highlands Shopping Center. This single-level, ground-floor condo provides easy access and a spacious lanai perfect for morning coffees or evening cocktails.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
The Waikoloa Fairway Villas offer an ideal location for exploring the Big Island. Just across the street from the renowned Anaeho'omalu Beach, also known as A-bay, this condo puts you within walking distance to top shops and restaurants in Waikoloa Beach Resort.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Escape to Waikoloa Villages E108, a delightful island condo offering stunning views of the sixth hole at The Village Course. Whether you're relaxing on the private furnished patio or enjoying the shared amenities, this property provides the perfect base for your Hawaiian vacation.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to Waikoloa Villas H202, a beautifully appointed three-bedroom, three-bath condo nestled in the peaceful Waikoloa Villas community. This spacious unit features soaring 18-foot wood ceilings, large picturesque windows, and sliding glass doors that flood the open-concept living area with natural light.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This newly remodeled condo offers stunning ocean views, central air conditioning, and upscale furnishings for a comfortable stay.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover this beautifully remodeled one-bedroom condo located in the gated Fairway Terrace complex in Waikoloa Village. Situated at 800 ft elevation, the condo offers a cool, breezy atmosphere with lovely mountain and ocean views. The unit is on the upper floor of building G, featuring a spacious lanai perfect for morning coffee or sunset watching.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waikoloa Village
Imagine your dream vacation in a private Hawaiian resort where you wake up each morning to enjoy your Kona coffee in a lovely 3-bedroom, 3-bath condo. This spacious garden-level unit offers 2,138 sq. ft. of luxurious living space, featuring original artwork by local artists throughout that reflects the vibrant Hawaiian culture.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the pinnacle of luxury living in our meticulously selected second-floor condo, ideally situated at the end of the complex for maximum privacy and breathtaking views. Located just minutes from the beach, it offers a tranquil retreat with sweeping vistas of the golf course lake, lush fairways, and the distant ocean horizon.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Discover paradise at Vista Waikoloa, a luxurious condo nestled in a tranquil community just steps from Hawaii's stunning beaches. This spacious, modern retreat offers the perfect blend of comfort and convenience for your Hawaiian vacation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover this spacious ground floor condo featuring a large attached lanai with breathtaking views of the golf course, green fields, and mountains. Just minutes from A-Bay Beach, shops, and dining options, this property offers the perfect blend of privacy and convenience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome to our charming single-story, ground-floor condo located at Waikoloa Villas in Waikoloa Village, offering breathtaking views of the ocean and the nearby golf course. This inviting two-bedroom, two-bath unit provides a perfect retreat for relaxation and adventure on the Big Island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to Hale A Ka La (House of the Sun), a beautifully appointed, private town-home nestled on the 9th hole of Waikoloa Beach Golf Course. Managed professionally by Kona Coast Vacations, this spacious residence offers a perfect blend of comfort, privacy, and tropical elegance.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Immerse yourself in tropical luxury at this stunning two-story villa located in the exclusive, gated Waikoloa Beach Resort community. As the closest villa to the ocean, it offers an unbeatable location within walking distance to pristine beaches, world-class golf courses, upscale resorts, fine dining, boutique shops, art galleries, and grocery stores.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome to our beautiful villa at Waikoloa Beach Villas, nestled in the heart of the renowned Waikoloa Beach Resort on Hawaii's sunny Kohala coast. Owned by Canadians Moreno and Erika from Vancouver, this spacious 2-bedroom, 2-bath unit offers a perfect blend of luxury and comfort with tropical decor and modern amenities.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience a luxurious stay at Halii Kai, a premier oceanfront resort on the Big Island's Kohala Coast. This professionally managed 3-bedroom condo offers stunning ocean views and top-notch amenities.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Shores at Waikoloa 317 is a beautifully remodeled condo located on the third floor of the Tamarind Tower within the gated Waikoloa Beach Resort on Hawaii's Big Island. The unit features a comfortable master bedroom with a king-sized bed, a living room with a sleeper sofa and twin fold-out chair, making it ideal for families or groups.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Experience the best of Big Island at Shores Waikoloa 136, a spacious ground-floor condo offering comfort and convenience. This large, open-plan unit is perfect for families or couples seeking a memorable vacation. Enjoy access to a refreshing outdoor pool and a well-equipped fitness center for your morning workouts.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This spacious 3-bedroom, 3-bath condo located in Waikoloa Village within the Paniolo Greens complex offers a perfect Hawaiian getaway. With a prime location just minutes from stunning beaches, shopping centers, and outdoor adventures, this condo is ideal for families and groups seeking comfort and convenience.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
