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 (city = 'Waikoloa Beach Resort' OR postalCode = 'Waikoloa Beach Resort' OR region like '%|Waikoloa Beach Resort|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Newly remodeled in 2018, this luxurious 3 bedroom villa is ideal for family and friends looking to explore the Hawaiian island. The Waikoloa Beach Resort has numerous amenities from golf course greenery, swimming pool, fitness room, BBQ ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
HI> Waikoloa Beach Resort
Surrounded by the luscious garden greenery at the Waikoloa Beach Resort, this villa is great for couples or family vacations with the kids! The Kolea Beach Club and the A-bay are a short walk away from this unit. The perfect Hawaiian vacation ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Waikoloa Beach Resort
Kolea 14F (1) is nestled along tranquil blue waters and soft sand beaches of Anaeho'omalu Bay (A-BAY). Ideal for families or for individuals who enjoy gracious, exclusive vacation residences, Kolea Villas offers luxurious accommodations for ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Waikoloa Beach Resort
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
Halii Kai 13A. Includes Waikoloa Gold Golf Membership Discounts
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 Community/BBQ Areas...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Waikoloa Beach Resort
Waikoloa Beach Villas I22.
Please scroll down for detailed information
PLEASE NOTE: The swimming pools and pool areas at the Waikoloa Beach Villas will be undergoing renovations beginning June 30, 2021. Guests will have access to a ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa Beach Resort
Explore Waikoloa Beach Villas O33, a Big Island penthouse condo furnished with designer touches throughout. The vacation rental is thoughtfully supplied with most of the items you will need for feeling right at home during your Hawaiian ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Waikoloa Beach Resort
This large upscale 2BR/2BA villa is at Hali'i Kai, the only OCEANFRONT condo community within the famous Waikoloa Beach Resort. Besides a great condo, you will get to enjoy Hali'i Kai's unique Ocean Club. Perched at the ocean's edge, it ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Waikoloa Beach Resort
Waikoloa Colony Villas 1705.
Please scroll down for detailed information
PLEASE NOTE: The Mauka Pool and Hot Tub will be closed for renovations from mid-April for approximately 6 weeks barring any unforseen issues/delays. The Makai ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Waikoloa Beach Resort
Waikoloa Colony Villas 1102.
Please scroll down for detailed information
PLEASE NOTE: The Mauka Pool and Hot Tub will be closed for renovations from mid-April for approximately 6 weeks barring any unforseen issues/delays. The Makai ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Waikoloa Beach Resort
Vista Waikoloa A304. Ocean Views!
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 Community/BBQ Areas ***
Bedroom ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Waikoloa Beach Resort
11C is a prime location to enjoy those seamless Hawaiian sunsets. Situated right by the ocean, you will be able to smell the salt air as the winds of Waikoloa breeze on by. The condo is footsteps away from the Ocean Club, where you can enjoy ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Waikoloa Beach Resort
Waikoloa Beach Villas J33
Please scroll down for detailed information
PLEASE NOTE: The swimming pools and pool areas at the Waikoloa Beach Villas will be undergoing renovations beginning June 30, 2021. Guests will have access to a swimming ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Waikoloa Beach Resort
Waikoloa Beach Villas I2.
Please scroll down for detailed information:
PLEASE NOTE: The swimming pools and pool areas at the Waikoloa Beach Villas will be undergoing renovations beginning June 30, 2021. Guests will have access to a swimming ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Waikoloa Beach Resort
Waikoloa Beach Villas O32
Please scroll down for detailed information
PLEASE NOTE: The swimming pools and pool areas at the Waikoloa Beach Villas will be undergoing renovations beginning June 30, 2021. Guests will have access to a swimming ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa Beach Resort
