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 '%new listing%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Our condo is zoned "Hotel/Resort" so no need to worry about our zoning.
Hear the Waves....Lay in bed and listen to the waves! Spacious condo with direct beach & ocean view. A Sandy Swimmable beach is right outside your door.
*** ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Now with Air Conditioning! Yes we now have Daikin AC systems in each Bedroom, and Living room.
New Listing, other listing 5 Stars all the time. Spectacular Ocean Beach Front Maui Penthouses 6 Bedrooms 6 Baths.
Our MauiPenthouses are the ...
- 9 bedroom
- 6 bathroom
- Sleeps 18
Hawaii> Lahaina
Escape to paradise at Wailea Ekahi Village 4A, Maui's dream vacation rental. This spacious 2-bedroom, 2.5-bathroom townhouse offers the perfect blend of indoor comfort and outdoor living.
Unwind in Style:
Indulge in expansive ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Prime Location & Ocean Vistas: Escape to Maui's paradise at Wailea Ekahi Village unit 5F. This charming 2-bedroom, 2-bathroom condo boasts a prime location near the beachfront pool, pavilion, and Wailea Beach Walk. Relax on the second-floor ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Maui Kamaole G211, picturesque ocean views! This 2bed/2bath condo offers the perfect getaway for families or couples traveling together. Take in the relaxing views from your private lanai. Enjoy your morning coffee or dine al fresco with seating...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
vrKCV - Mauna Lani Fairways 1601, Professionally Managed by Kona Coast Vacations
Feel the Aloha Spirit and relax in tropical paradise at Sandy Feet Retreat! Newly renovated in the summer of 2022, enjoy the modern Hawaiian vibe in this ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
New Listing! Relax at this two-bedroom, two-and-a-half-bath Waikoloa Fairways unit in the heart of Waikoloa Village. The primary offers a soaking tub. Enjoy the open living layout, outdoor wet bar, and large lanai. Pool, hot tub, and BBQ at the ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Waikoloa
About our place
- Walk across the street to beautiful, uncrowded Kahana Beach
- See sea turtles and monk seals swimming in the ocean
- 8 minute drive to Ka'anapali and Kapalua golf courses
- Arcade machine with Classic 80’s ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Enjoy this ground floor unit featuring gorgeous views throughout the unit of Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. The unit is tastefully decorated with the spirit of Aloha and has a portable a/c unit ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Maalaea
Beautiful newly updated condo 2 bd/1 ba duplex unit, on a quiet access road between Princeville and Hanalei, avoiding the bustle of the main Princeville thoroughfare. Relax in the natural tropical landscape and take advantage of all the ...
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Princeville
Boasting stunning panoramic views of the Pacific Ocean that extend deep into the home's living, kitchen, and dining areas, Wailea Elua 2204 is a luxuriously renovated second-floor villa featuring some of the most spectacular vistas South Maui ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Beautiful remodel and ideal Kihei location create beachy perfection for your island getaway.
Relax on your own huge lanai. Feel the wonderful trade winds and take in the island scents from the beautiful Plumeria trees and Hawaiian ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
vrKVR - Halii Kai 10B, Professionally Managed by Kona Coast Vacations
Hali'i Kai 10B - Your Piece of Paradise
Haili'i Kai 10B is a premier ocean view property inviting you to start your vacation mornings with a cup of Kona coffee ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa
Be the first to enjoy this STUNNING VIEW! First time being offered as a rental for your vacation, this condo has been meticulously remodeled and fully refurnished. Everything is new! From the flooring and appliances to the push-back glass ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
vrKCV - Kulalani at Mauna Lani 205 - Professionally managed by Kona Coast Vacations
Secluded Kulalani Villa in the Mauna Lani Resort
Your perfect vacation in a tranquil tropical oasis! Kulalani at Mauna Lani is a private gated ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
Located just moments away from the pristine beaches, this elegant condo offers the perfect balance of tranquility and convenience. Explore the nearby attractions, engage in water sports, or simply lounge by the pool and soak up the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Escape to paradise and experience the wonders of Ma'alaea Harbor in this exceptional 2-bedroom condo. With its array of features and stunning ocean views, your vacation here promises to be truly unforgettable.
Step onto the grand ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
This property is hotel zoned, and not being affected by the recent proposed legislation.
Be one of the first guests to rent this very special ocean-front residence! Welcome to Wailea Beach Villa B202, an exquisite oceanfront retreat of ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kihei
NEW LISTING JULY 2023. See my other Properties # 1503692 and # 3197017 and #3156864 for more 5-STAR reviews and to book multiple units in the same building.
Located on the 10th floor of the Waikiki Sunset with beautiful mountain and city...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Wailea Elua Village #1603 boasts some of the best Ocean Views and is located on one of the most prestigious properties on Maui. This condo has been lovingly maintained and updated to ensure it remains a luxury-level choice for those traveling to...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Experience the beauty of Waikoloa Colony Villas, Unit 1206! This exceptional and newly renovated, 2-bedroom and 2-bathroom condo is a lavish and cozy home base for your Hawaiian getaway. Property amenities include a BBQ, fitness center, pool, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Are you ready to indulge in an unforgettable escape on the stunning west coast of Hawaii Island? The Shores at Waikoloa Beach Resort beckons you to experience the tranquility and beauty of the Big Island. Nestled between two pristine golf ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Waikoloa Village
Experience breathtaking ocean views from our bright and spacious condo. This ground-floor unit features 2 bedrooms and 2 baths, just steps from the ocean—perfect for unwinding after a day of island adventures. Located conveniently in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Two bedroom, two bath private residence at the top of Lanikai with spectacular, panoramic views of the Kailua coastline. Feel on top of the world in this truly unique hilltop bungalow. Nestled just beneath the Lanikai Pillboxes, a popular hiking...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua
Modern and sophisticated with a splash of Aloha, #29 is an ideal spot for honeymooning couples and those returning for their tropical anniversary celebration. Tucked in the lush foliage at the back of Grand Champions, this condo has the unique ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
*Professional photos coming mid-December*
Beautifully remodeled home at one of Maui’s favorite properties! G302 is one of a handful of condos on property with a fantastic ocean and sunset view as well as views of Haleakala all from your own...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Soak in this Third floor unit featuring gorgeous views throughout the unit of Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. The unit is a new listing and tastefully decorated with central AC throughout to keep...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Maalaea
Escape to paradise in this newly updated, air conditioned, 2-bedroom vacation haven nestled within the prestigious Regency at Poipu Kai. This tropical retreat promises a perfect blend of modern luxury and Hawaiian charm, providing an ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Koloa
Welcome to Waikomo Stream Villas unit 100, your tropical gem in the heart of Poipu! Nestled in lush greenery and surrounded by vibrant flora, our one-bedroom condo is the perfect escape for those seeking tranquility and natural Hawaiian ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
FREE daily activities included with your stay value up to $570! Complimentary admission, every day of your stay*: Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day Electric Bike Rental. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Welcome to our tranquil retreat in Princeville, Hawaii! A well established vacation rental that just changed ownership! Nestled amidst the lush greenery of the island, this spacious 3-bedroom house offers an idyllic escape for your vacation. ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Princeville
The Location:
Perfectly positioned to accommodate everyone's interests, the Coconut Coast is flanked by epic coastline threaded together by the Ke Ala Hele Makale coastal bike and walking path. Just a 7-minute walk along the path or the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lihue
Welcome to your dream Maui getaway! This highly updated 2-bedroom, 2-bath condo in Maalaea offers the perfect blend of modern comfort and breathtaking oceanfront views making it an ideal haven for families or friends seeking an unforgettable ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Welcome to the Sunrise House, your ocean front split-level oasis in Aliomanu, Kauai!
Escape to paradise and experience the ultimate retreat at our tranquil two-bedroom vacation rental. Nestled along the breathtaking shores, this peaceful ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Anahola
Welcome to Hale Cassia by Gather, a serene retreat nestled in the heart of Princeville, HI. This three-bedroom, two-bathroom home is an ideal sanctuary for six guests seeking a quintessential Kauai experience. From the moment you arrive, the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Forget your worries and experience the best of the Big Island in this elegant, light-filled, quiet unit at the Mauna Lani Golf Villas. Wake up to the spectacular views of the sunrise above the sacred mountains of Mauna Kea and extraordinary ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Puako
NEW LISTING: BOOK WITHIN 60 DAYS OF ARRIVAL DATE AND RECEIVE A 10% DISCOUNT. Please message manager Barb for details.
Welcome to Kealohilani, our heavenly home by the sea and your ground floor tropical escape. Nestled a few steps from...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Your Ideal Vacation Stay Here! The view is awesome from this newly remodeled, spacious studio condo on the 27th Floor of the Hawaiian Monarch Hotel, just a few blocks from Waikiki Beach! This clean, modern, luxurious space is in a great location...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
FREE daily activities included with your stay value up to $570! Complimentary admission, every day of your stay*: Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day Electric Bike Rental. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Nestled at the end of a cul-de-sac on a quiet street in the sunny village of Poipu, Hale PIKO is a private tropical retreat just one block away from two of the world’s most beautiful beaches: Poipu Beach and Brennecke's Beach. Spend your days ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Book 5 nights and enjoy the 6th night free! Valid for new reservations from 10/10/24 for stays between 10/10/2024 - 12/8/2024!
Welcome to your dream vacation home at Pacific Palm Villas, Hali'i Kai, located in the heart of the exquisite ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
A beautiful, unobstructed view of the deep blue Pacific Ocean and coral reef from every room! Our condo is located on an ocean bluff having the luxury of a 180 degree view of blue water from every room. Along with magical waves, you will see ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Discover Wailea Ekahi 39E, our exquisite 1-bedroom, 2-bath condo with stunning ocean views. Whether you're seeking a romantic getaway, a solo escape, or a tranquil spot to work remotely, this condo provides the ultimate coastal ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
This 2 bedroom, 2 bathroom condo located on the ground floor, offers majestic ocean views and is fronting a beautiful golden sandy beach. Fully finished and equipped with everything you need in your home away from home, this is the perfect spot ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Maalaea
The Maalaea Banyans AOAO that the building elevator will be replaced starting on or around January 27th, 2025. This project is expected to be completed at the end of April. The elevator will not be available at all during this ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Wailuku
Beautifully remodeled house, located directly across the street from Kahalu'u Beach Park! Enjoy the soothing sounds of the waves, world class snorkeling, memorable surfing, and the amazing ocean views; just steps away from your spacious back ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
FREE daily activities included with your stay value up to $570! Complimentary admission, every day of your stay*: Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day Electric Bike Rental. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
FREE daily activities included with your stay value up to $1035! Complimentary admission, every day of your stay*: Lu’au, Golf, Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
FREE daily activities included with your stay value up to $570! Complimentary admission, every day of your stay*: Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day Electric Bike Rental. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Bldg. 16 - Unit #153 - A front-row, ground floor, impeccable 3 Bedroom + 2 Bathroom paradise condominium at the Wailea Grand Champion Villas. This is a prime example of what Wailea Grand Champion Golf Villas has to offer in a 3 bdrm + 2 bath ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
