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, 30
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
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
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. All the windows have been replaced with new thermal aluminum windows that will keep the home quiet and cool. We are also upgraded to a ...
- 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
Hawaii Life Vacations presents Royal Mauian Unit 509. This light, bright and completely remodeled one bedroom condo offers a full ocean view from the living room, kitchen and bedroom. AC and ceiling fans throughout to keep you cool and fully ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
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 5
Hawaii> Kihei, Maui
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...
- 3 bedroom
- 2 bathroom
- Sleeps 6
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
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
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. I think everyone goes out on the lanai immediately. Unpacking can wait. You can enjoy the incredible view & ...
- 2 bedroom
- 2 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 6
Hawaii> Kihei, Maui
Hawaii Tax ID: TA-076-894-8224-01
TMK: 390050100065
MAGNIFICENT! 30 feet from the ocean with a 180 degree unobstructed panoramic ocean view. Deluxe Unit with the best location in the building. Beautiful and serene, with warm ocean ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Introductory Rate - We would love for you to be one of the first guests at this unit to help start up the great reviews! Royal Mauian #201 is a renovated 2Bd/2Ba unit on the 2nd floor of an amazing complex! The unit is equipped with split system...
- 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
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...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
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...
- 3 bedroom
- 2 bathroom
- Sleeps 7
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...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei, Maui
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
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...
- 3 bedroom
- 2 bathroom
- Sleeps 7
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
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
