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 '%condo%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Punahele Penthouse Condo will fast become your favorite place to stay in Kailua-Kona! Punahele Penthouse is on the 7th (top) floor of the Kona Ali'i Condo Complex which is located in the heart of Kailua-Kona. You're within walking distance to ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Paradise awaits you in this beautiful ground floor, one-bedroom, two-full-bath Lae Nani Condo, Unit #317 on the beautiful Garden Isle of Kauai!
Located on Kauai’s beautiful Coconut Coast (East side), this Condo has all the amenities you...
- 1 bedroom
- 4 bathroom
- Sleeps 4
Hawaii> Kapaa
Dream your most desired vacation. Then come to magical Maui, one of the world’s most elite vacation destinations. How better to experience our island than by renting from an owner who has combined her own personal getaway with the conveniences...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Maalaea
Kauhale Makai (Village by the Sea) truly embodies paradise. This newly remodeled 2-bedroom, 2-bathroom condo has been beautifully updated, boasting a brand-new kitchen complete with granite countertops and high-end appliances.
The ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Make your trip to Maui more memorable with a stay at our 5 star condo at Luana Kai. Luana Kai is a beautiful resort with immaculate grounds in a park like setting, ocean front in sunny Kihei with no busy roads to cross to get to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Enjoy breathtaking sunset views every night from this beautifully updated condo steps from Downtown Kailua-Kona village with AC in both the living room and bedroom.
Sunset in Paradise is your private paradise with gorgeous ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Welcome to our beautifully decorated ocean front condo in paradise - Kona Reef A-22.
Our condo is our home away from home, so we respectfully request a maximum of 2 adult guests and no children under 18.
Whether this is your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Our unit is spacious with high ceilings, ceiling fans, fabulous sunlight, and gorgeous ocean views. This elevated third story unit affords a wonderful lanai with a view overlooking manicured golf courses, tropical flowers, natural lava ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
This is a an amazing 1 bedroom 1 Bath ground floor unit with plenty of space.It has been fully renovated with beautiful furnishings inside and out. It has lots of windows that lead out to a spacious patio. It has central air if the cool Hawaiian...
- 1 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> waikoloa village
Paradise inside and out! The elegant B314 is an elevator ride up to the third floor of the Maui Sunset, where you'll enjoy breathtaking ocean views right outside your window. The warm Hawaiian shore nestles directly against the Maui Sunset ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Aloha and welcome to our condo at White Sands Village. Brand new to the rental market, this wonderfully appointed 2-bedroom, 2-bathroom top floor condo offers everything you’ll need to enjoy your stay in Kailua-Kona and the Big Island Of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Visit our remodeled ocean front get-away. The unit has been remodeled from top to bottom, from ceiling to floor. The master bedroom features a California King bed, night stands with reading lights, built-in cabinets for clothing storage, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Was recently completely remodeled.
Our oceanfront condo is 23ft. from the ocean and is located on the 2nd floor and offers spectacular sunsets.
Watch the surfers and the dolphins from your lanai.
A Short walk to town and over 20 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
MANA KAI 503 - MOST LUXURIOUS CONDO IN THE MANA KAI MAUI RESORT.
Please review and sign the rental agreement and send it back as an attachment to continue and complete the booking. Additionally, call to discuss children's ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Royal Kahana 907 is located on the northwest side of the building which is the quiet side of the building that offers both sun and shade onto the lanai. With the opposite side of the building there is no escaping the sun or the sound coming up ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
UPGRADED UNIT***OCEANVIEWS FROM THE LIVING AREA AND MASTER BEDROOM***AIR CONDITIONED***GOURMET KITCHEN***EQUIPPED WITH BABY GEAR!!
ABOUT THE CONDO...
This AIR CONDITIONED 2 bedroom, 2 full bathroom unit is located on the second floor ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Are you looking for the absolute perfect condo for your once in a lifetime vacation to Kauai? Do you want breath taking views from almost every room? Do you want to be within walking distance to multiple beaches? How about be close to shops and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Aloha! Come and experience your Maui dream vacation!
This newly renovated Kahana Manor condo located between Napili – Honokowai and Kaanapali, in one of the most popular areas in West Maui often referred to as Kapalua Bay. Less than ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
This upscale resort offers incredible panoramic ocean views. Set on a point with coastline vistas, including Bali Hai, Pu'u Po'a features commanding 180 degree plus views of the Pacific Ocean. The spacious lanais are perfect for relaxing, whale ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Welcome to our home, Sugar Beach 311. This is a non-smoking, professionally cleaned, recently renovated and updated one bedroom condo. It is an immaculately maintained and spacious one bedroom, third floor corner unit, with front and side views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Maui Kaanapali Villas is located on 11 acres of beautifully landscaped tropical gardens. The beach in front of the property is rated number one on Maui. There are many activities just steps away including snorkeling, swimming and scuba. Check ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Welcome to the oceanfront paradise on Kauai at Waipouli Beach Resort Unit F-204!
This unit is a 2 Master Suite bedroom with 3 full bathroom floorplan. Each Master Suite comes with a smart flat screen television, balcony, dresser and ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Our condo, now with Air conditioning! Our condo is in north Lahaina, far away from the fire affected areas. We are considered to be located Napili Honokowai. Our unit E-8, is one of 72 units in an oceanfront resort. This unit is a townhouse ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Papakea is an unique property. Instead of a high rise it is a set of buildings only 4 levels high. There are three sets of buildings that front the ocean and we are located in the middle section--the perfect location for reaching either of the...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kahana Sunset is a beachfront resort on the world famous west coast of Maui, 10 minutes south of Kapalua and north of Kaanapali. You will be swimming and snorkeling with turtles and tropical fish off of the white sand beach of protected Keoni ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Enjoy stunning ocean views from all the bedrooms and living area of the condo. Greet every morning on your bed enjoying the views and sounds from the ocean!
*** OCEANFRONT overlooking greenbelt, quiet and private, no noise from pool or ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
This nicely remodeled 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Experience breathtaking ocean vistas on your next vacation with our deluxe Cliffs Resort Condo.
Furnishings and amenities include:
- Two queen beds in loft
- Portable Honeywell air conditioner in master bedroom
- Two ceiling ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Princeville
Kaanapali Royal N102 is situated on top of the hill by the 16th tee at the Royal Golf Course in Kaanapali. The location of our ground floor condo offers the best views across the resort, and at the same time is very private, quiet, and peaceful....
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Located on a picturesque lava bluff, our top floor condo offers privacy, cooling trade winds and panoramic ocean vistas which a camera can't begin to capture.
Crashing waves, migrating whales and sunrise/sunset views are what you'll see as...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Napili Point, Unit A-2 is located on Maui's western shore. This fully-equipped one-bedroom ground-floor condo (sleeps four) offers beautiful views of the Pacific Ocean from the lanai and living room, with the islands of Molokai and Lanai in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Napili
OCEAN VIEW
BOOK WITH US TODAY!
BEST REMODEL in MAUI VISTA
Completely gutted with NEW EVERYTHING!!!
Tigerwood Hardwood Floors, Italian Tile, Travertine, Fresh Paint, Ceiling Fans, All New Furniture, New Mattress...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Kolea 8C is a three bedroom, three and a half bath penthouse villa with an exquisite ocean and mountain view. It is perfectly situated overlooking the Kolea Beach Club, Anaeho’omalu Bay and Hualalai. The African Mahogany trimmed glass panels ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Waikoloa Village
This luxury condo is as close to the beautiful blue waters of the Pacific Ocean as you can get at Kanaloa at Kona. Unit 1804 is a top-floor (second floor) oceanfront condo with over 1700 square feet of living space with all the amenities of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Perched on a bluff above the beautiful Pacific Ocean, Cliffs 5-301 is on the third floor (no elevator) and offers wonderful views of the ocean and mountains as well as the lush foliage for which the north shore is famous. Enjoy these spectacular...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
A top floor luxury condo, with one king bedroom PLUS LOFT. The majestic 180 degree ocean to mountain views overlook Hanalei Bay, Bali Hai, and the surrounding mountains that hug the coastline. This privately owned condo has vaulted ceilings ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Princeville
Fantastic views of Hanalei Bay, Bali Hai, and the surrounding mountains and ocean help make this top floor, 2 bedroom plus loft condo perfect for enjoying the beauty of Kauai. The 2 bedrooms each have King beds and the loft provides 2 Queen beds...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Princeville
One of the best locations in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over 300 yards to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. The plantation style ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Are you craving PANORAMIC views from your condo & private balcony? Condo B9 boasts unobstructed 180° up-close-&-personal views of white sand beaches, Pacific Ocean, West Maui Mountains & North Kihei's resplendent sunrises AND ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Foster Tower is located steps away from beautiful Waikiki Beach. This is a one-bedroom unit in a highly desirable building and location. Enjoy beautiful ocean views and immediate access to the beach for surfing, swimming, and snorkeling, ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Hawaii STVR Registration Number: STVR-19-355146 ****************************** OUR CONDO HAS BEEN COMPLETELY RENOVATED !! NEW DUAL AIR CONDITIONING SYSTEMS - - KEEPS CONDO COOLER THAN MOST OTHER UNITS IN RESORT
PREMIUM OCEAN VIEWS ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
* 2 Bedroom, 2 Bath, third floor unit with loft.
* Beautifully furnished with tropical decor. TV in living room.
* Great views of the Kings' Lake & golf course.
* Master bedroom has a king bed and TV.
* Master bathroom fully ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Discover your perfect Maui retreat at Konea 642, a beautifully remodeled two-bedroom, two-bathroom condo on the sixth floor of Honua Kai Resort. This luxurious unit offers complete privacy, stunning ocean views, and all the upgrades to make your...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kaanapali
Aloha and welcome to our stunning 2-Story Retreat: Upgraded townhome with Breathtaking Mauna Kea Mountain Views, Steps to Coconut Grove, Lanai Dining, and a Private Primary Bedroom with its Own Lanai! Enjoy Hali’i Kai Resorts' saltwater pool...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waikoloa
Mauna Lani Terrace K202 is a beautiful 1 bedroom, 2 bath upgraded condo only a short walk away from the beach! Located on the second floor (stairs, no elevator), the condo has great views of the Mauna Lani fishponds and gardens. The living room...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kamuela
J104 is a gorgeous renovated 2-bedroom, 2-bathroom condo with easy access to the Mauna Lani's private Beach Club. Located on the first floor and only steps away from the ocean and ancient Hawaiian fishponds, the condo's covered lanai opens to a ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Kamuela
Enjoy Panoramic Ocean Views from your private lanais. Sunset views 365 days a year. Your only decision, whether to use the upstairs lanai with chaise lounge and cosy sitting area perfect for morning coffee or wine and cheese at sunset, or the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Ma'alaea Surf, Unit E4, is conveniently located on the ground floor, just steps away from the large pool. It was completely remodeled recently. This unit is well furnished, features a full kitchen and a second bedroom and bath. This condo ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
J202 is an amazingly well-located 2-bedroom, 2.5 bathroom condo only steps away from the ocean. With a great location on the property, families will enjoy being close to the Mauna Lani's sandy beaches, as well as a private pool/BBQ area. The ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
