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 (`headline` like '%Banyan Tree%') AND sleeps>=1 order by rating desc LIMIT 0, 20
WOW! That's what you'll say when walking into Banyan Tree, Unit#301, a third floor fully remodeled condo in a secure and private building with an elevator, pool, spa and barbecue!
You'll find floor to ceiling glass showcasing the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Hale Laule'a Banyan Bed and Breakfast Suite - Not a Typical VRBO
•Multi-Million Dollar Gated Oceanfront Estate RETREAT- pool, spa
four gardens, and Horses
•Views of the ocean and sunrise from the Bed in the bedroom, Kitchen, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hakalau
Welcome to your slice of paradise in Kailua-Kona at Banyan Tree, Unit 300! This stunning 2-bedroom, 2-bathroom retreat on famous Banyans Beach offers the ultimate blend of relaxation and adventure. So close to the ocean it feels as though you ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Ocean front doesn't get any closer! Enjoy the beach and the views' in this fully remodeled gem. Open the windows and let the ocean sounds take over! Brand new kitchen, custom made furniture, AC in all rooms, and beach access makes this vacation ...
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
OCEAN FRONT JEWEL!
You will think you're in heaven when you see the dazzling view from this 2 bedroom, 2 bath 3rd floor condo. Enjoy spectacular Kona sunsets, whales in winter, dolphins, and surfers just below at a famous surf break. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
Overlooking the beautiful Pacific Ocean and situated on the 3rd floor, this 2 bedroom/2 bathroom oceanfront condo has to-die-for views along with tropical comfort ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua Kona
Complete oceanfront penthouse with two bedrooms and two condos at the top floor.
Wondrous views of the ocean, reefs, moon and sunset plus all the facilities that your family and you enjoy..
Surfers and long waves have entertained ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
9/24/24 - Banyan Tree 200 is currently available for the Kona Ironman World Championship Kona. This fabulous oceanfront condo is located just south of the Kailua Pier, where the Kona Ironman starts and finishes. If you want to stay close to the...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
9/24/24 - Banyan Tree 203 is currently available for the Kona Ironman World Championship Kona. This fabulous oceanfront condo is located just south of the Kailua Pier, where the Kona Ironman starts and finishes. If you want to stay close to the...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Knutson & Associates is very proud to represent Banyan Tree 404, a top floor condo in a secure and private building with an elevator, pool, spa and barbecue!
Full length views of the ocean from both the living room and bedroom of this ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Relax at the pool or on the beach, hit the trails, or enjoy tons of daily activities and entertainment right on site–the choice is yours at this oceanfront property in Lahaina! Spend the day lounging on beach chairs or enjoying a private beach...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
