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, 30
Hawaii Tax ID No. TA 200-103-3216-01, 2020 NUC No. 90-TVU-1101
The Condo is located one block from the world-famous Waikiki Beach. The unit offers luxury living with peaceful mountain, canal and golf course views. It has been recently ...
Registration Number: W13230254-01, 260250050627, TA-200-103-3216-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
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
- 0 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 4
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
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, ceiling fan and tile floors. ...
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 bight 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
It’s hard to resist this deluxe 396sf haven with the spacious sundeck opening to spectacular ocean and Diamond Head views! Touches include air conditioning, ceiling fan, WiFi, flat screen, and modern kitchenette with breakfast bar and stovetop...
Registration Number: GE-142-548-9920-01, 260270310170, TA-142-548-9920-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> HONOLULU
Enjoy your stay in this well-designed Waikiki 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 an ...
Registration Number: GE-158-478-1312-01, TA-158-478-1312-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Your Waikiki 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 Waikiki vacation. With a stellar location, you’ll enjoy gorgeous views of the Pacific Ocean and Waikiki beach from your furnished, ...
Registration Number: TA-076-605-4400-01, GE-076-605-4400-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Though you’re just steps from the excitement of Waikiki and the sparkling waters of Waikiki Beach, this comfortable deluxe studio is quietly tucked away amid the tropical greenery of Fort DeRussy Park. Unit 314 includes a platform queen bed, ...
Registration Number: TA-199-188-0704-01, GE-199-188-0704-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
This authentic Waikiki 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
- 0 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
Are you craving more aloha vibes in your life?
Look no further than the Aqua Aloha Surf Waikiki, nestled just off Kuhio Avenue! As soon as you step into our inviting open-air lobby with its delightful art-deco surf atmosphere, you'll know...
Registration Number: TA-174-630-5024-01, GE-174-630-5024-01, 260210160166
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
***PLEASE NOTE:
From April 22nd, all elevators at Waikiki Banyan will undergo complete replacement. Each tower will have 1 elevator replaced at a time. During this period, there will be 3 elevators in operation instead of 4, leading to ...
Registration Number: 260250050736, TA-088-191-6416-01, GE-088-191-6416-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
True One bedroom one bathroom unit. New listing and new luxury renovations.
Be one of the first ones to stay in this beautiful and luxurious condo!
We put a lot of attention to details to make sure our guests enjoy their vacations to the ...
Registration Number: TA-000-717-6192-01, GE-000-717-6192-01, 260100070637
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Want to be steps from Waikiki beach, fitness venues, and shopping, while having the advantage of a well-equipped kitchen for healthy meals? We offer fully renovated, upscale studio with all the conveniences of home. Fitness and ...
Registration Number: GE-130-575-5648-01, TA-130-575-5648-01
- 0 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
- 2 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
- 2 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
Newly renovated 1 BR/1 BA unit located 21/2 blocks from Waikiki Beach and one block from the International Market Place.
Shopping, restaurants, grocery store, bus line and everything Waikiki has to offer are all within walking distance. 15th...
Registration Number: TA-042-634-6496-01, GE-042-634-6496-01, 260210210218
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
This is a fabulous newly renovated, with new owner, so we do not have many reviews yet. You will never want to leave!
Awesome ….well appointed, privately owned, beautifully furnished. Just remodeled, kitchen, new furniture, beds and ...
Registration Number: 260230070010, GE-072-016-8960-01, TA-072-016-8960-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Hotel Zoned & %100 legal rentals - TROPICAL STUDIO IN WAIKIKI LLC
TA-193-411-2768- 01, GE-193-411-2768- 01, TMK: 260190010000
364 Seaside Ave , Honolulu, HI, 96815 - MARINE SURF WAIKIKI ( condominium building )
This ...
Registration Number: 260190010000, TA-193-411-2768-01, GE-193-411-2768-01
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
This property is a 30-day minimum rental and is professionally managed by Ali'i Beach Rentals.
The Fairway Villa Located at 2345 Ala Wai Blvd. 11th Floor
Living/Dining Area
- 1 Couch (not a sofa bed)
- Dining Table w/4 ...
Registration Number: TA-168-124-0064-02, GE-168-124-0064-02
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Hotel Zoned & %100 legal rentals - TROPICAL STUDIO IN WAIKIKI LLC
TA-193-411-2768- 01, GE-193-411-2768- 01, TMK: 260190010000
364 Seaside Ave , Honolulu, HI, 96815 - MARINE SURF WAIKIKI ( condominium building )
This ...
Registration Number: GE-193-411-2768-01, 260190010000, TA-193-411-2768-01
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Hotel Zoned & %100 legal rentals - TROPICAL STUDIO IN WAIKIKI LLC
TA-193-411-2768- 01, GE-193-411-2768- 01, TMK: 260190010000
364 Seaside Ave , Honolulu, HI, 96815 - MARINE SURF WAIKIKI ( condominium building )
This ...
Registration Number: 260190010000, GE-193-411-2768-01, TA-193-411-2768-01
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu