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 (registration_number like '%TA-%' or registration_number like '%TA %') AND (city = '96815' OR postalCode = '96815' OR region like '%|96815|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Top-of-the-line modern, high-end designer remodel. The direct ocean view will greet you as soon as you open the door! This studio unit boasts a unique feature of separation doors that create a one bedroom atmosphere. The open floor plan sweeps ...
Registration Number: GE-170-984-8576-01, TA-170-984-8576-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Waikiki Sunset 2105 Paradise Awaits 1-bedroom Superior Suite with Incredible Views
World Famous Waikiki White Sandy Beaches only one block away, Incredible Views. After playing all day in the sun, you will be happy to come "home to Your...
Registration Number: GE-181-163-0080-01, TA-181-163-0080-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
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, ...
Registration Number: 260260140106, TA-069-363-0976-01, GE-069-363-0976-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Waikiki Shore #1302 – 2161 Kalia Rd.
(ALL ONLINE RESERVATIONS CONSIDERED REQUESTS SUBJECT TO APPROVAL.)
You'll enjoy spectacular ocean and Diamond Head views from this luxurious 2-bedroom, 2-bath condo on one of the uppermost, ...
Registration Number: GE-039-999-8976-01, 260040120125, TA-039-999-8976-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Four Paddle #2311 – 2140 Kuhio Ave
(30-Night Minimum. ALL MONTHLY RESERVATIONS ARE SUBJECT TO APPROVAL.)
Located on the cool side of the building, you'll enjoy lovely Ala Wai Canal and Ko'olau Mountain views from this well-equipped ...
Registration Number: GE-173-535-8976-01, TA-173-535-8976-01, 260170570225
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Castle at Waikīkī Grand #1003 - Relax with mai tais on the sundeck with ocean and Diamond Head view. This stylish, upgraded 396sf studio features air conditioning, open kitchenette with granite countertop breakfast bar, WiFi, flat screen, DVD,...
Registration Number: TA-203-430-0928-01, GE-203-430-0928-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> HONOLULU
Updated in 2014, this deluxe one-bedroom one-bathroom suite with Pacific Ocean views has modern features that beach-goers will love! This bright and airy suite has a beachy décor, modern island furnishings, and Hawaiian-style artwork. You’ll ...
Registration Number: GE-066-371-9936-01, TA-066-371-9936-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> HONOLULU
Get comfy in this island-designed one-bedroom one-bathroom deluxe ocean view suite! You’ll love the wicker furniture, full-size kitchen, and enclosable bedroom with a King size bed. Watch the sun set over the Pacific Ocean from your private ...
Registration Number: GE-132-795-8016-01, TA-132-795-8016-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> HONOLULU
Enjoy your stay in this well-designed Waikīkī beach deluxe studio with views of the Pacific Ocean! You’ll love the charming wicker King-sized bed frame, modern furnishings, and granite-top kitchenette. This unit has everything you need for ...
Registration Number: GE-158-478-1312-01, TA-158-478-1312-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Your Waikīkī vacation begins in this deluxe one-bedroom one-bathroom ocean view suite, which was updated in 2015. You’ll love the kitchen, which has lovely wooden cabinetry, stainless steel appliances, and a view! You’ll also have indoor ...
Registration Number: GE-199-290-0608-01, TA-199-290-0608-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> HONOLULU
"This tastefully decorated island suite is fully equipped with everything you need to have a fantastic Waikīkī vacation. With a stellar location, you’ll enjoy gorgeous views of the Pacific Ocean and Waikīkī beach from your furnished, ...
Registration Number: TA-076-605-4400-01, GE-076-605-4400-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Though you’re just steps from the excitement of Waikīkī and the sparkling waters of Waikīkī Beach, this comfortable deluxe studio is quietly tucked away amid the tropical greenery of Fort DeRussy Park. Unit 314 includes a platform queen ...
Registration Number: TA-199-188-0704-01, GE-199-188-0704-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
This authentic Waikīkī beach deluxe ocean view studio is the perfect island getaway! You’ll love the unique décor, like the teal backsplash in your kitchenette, white wicker furniture, and the ocean-toned walls. This studio has a furnished ...
Registration Number: GE-176-531-4560-01, TA-176-531-4560-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Imagine your self on the beaches of Waikiki staying in a luxury condo. Hale Kuhio (Home on Kuhio) is the best 30 day Waikiki vacation rental hands down.
This property may not be rented for fewer than 30 consecutive days. Rental prices will ...
Registration Number: GE-104-002-4576-01, TA-104-002-4576-01, 260170570018
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
OUR GUIDELINES FOR COVID-19: OUR PROPERTY IS PROFESSIONALLY CLEANED AND SANITIZED AFTER EACH STAY, AS PER CDC CLEANING GUIDELINES FOR COVID-19. WE HAVE AT LEAST A 24 HOURS GAP BETWEEN THE CHECKOUT AND THE NEXT CHECK-IN FOR OUR PROPERTY.
I...
Registration Number: 260250050692, GE-085-643-4688-01, TA-085-643-4688-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Aloha from Midway Vacations! Our team of local hospitality experts is dedicated to turning your Hawaiian getaway into a reality. When you stay at one of our properties, you can count on a professionally cleaned and meticulously maintained ...
Registration Number: TA-174-630-5024-01, GE-174-630-5024-01, 260210160166
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Aloha from Midway Vacations! Our team of local hospitality experts is dedicated to turning your Hawaiian getaway into a reality. When you stay at one of our properties, you can count on a professionally cleaned and meticulously maintained ...
Registration Number: 260250050736, TA-088-191-6416-01, GE-088-191-6416-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Location, location, location! Our elegantly appointed studio is a block from Waikiki Beach, and at the center of shopping, restaurants, and nightlife. We offer FREE parking, high speed WiFi, and a well-equipped kitchen with essentials for ...
Registration Number: 260190010177, GE-130-575-5648-01, TA-130-575-5648-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
This rental is on the 19th floor of the Ilikai Hotel with direct lagoon and ocean views. This 1 bedroom apartment has been remodeled into a large 500 sq ft studio and is gorgeous! High-end materials were used throughout the apartment. We used ...
Registration Number: GE-177-208-7296-01, 260100070792, TA-177-208-7296-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Fairway Villa #2417 – 2345 Ala Wai Blvd
(30-Night Minimum. ALL MONTHLY RESERVATIONS ARE SUBJECT TO APPROVAL.)
HIGH FLOOR! TOP TO BOTTOM RENOVATION! Your Home Away from Home! Enjoy this recently renovated two-bedroom, two-bath condo ...
Registration Number: TA-088-347-5968-01, GE-088-347-5968-01, 260210210305
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Honolulu
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 ...
Registration Number: TA-212-379-4432-01, GE-212-379-4432-01
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Located just a block from Waikiki Beach and surrounded by top-tier hotels, this gorgeous penthouse condo offers the style and comfort of a luxury hotel with the added benefits of 1700+ square feet of living space, a large well-appointed kitchen,...
Registration Number: GE-158-232-3712-01, TA-158-232-3712-01
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
See my other properties #3156864 and #3197017 and #3512192 for more 5-star reviews and to book multiple condos for large groups.
Located on a high floor of the Waikiki Sunset with sweeping ocean, coastline, and park views this condo ...
Registration Number: TA-065-133-9776-01, GE-065-133-9776-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
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 ...
Registration Number: TA-044-981-6576-01, 1260270260002, GE-044-981-6576-01
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Honolulu
We are please to provide our Suite units by Hilton Grand Vacations.
The resort gives guests ideal access to the best stretch of Waikiki's famous white-sand beach and the extensive entertainment, shopping and dining options within the ...
Registration Number: 123456789012, GE-003-799-2960-01, TA-003-799-2960-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
* Best Views and the Best Value in Waikiki! This unit is completely and beautifully redone with quality and function, 'Pride of ownership for our Guests to Enjoy'.
* This unit has a 30 day minimum requirement.
* This Spacious ...
Registration Number: GE-038-608-6912-01, 260210260668, TA-038-608-6912-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Visits to Hawaii have always been expensive, but each year prices seem to go higher. However, I think if you compare my condo with other options, you will realize it is a good deal. I try to keep the nightly rate about $100 less than what the ...
Registration Number: GE-088-423-0144-01, TA-088-423-0144-01
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
This property is a LEGAL SHORT TERM VACATION RENTAL located in the resort zoned, oceanfront, Ilikai Apt. Building!
This gorgeous, bright and airy, 1bd/1bth oceanfront, ocean/lagoon view unit, located in the iconic Ilikai Apt Building ...
Registration Number: TA-160-575-0784-01, 260100070188, GE-160-575-0784-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Aloha and Welcome!
Sans Souci, which means ' No worries' in French
One of the very few 2 bedroom Beach Front Condo on the beautiful 'Gold Coast' in Waikiki. We are not near the beach or across the street, we are ON THE BEACH!
Open ...
Registration Number: 310320070019, TA-166-003-3024-01, GE-166-003-3024-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
This spacious two story home is nestled in a quiet historic neighborhood at the base of Diamond Head. The second story has a long lanai with views of Diamond Head. This home has 3 bedrooms with a study that can also be used as a bedroom. All ...
Registration Number: GE-198-568-1408-01, TA-198-568-1408-01
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Honolulu
Hawaii Tax ID #TA-137-632-1536-01 & GE-137-632-1536-01
TMK/Parcel# 260020140286 Address: 205 Lewers St Unit C/12B, Honolulu, HI 96815
Located atop the newly remodeled Imperial Hotel at the Waikiki Beach Walk! New salt water rooftop pool ...
Registration Number: GE-137-632-1536-01, 260020140286, TA-137-632-1536-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Please no party or event inquiries.
As you drive onto the property of this Historic Home, built in 1937 by Charles W. Dickey and recently restored, you are greeted by your own private park. Native Kukui and Ulu trees rustle in the cool ...
Registration Number: TA-115-667-1488-01, GE-115-667-1488-01
- 3 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Honolulu
This property is a 2 minute walk to the beach. Set moments from Waikiki Beach and offering easy access to a variety of area attractions, this condominium features spacious accommodations along with a variety of modern and thoughtful ...
Registration Number: TA-124-845-1072-01, GE-124-845-1072-01, 26002014
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Large beach front studio (460 sq, ft,) located at the Waikiki Shore, the only condo building in Waikiki that is directly on Waikiki Beach.
This unit is located in a building that is within the designated Resort Zone, and therefore eligible ...
Registration Number: GE-202-106-0608-01, 126004012109, TA-202-106-0608-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Fall asleep to lapping waves on a quiet corner of Waikiki in this beautiful modern haven. Located on the sought after 3rd floor of the famed Colony Surf on the Gold Coast end of Waikiki. Clean and modern remodel in 2018: all new cabinets, ...
Registration Number: 131032010002, GE-072-845-0048-01, TA-072-845-0048-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
The Waikiki Banyan is located in the heart of East Waikiki and just 1 block away from the warm sands and azure waters of world famous Waikiki Beach. Just steps away are the Honolulu Zoo, Kapiolani Park, Waikiki Aquarium, world-class shopping, ...
Registration Number: GE-031-951-0528-01, TA-031-951-0528-01, 1260250050706
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Famed COLONY SURF condo on Oahu's 'Gold Coast' on the Diamond Head end of Waikiki. This romantic get-a-way is perfect for an unforgettable Hawaiian vacation. An extra large DIRECT OCEANFRONT 1,440 sq' condo with a 25' long panel of windows in ...
Registration Number: 001313210146, GE-134-682-0096-01, TA-134-682-0096-01
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Located on the same block as the Fort DeRussy Park, where no other hotels obstruct the fantastic ocean and park views. Our condominium is a studio with kitchenette. It has a two burner cook top, dual purpose microwave oven/ standard oven, ...
Registration Number: TA-063-675-5968-01, GE-063-675-5968-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Large, well appointed 2 bed 2 bath condo located across the street from Waikiki Beach with direct ocean views from window. Condo sleeps up to 6 people comfortably: King in Master, Queen & Day bed in guest and futon on the lanai. New ...
Registration Number: GE-055-488-7168-02, TA-055-488-7168-02, 260260140068
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Aloha, our condo is located in the family friendly Waikiki Banyan on the 12 floor. We have a beautiful side ocean view from our large lanai. The unit has a fully equipped kitchen and spacious living area.
The fully furnished living room...
Registration Number: 260250050084, TA-052-862-9760-01, GE-052-862-9760-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Hilton Hawaiian Village one-bedroom villa in the heart of Waikiki.
Thank you for choosing Dash Realty Group of Windermere, Florida. We are a registered Fla. Seller of Travel Ref. No. ST42435.
Please choose a Friday, Saturday, or ...
Registration Number: TA-000-785-4592-01, GE-000-785-4592-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Kaiolu Sunrise is the perfect Waikiki vacation. This is the kind of place that I always wanted to stay in when traveling to Honolulu. A small building – only 11 floors high, very clean and ideally located 3 blocks from the world famous Waikiki...
Registration Number: GE-209-003-5712-01, TA-209-003-5712-01, 260170600040
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Beautifully decorated Waikiki 2br/1ba 8th floor condo in secured building, pool, assigned parking, steps to public transportation, a/c, in unit washer/dryer, new mattresses (Master bedroom king mattress, second bedroom queen mattress), new paint...
Registration Number: TA-003-954-9440-01, GE-003-954-9440-01, 260240970038
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Enjoy waking up to paradise right outside your window. Step out on your private lanai and breathe in the sweet tropical air as you watch the graceful surfers ride the gentle breaking waves.
Take advantage of your home-away-from-home and ...
Registration Number: GE-083-255-2960-01, TA-083-255-2960-01
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Honolulu
Imagine indulging in a Hawaiian vacation experience from the moment you wake up and open your eyes. Leisurely laze in bed as you enjoy sunrise through the wall-to-wall windows from a variety of angles. Watch the backdrop throughout the day like ...
Registration Number: 260140390143, GE-078-467-0720-01, TA-078-467-0720-01
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Honolulu
Great care was taken in creating this comfortable, practical, and modern luxury home with ocean views from every room. Materials, colors and features uncommon in a rental were thoughtfully combined to create a unique, calm environment for ...
Registration Number: TA-066-888-7040-01, GE-066-888-7040-01, 126012010453
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Shangrila, is a Charming Spacious Studio that is ready to Relax you. It
has all the amenities you need to come and Enjoy Hawaii!
FREE High Speed Internet and cable package
The Shangrila is a spacious 400 + sqft apartment with ...
Registration Number: TA-077-720-3712-01, GE-077-720-3712-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Aloha E Komo Mai. Welcome to our vacation rental at the Waikiki Banyan.
This is a LEGAL short term vacation rental at the Waikiki Banyan. (Registered with the C&C of Honolulu, Permit # 2212-CCH-0749).
The Waikiki Banyan is one of...
Registration Number: 2212-CCH-0749, TA-011-293-0816-01, 260250050777, GE-011-293-0816-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Spectacular high floor unit, stunning panoramic views of Waikiki, sunsets, surfers and Diamond Head. Beautiful 1 bedroom oceanfront condo with easy Waikiki Gold Coast living is located in the Diamond Head Beach Hotel & Residences, right on the ...
Registration Number: 310320290052, GE-145-993-9328-01, TA-145-993-9328-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Waikiki Shore #818 – 2161 Kalia Rd.
(ONLINE RESERVATIONS CONSIDERED REQUESTS SUBJECT TO APPROVAL)
RENOVATED July 2021! You'll enjoy spectacular Ocean Views from this luxurious 8th floor beach condo (932 interior sqft + 160-sqft ...
Registration Number: GE-039-999-8976-01, TA-039-999-8976-01, 260040120075
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
