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
This one bedroom Maui Kamaole condo is right on the front row and enjoys unobstructed ocean views, seasonal whale watching, an easy walk across the street to Kamaole Beach Park III and it is in close proximity to the charms and offerings of ...
- 1 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 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 your peaceful oasis in paradise at Maui Kamaole I-103! Nestled in the lush tropical surroundings of the renowned 23-acre Maui Kamaole resort, this charming condominium offers a serene retreat just moments away from the stunning ...
- 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
This one bedroom Maui Kamaole condo is right on the front row and enjoys unobstructed ocean views, seasonal whale watching, an easy walk across the street to Kamaole Beach Park III and it is in close proximity to the charms and offerings of ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
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
