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 '%Maui Kamaole%') AND sleeps>=1 order by rating desc LIMIT 0, 20
LOCATION: MAUI KAMAOLE, South Kihei (Kamaole III Beach Area), Maui, Hawaii, USA
Transient Accommodation Registration ID:
Fred Coombes TA-214-745-7024-01 GE-214-745-7024-01
Mary Coombes TA-043-043-6352-01 ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
This light and bright ground floor unit has beautiful ocean views. The recently updated decor has a mid-century modern vibe with vintage Hawaiian accents. One of the larger units, this condo has over 1000 square feet of living space, large ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Beautiful updated bedroom furniture and King bed in this Ground floor one-bedroom Condo. The kitchen opens up to the dining and living areas providing an open living space. The bedroom has a king bed and a flat-screen TV. The living room has a ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> KIHEI
Maui Kamaole #J-103 is a 1 bedroom, 2 bath unit on the ground floor. It has split system air conditioning in both the living room and bedroom, a fully equipped kitchen, washer and dryer, flat-screen TV, telephone service, California king bed, ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Maui Dream Place is our vacation home rental property in Maui, Hawaii. This two story condo has plenty of space for your family, plus hotel-style amenities which include access to two swimming pools, hot tubs and tennis courts. In our opinion, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Personal Touch: We stock everything you need: dish soap, laundry soap, paper towels, napkins, Kleenex, TP, garbage bags, coffee filters etc... All you need to bring is you! We provide the definition of a care free vacation, if this interests ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Condo was fully renovated, new cabinets, tile floor, granite countertops, stainless appliances and new A/Cs. The Master Bedroom boasts a California King bed, ensuite bathroom, and private courtyard. The modern kitchen is very well stocked and ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
This 1050+ square foot 2BR/2BA condo is in the highly desired Maui Kamaole complex, one of the newer condo complexes with some of the largest units in South Maui. Our home is spacious with 2 large bedrooms (plus a pull-out memory foam sleeper ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Welcome to our beautiful oceanview Maui Kamaole I-103 condo. This is a large 1040 sq ft 1-bed 2-bathroom ground-floor condo. The bedroom boasts a comfortable king-sized bed, the large livingroom a queen-sized Murphy bed (no pull-out sofa). The ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
With an incredible "Aloha" OCEAN FRONT view, G-212 is a light and bright elegantly upgraded condo across the street from Kamaole Beach Park III. Maui Kamaole's sweeping green lawn and lush tropical gardens rise up from South Kihei Road on ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Our ground floor unit is located in the Melia "M" Cluster, and is among the newest and most private buildings in the Maui Kamaole complex. We completed an extensive remodel of our condominium, including - new tile floors throughout; new tiled, ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Maui Kamaole, located in South Kihei near Wailea, is a tranquil, low-rise residential complex situated on 23 beautifully landscaped acres. There are two large swimming pools, tennis courts, Jacuzzi s, and best of all, it’s just a short 5 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Personal Touch:
We stock everything you need: dish soap, laundry soap, paper towels, napkins, Kleenex, TP, garbage bags, coffee filters etc... All you need to bring is you! We provide the definition of a care free vacation, if this ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
This ocean view, two-bedroom, two-bath condominium is located near the front of Maui Kamaole and is located up one flight of stairs on the second level. Inside you will notice it is designed in the style of a townhouse with the master bedroom on...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Whether you are just 1 person or a party of up to 6, this is an amazing, oceanview condo located in sunny Kihei on the south part of the island. It is a 2 bedroom, 2 bath, 2 story newly remodeled property in a family friendly, community called ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Partial ocean view remodeled unit located in the nicest complex in Kihei. This 2 bed, 2 bath unit was renovated in 2019. The complex boasts 2 pools and 2 hot tubs and is considered one of the quietest on the island. This unit is uniquely ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Beautiful VRBO Premier Partner Designation and 5 Star Property is in the B building, across from main pool and a 5 min walk across from Kihei's favorite beach. 2 bedrooms, 2 bath, over- sized unit with many high quality, unique upgrades rarely ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Welcome to your slice of paradise! Incredible ocean views, quiet, private and relaxing. The perfect place and location to call home during your stay in Maui.
Note: Travelers to Hawaii will no longer be required to quarantine for 10 days ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Garden Paradise at the beautiful Maui Kamaole
Don't get us wrong- the entire Maui Kamaole property is gorgeous in itself, but this condo is one to behold. It is located in a quiet corner of the property in one of the last buildings to be ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Maui Kamaole #J-119 is a beautiful ground floor, 1 bedroom, 2 bath ocean view unit in a great location. It features a full kitchen with a microwave convection oven combo in lieu of a conventional oven. It features air conditioning throughout, an...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
