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 '%Koa Resort%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Welcome to Koa Resort 5i, located across the street from the beautiful beaches of South Maui. With plenty of room for your entire family, Koa Resort 5i is a great spot to kick back and experience the Maui lifestyle. This is a 3-bedroom ...
- 4 bedroom
- 2 bathroom
- Sleeps 9
Hawaii> Kihei
Summary:
Discover our spacious corner townhouse villa at Koa Resort, opposite Keaka, Kalepolepo & Waipuilani Beach Parks. Adjacent to the Humpback Whale National Marine Sanctuary Visitor Center, it's a nature lover's paradise. With room for 8...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kihei
WELCOME TO KOA RESORT UNIT 1F
YOU PAY CLEANERS AT TIME OF ARRIVAL $500 (SAVING YOU ABOUT $90)IN TAXES
ONE OF A KIND, no other units like this townhome, decorated in colorful island style decor, is located only one block from ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Kihei
Aloha and welcome to our Maui vacation home.
The first floor location is convenient and offers a private garden setting from the lanai. The two bedroom one bath condo has been updated with all new appliances and furnishings. The master ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
As owners who manage our own property, our commitment, to our guests, is to have a clean and comfortable experience.
Our Ground floor unit is easy to access from the parking lot and all the amenities at Koa Resort. This ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
In this beautiful 1100SF 2nd floor 2BD/2BA condo all the amenities of a luxury home will be at your disposal. Extensively remodeled in 2015 with name brand appliances and new furniture this property features gorgeous native Hawaiian Koa wood ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Welcome to your Maui home away from home, located in the desirable Koa Resort complex with just a short walk to the gorgeous beaches of South Maui. Unit 4B is a spacious ground floor fully furnished two bedroom one bathroom vacation rental ...
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kihei
A rare low-density complex (only 54 units) on 5 acres of lush well manicured grounds with the largest private heated pool on Maui and the ocean that awaits you across the street! Fully air conditioned! The complex boasts private tennis courts,...
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kihei
Koa Resort Unit 4i is a fantastic place for a relaxing and enjoyable vacation in a tropical paradise. This second-floor condo has stairs leading up to this renovated unit and features 3 bedrooms and 2 bathrooms, making it perfect for families or...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Gorgeous remodeled townhome, across from ocean - Remodel includes all-tile and granite in kitchen and baths!
Steps from Pool, BBQ at Cabana, A/C througout, FREE WIFI
This Koa Resort townhome is a completely remodeled 3 bedroom 2 ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
A beautiful tropical garden setting awaits you from this first floor location. The one bedroom one bath apartment is upgraded and updated . New Air Conditioning throughout the apartment! It has a spacious living/dining/kitchen area. The ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Experience the allure of Kihei at Koa Resort 5H, where luxury meets leisure in the heart of this iconic Hawaiian destination. Situated amidst six miles of pristine beaches and breathtaking views, Kihei offers endless opportunities for adventure,...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Summary:
Aloha and welcome to our 3 bedroom condo, your home away from home! My family and I live on Maui and we are so excited to host your vacation. Don't get stuck with a major rental company that doesn't care about your individual group. ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
This 4-bedroom, 3-bath ground-floor end unit is tropically decorated "Island Style" and is available for your Maui vacation! This spacious condo is conveniently located just 40 feet from the entrance to the swimming pool and hot tub. Koa Resort ...
- 5 bedroom
- 3 bathroom
- Sleeps 12
Hawaii> Kihei
Gorgeous townhome, across from ocean -
Steps from Pool, BBQ at Cabana, A/C, Air-Conditioning located in all three bedrooms and living room, FREE WIFI
This beautiful 3-bedroom, 2-bath townhome will be your home-away-from-home during...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
A rare find! Three bedrooms and three baths and two levels make for plenty of space in this apartment. The first floor has a master bedroom-bath suite with a king bed; the second bedroom has two twin beds, and there is a kitchen and dining ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kihei
Koa Resort 4G | Maui Beachfront Rentals | Beautiful 3-Bedroom Conveniently Located.
Private 2nd floor unit and loft at the Koa Resort - directly across the street from Kalepolepo Beach Park in Kihei. Main floor includes a spacious ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
**REMODELED MAY 2023!!**
This large remodeled 2 bedroom, 2 bath townhome is beautifully decorated in Hawaiian style decor. There is a King bed in the downstairs bedroom and Queen in the upstairs loft. There are two full bathrooms; one ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
