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, 20
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
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome back to the Aloha State.
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!
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Aloha and welcome to Leilani in the Beach Villas in the Waikoloa Beach Resort. A flawless vacations start at here, from it's lush palm-shaded grounds and lava rock swimming pools, to it's gourmet kitchen and private lanais scented by tropical ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
vrKCV - Hali'i Kai 10E, Professionally Managed by Kona Coast Vacations
3 bedroom
2 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 and welcome to the idyllic gated community of Hali'i Kai! We're thrilled to introduce you to our exquisite, 180-degree oceanfront condo, where luxury meets affordability, and offers all the indulges, beauty, and relaxation that the Big ...
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
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Our slice of Hawaiian Paradise! Traveller cancellation is your opportunity to get dates in May/June at a 20% discount!!
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
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Welcome to Paradise! Enjoy Hawaii's Big Island from your private condo (with an ocean view)!
2 bedroom
2 bathroom
Sleeps 4
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
3 bedroom
3 bathroom
Sleeps 8
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
