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 '%Royal Mauian%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Royal Mauian #610 is on the top floor of the property with close elevator access for convenience. Enjoy sunsets everyday from your lanai that hosts unobstructed ocean views or wake up every morning to views of the Pacific Ocean from your ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
Amazing VIEWS of 3 Islands from this FULL oceanfront beach condo located in South Kihei. If you want the best...this is it! Royal Mauian Resort #316
Watch whales (in season) and turtles feeding on the snorkeling reef from the private...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Royal Mauian #410 is a 2 bedroom, 2 bath unit on the 4th floor with a panoramic ocean view and central A/C throughout. It has a fully-equipped kitchen, full size washer and dryer, 3 brand new flat-screen TV, DVD, telephone, and free WIFI. There ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
ALOHA!
Welcome to Kihei, Maui and your oceanfront paradise! Our incredibly beautiful condo is near the center of the building and only steps away from the water. Watch whales, savor the sunset and enjoy the sound of the waves from the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Fully remodeled and updated, our home is ideally suited for a romantic getaway, celebrating a special life milestone, those looking for a little laid-back downtime, and anyone wanting to enjoy Maui to its fullest potential. Our 1 bedroom 1 bath...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
This beautifully decorated 2 bedroom, 2 bath ground floor condominium home underwent a complete renovation. New thermal aluminum windows that will keep the home quiet and cool. Tri-zone air conditioning system with a thermostat in each room so ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Experience No. 604 at Royal Mauian, a comfy top-floor, air-conditioned beachfront sanctuary with breathtaking views. Nestled between two white sand beaches, enjoy the best of both worlds: dive into vibrant beach life or relax watching the ocean ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
You'll enjoy making memories in this well maintained, beautiful, family owned condo, Unit 515. The view is fabulous and the location is perfect. Simply walk downstairs and lounge on the grass next to the pool, or walk right next door to Kamaole ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Welcome to your perfect Maui getaway at Royal Mauian Unit 509! This beautifully designed, fully remodeled 1-bedroom, 1-bathroom oceanfront condo is located in the vibrant Kihei area, just steps from the stunning beaches and local attractions. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Enjoy amazing panoramic views of the ocean/outer islands from this 5th floor beachfront condominium that is breathtakingly peaceful and serene. This unit offers year-round sunsets and is fully furnished with a completely equipped kitchen, king ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
A Note From The Owner:
Everything about this condo says "vacation" from the custom kitchen to the oceanfront living-room-style lanai. Come with friends or bring your ohana for a get away with plenty of room for everyone and all the ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kihei, Maui
This perfect 2 Bdrm Condo, Unit# 304, is located right on the beautiful and sandy Kamaole I beach. The best beach on Maui! Enjoy eating all of your meals on the large lanai prepared in the fully equipped kitchen. Cal King size temperpedic bed ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
After a long trip to get to Maui, it is wonderful to walk into the condo and be wowed by the view. You are right on the ocean. On the 2nd floor, you are looking directly at the water. I think everyone heads to the lanai immediately. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Royal Mauian 309 is a newly renovated luxury condo for the guest who wants a total immersion in Maui's ultimate beach experience. Sitting less than 50 feet from the pristine Pacific and is flanked by two of Maui's postcard beaches – Kamaole I...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Royal Mauian #210 is a 2 bedroom, 2 bath unit on the 2nd floor with a panoramic ocean view and split system A/C throughout. It provides a fully-equipped kitchen, full size washer and dryer, 2 flat-screen TVs, and free WIFI. Go to sleep in your ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Royal Mauian stands as a distinctive six-story oceanfront property nestled in South Kihei, providing uninterrupted vistas of the crystal-clear Pacific waters. Situated alongside Kamaole Beach, this locale is a haven for breathtaking sunsets, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
Welcome to Royal Mauian #218, a beautifully remodeled condo offering exquisite details and modern amenities for your perfect Hawaiian getaway.
Key Features:
*Remodeled with Exquisite Detail: Enjoy a stylish and contemporary living...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
