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 (`headline` like '%Waikiki Beach Tower%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Relax in this beautifully renovated unit with ocean views at Waikiki’s finest oceanfront condo building. Wake up to the crystal blue ocean from the luxurious king bed in the master bedroom, entertain family and friends in the open kitchen ...
Registration Number: TA-013-407-1808-01, 260030070029, GE-013-407-1808-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
9/29/23: Please be advised that effective Monday, November 6, 2023, the 4th floor main pool deck
will be closed to begin remedial repair work to ...
Registration Number: TA-169-845-9648-01, GE-169-845-9648-01, STR # 1826, 260230070034
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Summary:
Located in the heart of Waikiki, this 2 Bed 2 Bath Luxury Oceanfront Suite is located in the Aston Waikiki Beach Tower Hotel! This property features floor to ceiling windows which open to a large private balcony that overlooks ...
Registration Number: 260230070019, TA-086-342-6560-01, GE-086-342-6560-01
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Honolulu
Waikiki Beach Tower
Relax in this exclusive upper floor unit at the most luxurious vacation rental tower on the beach in Waikiki. This 1,200 SF newly renovated condo has the only open floor plan in the entire building and its sky high ...
Registration Number: 260030070083, TA-118-133-4016-01, GE-118-133-4016-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Aston Waikiki Beach Tower2 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 process ...
Registration Number: TA-162-883-7888-01, 260230070000, GE-199-896-9344-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Visit https://www.youtube.com/watch?v=CVPLLGUG9Jw for a video walk through of this royal penthouse suite. Truly a WOW Top floor Penthouse!
Soar above Waikiki with a 2 bed, 2 bath, 1,092 sqft suite, featuring stunning coastline, sandy beach, ...
Registration Number: TA-058-201-7024-01, 260230070142, GE-058-201-7024-01
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Honolulu
Waikiki Beach Tower #2003 – 2470 Kalakaua Ave.
NEW TO OUR INVENTORY! This fully remodeled (October 2024) full ocean view condo is the perfect home base for your dream Hawaiian vacation. Take in the stunning panoramic views of Waikiki ...
Registration Number: 260230070061, TA-171-986-6880-01, GE-171-986-6880-01
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Experience the ultimate in beachfront luxury with this 2-bedroom, 2-bath condo that offers stunning ocean views and comes with complimentary valet parking. Situated just a few footsteps away from the beach, you'll be in the most vibrant ...
Registration Number: TA-170-708-6336-01, GE-170-708-6336-01, 260230070003, STR#1780
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Welcome to Aston Waikiki Beach Tower by Gather, a stunning 2-bedroom, 2-bathroom condo perfectly located in the vibrant heart of Waikiki. With breathtaking 180-degree views of Queen’s Beach and mesmerizing sunsets, this condo offers the ideal ...
Registration Number: GE-103-524-9152-01, TA-103-524-9152-01, 1260230070008, STR#2143
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
