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 '%2 bedroom%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Stylish Oceanfront 2nd Floor 2 Bedroom | 2 Bathroom
Lanikai is centrally located on the Island yet 'tucked away'. This elegant second floor unit has sweeping Ocean views. This luxurious condo comes with a fully-equipped kitchen area ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Come enjoy the beauty of Hawaii in our perfectly located condo at Waikoloa Beach Villas. Just 20 minutes north of the Kona airport you will find yourself in paradise!
Our property is perfect for 2 couples or a family of 3. Decorated to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa Village
vrKCV - Country Club Villas 315, Professionally Managed by Kona Coast Vacations
*CONSTRUCTION NOTICE - Complex began stair railing steel painting project for the month of August, 2024. This consists of sanding away old paint, which can be...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Gather during the day, and then retreat at night in this higher floor air-conditioned 1,036sf condo. It's all about convenience with the full kitchen for dining ease, furnished lanai, flat screen TVs, WiFi, washer/dryer, ceiling fans and DVD ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KIHEI
Our luxury 1400+ sq ft condo (F102 at the Mauna Lani Terrace) is walking distance from the world class (Auberge) Mauna Lani Resort, with nearby restaurants, Stores tennis, and golf facilities. It’s a short walk either to Mauna Lani Hotel or ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela,
Stylish island décor creates a casual vibe in this 1,082sf suite with lanai flowing onto the lawn. Homey ease plays out with a modern kitchen opening to the dining/living area, washer/dryer, flat screens, WiFi, double-vanity master bath, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> WAIKOLOA
Book with confidence, this nicely updated 2 bedroom, 2 bathroom (1086 sq. ft) single level condo. This condo is managed by PMI Maui, a local Real Estate Brokerage that specializes in vacation rentals. We are located within a mile of the resort...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Two bedroom two bath ocean view villa at Marriott's Ko Olina Beach Club in Oahu, Hawaii. Villa has a private lagoon and is located next to the Ritz Carlton and Disney Resort. Living area is approximately 1200 sq ft and includes flat screen ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kapolei
Surround yourself in luxury while enjoying privacy and seclusion all while being close to world class dining and shopping. Enjoy unparalleled views and seamless indoor/outdoor living with a top notch beach steps away and some of the best ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to "Paradise Found" at Hale Awapuhi, a luxurious 2-bedroom, 2-bath oceanfront condo on Kauai's stunning east shore. Nestled within a private, gated community, this exclusive retreat offers breathtaking ocean views and the ultimate island...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
TMK: 4-2-8-018-005-0001-000
This custom designed Hawaiian plantation style cottage is located just off the Brennecke's beach side of Poipu Beach Park, 100 steps to the beach!
This spacious house was designed and built in 2013 to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Penthouse Luxury Oceanfront, Third Floor, 2 Bedroom | 2 Bathroom with Central Air
Fall asleep to the sounds of enchanting surf and awake to tropical sunrises in this spectacular and fully remodeled 2 bedroom condo overlooking Wailua Bay ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Welcome to paradise when you open the door to our gorgeous plantation home. See video of unit on YouTube - https://www.youtube.com/watch?v=bHAJQ9oYFBI
This is an end unit with no one above or below and has panoramic ocean views in a lush ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kaanapali
Welcome to our 2 bedroom Waikiki Shore Condo. This exclusive Waikiki Shore Condominium is located directly on Waikiki Beach, and is the only privately owned condominium building on Waikiki Beach, and sharing this world famous beach frontage ...
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Hana's Heaven - a charming 2 bedroom cottage complete with full kitchen, living room and 3 lanais. Wake every day to the sunrise and ocean expanse. Off the beaten track, this home away from home is quiet and serene, the perfect place to relax ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Hana
Aloha! We would like to welcome you to our little piece of paradise on the Big Island of Hawaii on the South Kohala/Kona Coast. A 20 minute drive north from the Kona airport, the South Kohala area is upscale and close to some of the islands ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa
TWO-BEDROOM VILLA with Resort View on Maui's North Kā anapali Beach
1,260 SQUARE FEET • SLEEPS UP TO 6 ADULTS (2 King beds, 1 pull out sofa bed)
Entertainment console in living area with HDTV, DVD player and Bose® Wave® AM/FM ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Newly renovated Fully equipped Kitchen – new refrigerator, range, dishwasher and microwave. Many amenities.
Hawaiian style outdoor living and dining room in 700 sq. ft. Lanai with 4 ceiling fans to assist the ocean breezes.
• 2 ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
We are literally oceanfront!
E Komo Mai (Welcome) to Paradise!
30 Day minimum stay required by Oahu Ordinance
Our homes are fully furnished with everything you need for an ideal Hawaiian vacation!
Makapu'u Hale is 2 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waimanalo
Newly renovated Two Bedroom Two Bath Condo with Full Ocean View in Center of Complex on 2nd Floor on the Kona Coast. Corner Unit
Wifi equipped
Ceiling Fans
Portable Air Conditioner in living room
Enjoy Dophins spinning and whale ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Sun, fun and the Hawaiian Island lifestyle is waiting for you! Our townhouse is located in the beautiful beach front property of Ekahi Village. Explore the winding pathways through Ekahi's lush gardens spanning over 34 acres. Take a stroll ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Kihei
Kings’ Land, the newest Hilton Grand Vacations Club time share resort on the famed Kohala Coast, offers expansive fairway views and stunning Waikoloa panoramas. Carved out of an ancient lava field, this 112-acre oasis occupies a superb ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Two bedroom, two bath, balcony. Great pool deck.
Size of the unit: 1,017sqft. Size of Balcony; 105sqft
All electric kitchen. New appliances.
Fully furnished. two king size beds. Nice size living room and dining area.
Washer and dryer...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Legal transient vacation rental Permit 2211-CCH-0190
Amazing location! Right in the heart of Waikiki Our beautiful beach condo is located 2 minute walking distance to the waikiki beach & one block away from the Honolulu Zoo.
This ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Honolulu
Evan Price Binnacle Realty, Inc.
We will be happy to respond to your needs as concerns dates and other resort options via return e-mail. Please use the "Send email" for any questions that you may have. This is a ...
- 3 bedroom
- 2 bathroom
- Sleeps 9
Hawaii> Lahaina
FABULOUS LOCATION - Just 50 steps to the pool, the hot tub and the BBQ’s and a few more steps to the ocean-front lounge chairs. Grab a cool drink and head out to watch the sun set between Molokai and Lanai. Bring your binoculars for excellent ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
The Wailea resort is located at 155 Wailea Ike Drive and is considered by many to be the most desirable spot on Maui.
We recently completed a kitchen renovation, new flooring through our and a remodeled bathroom featuring a walk-in ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Local Owners! Renovated and upgraded! Steps from Kamaole Beaches! The perfect spot to relax and enjoy all that Maui has to offer.
Everything you need to make your stay comfortable and convenient is here.
•Split Air Conditioning ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Westin Ka'anapali Ocean Resort Villas - Oceanview 2 Bedroom Unit Sleeps 8
***PLEASE MESSAGE HOST WITH ANY QUESTIONS YOU HAVE BEFORE 'INSTANT BOOKING' AND WE WILL PROMPTLY REPLY***
The Listing photos are stock photos that the resort...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
ALOHA and welcome to our home at Luana Kai. Our unit is set apart from any other by the love we've put into it and the care we've taken to maintain it.
Our deluxe condo has tile floors throughout and stainless steel appliances for ...
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
As you wake up in the morning, take in the beautiful view with the cool ocean breeze. Sit on the balcony with a refreshing drink as you appreciate the magical Maui sunsets. Vaulted ceilings. Granite countertops. Spacious bathrooms. Open concept....
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Hyatt Residence Club Ka'anapali 2 Bedroom Unit Sleeps 6
***PLEASE DO NOT PUT IN A RESERVATION REQUEST USING THE “REQUEST TO BOOK” FEATURE***
Book our Amazing Luxury Villa & let The Timeshare Brokers, LLC make the booking ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Reservations are available in weekly (7 night) increments with arrivals and departures on Fridays, Saturdays, and Sundays only. The Westin Ka'anapali Ocean Resort Villas is a gorgeous beachfront resort with luxury villas. Enjoy this villa and ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
We just had a cancellation for Jan 4th to Jan 9th 2025. This would be a perfect stay combined with 3 or 4 days on another island!
This beautifully decorated 2 bedroom, 2 bath ground floor condominium home underwent a complete ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Mahana 919 is a 2 bedroom, 2 bath spacious corner unit has one of the most outstanding views at the Mahana and on the island - It is also one of the nicest places to stay!! This condo is on the south west corner of the building which means you ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
May we welcome you to Alihi Lani at Poipu. Our beautiful air conditioned condo located in the heart of Kauai's sunny south shore! Alihi Lani. or 'Beautiful Horizon Views', aptly describes the magnificent ocean panorama of Hawaiian sunsets, ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Poipu
Aloha, we would like to welcome you to a beautiful ocean front condo designed by Peter Rusnak. Hawaiian decor with island style furnishings throughout the unit. You will experience Hawaii while relaxing in a luxury 2 bedroom condo.
H209...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
*note: if you are booking a short stay for 7 or less days, please inquire first before doing a reservation request, unless your arrival date is less than 30 days away, in which case you're welcome to proceed with the reservation ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Open the door to our beautiful second home in Kauai and know you are in paradise. There is nothing between you and the vast blue ocean, just 35 feet away, which is framed by a wall to wall expanse of floor to ceiling windows. The soothing sounds...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
***WOW - DIRECT OCEANFRONT - AC***
SANDS OF KAHANA
The Aloha Spirit invites you to come and experience firsthand the many activities and beautiful sunsets that await you at the Sands Of Kahana. Whether you're looking for a relaxing ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Plan an idyllic getaway to Marriott's Kauai Beach Club in Lihue. Steps from the water on Kalapaki Beach, this beautiful Vacation Ownership resort offers premium condo-style villas directly on Kalapaki Beach. Experience unforgettable, secluded,...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lihu?e
I only have a reservation for the date specified. Other dates can be acquired if they are far enough out.
Mar 15-22 Oceanfront View. Best view.
Cloaked in natural beauty, The Point at Poipu is a dream vacation destination on the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Coming to Ko Olina for a full month? Come stay in a great condo with a prime location at Ko Olina Kai gated community.
**Pool Closed for Renovation for Early December 2023***
December rate is discounted, due to 3 weeks of planned ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Ko Olina
Aloha and welcome to our home at The Palms at Wailea. The ocean view will take your breath away the minute you step inside. But it's the condo renovation and our commitment to our guests that will keep you coming back.
Our remodeled ...
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Book with confidence, this beautiful over sized, 2 bedroom, 2 bathroom condo that has stunning ocean views. This condo is managed by PMI Maui, a local Real Estate Brokerage that specializes in vacation rentals. We are located within a mile of ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This is an amazing opportunity to stay at this 4-Star resort for a reduced price of up to 70% discounted from the rate charged when booking directly with the resort. This resort offers stunning views directly to the ocean, which is steps away ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Enjoy one week of elegant luxury in my two bedroom unit right across from Kahana Beach. Check-in days are Wednesdays, Fridays and Saturdays. Amenities include pool, tennis courts, and air conditioning. Test the waters of nearby beaches famed for...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Whether you are coming to Waikiki on business or just to escape the stress of business with a vacation, you will find this 2 bedroom condo in a very desirable building your perfect tropical oasis. Designer -inspired décor echoes the peaceful ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Westin Ka'anapali Ocean Resort Villas - Ocean View 2 Bedroom Unit Sleeps 8
***PLEASE DO NOT PUT IN A RESERVATION REQUEST USING THE “REQUEST TO BOOK” FEATURE***
Book our Amazing Luxury Villa & let The Timeshare Brokers, LLC make ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Aloha! We are pleased to present our vacation rental at the Marriott Ko Olina Beach club.
Our spacious 1200 sq ft 2 bedroom Ocean View unit has a fully equipped kitchen, a separate living area (with pull out queen sofabed), and a dining ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kapolei
