select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Kolea%') AND sleeps>=1 order by rating desc LIMIT 0, 15
The perfect vacation rental awaits you! This 3 bedroom, 2 bath condo is centrally located in the beautiful oceanfront neighborhood of Kolea. It is one of those versatile homes that provides great space for one or two couples, or the ideal ...
3 bedroom
2 bathroom
Sleeps 6
HI> Waikoloa Beach Resort
Welcome to Kolea 10B ~
Location, View, Perfection... Here we have one of the best 2nd floor ocean views within the resort, looking over the Private Kolea Beach Club.
In the very heart of the Kolea Resort sits Kolea 10B. Perfectly placed to ...
3 bedroom
4 bathroom
Sleeps 6
HI> Waikoloa
Enjoy ample space for any occasion at this luxury 6 bedroom private home at Kolea at Waikoloa Beach Resort. Situated in an ideal location this home offers easy access to the beach, Kolea Beach Club, as well as amenities at the Waikoloa Beach ...
6 bedroom
7 bathroom
Sleeps 12
HI> Waikoloa
This finely landscaped beach front property will make your Hawaii trip unforgettable. Our complex is the premiere Big Island destination as the ONLY Waikoloa villas or condos with direct beach access; and as one of the closest villas to the ...
2 bedroom
2 bathroom
Sleeps 5
HI> Waikoloa
Kolea 14A. Stunning and Spacious Ground Floor Unit. Perfect for your vacation!
Please scroll down for detailed information
***PLEASE NOTE: Due to current Covid 19 protocols there may be modified hours for Pool Services, Fitness Rooms or...
3 bedroom
2 bathroom
Sleeps 6
HI> Waikoloa Village
3 bedroom, 3.5 bath
2nd floor level with semi private elevator
Partial ocean and mountain views
Master suite king bed with private lanai, ensuite with dual walk in shower, deep soaking tub, dual vanities and walk in closet
Guest master suite ...
3 bedroom
4 bathroom
Sleeps 6
HI> Waikoloa
Kolea Ocean Front Resort is truly a gem on the sun-drenched Kohala Coast of the Big Island in the Waikoloa Beach Resort. We fell in love with this spot and believe you will enjoy it too! Hale Mermaid is a LARGE 2 bedroom, 2 bath luxury ...
2 bedroom
2 bathroom
Sleeps 6
HI> Waikoloa Village
This listing is for two villas located in the best condo community in Waikoloa - Kolea! Our villas have over 150 5 star ratings and we have been doing this for over 10 years. We decided to create a new listing to allow guests to search for ...
5 bedroom
4 bathroom
Sleeps 15
HI> 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
HI> Waikoloa Village
Welcome to Kolea 6D ~
This 3 bedroom, 3 bathroom condo has a prime location just steps from the Kolea Beach Club. While it offers the convenience and ease of access to a large 3 bedroom home, it is extremely special as you wont need to ...
3 bedroom
3 bathroom
Sleeps 8
HI> Waikoloa
* 3 Bedroom, 3 Bath ground floor unit.
* All bedrooms have new bamboo hardwood flooring and private bathrooms.
* Lanai has a built in gas BBQ with comfortable outdoor furniture.
* Gourmet kitchen features Viking appliances and sub-zero ...
3 bedroom
3 bathroom
Sleeps 6
HI> Waikoloa
Aloha and Welcome to Kolea Landing
A short walk (less than 400 steps to be exact) and you will be relaxing at Lanikai Beach! Our 1,600 sf guest house is the perfect blend of island and modern living. Complete with two master bedrooms each with ...
2 bedroom
2 bathroom
Sleeps 4
HI> Kailua
Covid-19 Notice: Available amenities vary at each Complex, many have restricted use. Please inquire with KCV.
Feel your stress melt away when you enter this Hawaiian paradise located on the edge of the Pacific Ocean. Upon arrival, you enter a...
2 bedroom
2 bathroom
Sleeps 5
HI> Waikoloa
**SELF-QUARANTINE can only take place at a hotel/motel, required by the State of Hawaii. Regrettably, guests will not be able to stay at our condo.
**CLEANING DURING THE PANDEMIC: this property is unavailable at least 1 day between guests....
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa
You will begin to relax and unwind as soon as you enter Kolea 14H. Take in the calming views of the ocean from inside the villa and from the lanai as the sun sets over the Pacific. Use the Viking grill on the lanai to prepare your favorite meals...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa Village
