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 (city = 'Waikoloa Village' OR postalCode = 'Waikoloa Village' OR region like '%|Waikoloa Village|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
This is a an amazing 1 bedroom 1 Bath ground floor unit with plenty of space.It has been fully renovated with beautiful furnishings inside and out. It has lots of windows that lead out to a spacious patio. It has central air if the cool Hawaiian...
1 bedroom
1 bathroom
Sleeps 5
Hawaii> waikoloa village
Your Island Paradise Awaits You! Feel right at home in our spacious 1700 square foot, 3 bedroom, 2 1/2 bath Villa with an attached garage. This beautifully decorated Villa is just steps away from the resort style pool featuring a lava rock ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
ROLLBACK PRICES!!!! We are offering last year room rates from May through October 2024. Check out this opportunity to experience our updated oceanfront villa! Open the door of 15B and feel the Aloha greeting of the crashing blue ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome back to the Aloha State. One thing about the space you cannot see in the pictures is that we clean green, which means no toxic chemicals in your living ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Don’t miss out on the opportunity to stay at one of the few full ocean view penthouses at Kolea. This 3 bed/3.5 bath ocean view villa is very nicely furnished. The first master bedroom has a king size bed with a LCD television. The first ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Come enjoy the beauty of Hawaii in our perfectly located condo at Waikoloa Beach Villas. Just 20 minutes north of the Kona airport you will find yourself in paradise! Our property is perfect for 2 couples or a family of 3. Decorated to ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Flawless vacations start at Waikoloa Beach Resort, from it's lush, palm-shaded grounds and lava rock swimming pools, to it's gourmet kitchens and private lanais scented by tropical flowers. This coveted 3-bedroom, 3-bath corner unit at ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Hali'i Kai Ocean Front Resort is truly a gem on the sun-drenched Kohala Coast of the Big Island in the Waikoloa Beach Resort. The properties' grounds are well-kept, and the sunset views are AMAZING! Our unit, Hale Little Blue Whale (7F), is a ...
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Aloha! Welcome to the beautiful gated community of Hali’i Kai! We are thrilled to share our lovely, 180-degree oceanfront condo with you. We hope that you take advantage of the luxury feel, without the resort price tag, and indulge in all the ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
NEW FOLD-A-WAY PANORAMIC DOORS INSTALLED, REPLACING TRADITIONAL SLIDING GLASS DOOR THAT ONLY OPENS HALF WAY. THERE ARE ONLY A FEW UNITS IN HALI'I KAI THAT HAVE THESE FOLD-A-WAY DOORS. WITH THE DOORS OPENED, IT HAS UNOBSTRUCTED COASTAL AND ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Halii Kai 10H Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!' With unique ocean and golf course views, this three-bedroom, two-bath condo at ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Our slice of Hawaiian Paradise! Third floor corner unit at Vista Waikoloa. Private lanai with beautiful views of Mauna Kean, Moana Loa and Hualalai mountains plus A Bay beach palm trees and ocean. In the winter months you can see the ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
This beautifully decorated deluxe three bedroom condo is situated in the heart of Waikoloa Beach Resort. With no units above or below, the condo offers the most in privacy. It is newly professionally decorated in Ocean colors with granite and ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
You are sure to enjoy a fantastic memorable vacation at our Waikoloa Colony Villa 3 bedroom, 2 1/2 bath townhouse at the highly popular Waikoloa Beach Resort development on the sunny Kohala Coast. This 1699 sq. ft. villa is just steps to the...
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
You'll find all the comforts of home in this tropically furnished condo with scenic views of the mountains and golf course. Picture yourself relaxing in the reclining chair in the living room, where you can look out at the golf course or simply ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Aloha! 2 Bedroom, 2 Bath Townhome located in the gated Waikoloa Colony Villas in the exclusive Waikoloa Beach Resort, rated in the 10 Top Pacific Rim Resorts. It is on Hawaii's Kohala Coast with the Big Island's sunniest beaches and weather. ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This vacation rental is professionally managed by ResorticaHawaii Waikoloa Fairway Villas L4. Please scroll down for detailed information Bedding Configuration: Master Bedroom: King Bed Guest Bedroom Upstairs: One Queen ...
3 bedroom
3 bathroom
Sleeps 8
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
Hawaiian charm and modernity! Our spacious 1446 square foot, 2 bedrooms, 2.5 bath condo at the Waikoloa Colony Villas is located within the prestigious Waikoloa Beach Resort which offers spectacular views of Hualalai, Mauna Loa and Mauna Kea ...
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Luxurious ground floor "no stair steps" villa with private lanai, large lawn area and wide open views of the golf course. Granite slab counters in the large, well-equipped kitchen. Living and dining areas have hardwood floors. Kitchen, entry ...
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Hale 'O Malina is located in beautiful Waikoloa Village. The "Village" as it's affectionately referred to is located just up the road from world class golf courses, pristine beaches, stunning resorts, amazing restaurants and so much ...
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Come and enjoy all the amenities of a fully stocked three bedroom condominium at Hali'i Kai. Our beautiful condo (in Building 6) in Hali'i Kai (in the Waikoloa Beach Resort area) is the best value in the area. The bedrooms offer 1 King, ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Modern luxury right on the Waikoloa Village Golf Course! Ocean and sunset views from the lanai and almost every room!! Our Hawaiian home was completely renovated in 2017, with a brand new kitchen featuring custom maple cabinets, quartz ...
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
This vacation rental is professionally managed by ResorticaHawaii Waikoloa Beach Villas G1 Please scroll down for detailed information Bedding Configuration: Master Bedroom: King Bed Guest Bedroom Upstairs: One Queen ...
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
A LUXURIOUS VIEW CONDO CLOSE THE THE BEACH AT VISTA WAIKOLOA - THE ENTIRE CONDO HAS BEEN COMPLETELY RENOVATED AND UP TO DATE! THIS 2ND FLOOR CONDO IS IN ONE OF THE BEST LOCATIONS ON THE ISLAND! WALK 8 MINUTES TO A BEAUTIFUL WHITE SAND ...
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This villa offers: -Ground floor 2-bedroom, 2-bathroom villa with no steps -New AC installed in July 2022 -Free garage parking -Freshly renovated swimming pool as of August 2022 within few steps away -Beach ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Elegantly furnished Condo with Ocean Views from every room located on the Waikoloa Beach Golf Course! Walk to Waikoloa Hilton (restaurants, shops), Island Gourmet Market (grocery store), or to 'A' Bay beach. Truly a paradise location! ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Looking for just the right place to enjoy your Big Island Hawaiian Holiday? Treat yourself to a fresh approach. Your own private 1 bedroom 1 bath cottage. Newly renovated, this delightful guest cottage is tucked away behind gates at the end ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Awarded Hawaii's most hospitable host for the entire state of Hawaii, Coastal Cottage is nestled sweetly in the peaceful village of Waikoloa is the heart of Hawaii island Gd Coast on the Big Island of Hawaii, Ideal for visitors wanting close ...
4 bedroom
2 bathroom
Sleeps 10
Hawaii> Waikoloa Village
Bright and sunny second floor 2 bedroom, 2 bath condo. Amazing lanai views overlooking the 14th tee of the Waikoloa Village golf course with partial ocean views. A short walk to a grocery store, restaurants, and shopping. The condo is less than ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village