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, 50
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
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
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
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
Welcome to Banyan Tree 200, an exquisite oceanfront retreat located in the heart of Kailua Kona. This stunning condo offers breathtaking views of the Pacific Ocean, providing the perfect backdrop for your Hawaiian getaway. With its prime ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Welcome to Banyan Tree 203, an exquisite Kona oceanfront vacation rental located in the heart of Kailua Kona, Hawaii. This stunning property offers breathtaking coastline views and a serene ambiance, perfect for a relaxing getaway. With 1,166 ...
- 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
Discover this terrific 1-bedroom gem in Mountain View. Nestled in nature, this soothing cabin offers a serene escape for guests. With 1 bathroom, this cozy retreat is perfect for those seeking a peaceful getaway. Experience the magic of Mountain...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Mountain View
