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 '%Diamond Head%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Treat yourself to ultimate island luxury in this architecturally designed $5.5M home on 15,000 sq ft lot in one of the most desired locations on Oahu's south shore. Nestled on the slopes of Diamond Head just outside of Waikiki, this exquisite ...
Registration Number: 350020180000, GE-047-203-9424-01, TA-047-203-9424-01
- 4 bedroom
- 6 bathroom
- Sleeps 9
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
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
Fantastic Location! 2 bed, 2 bath, 1 parking stall in a secured bldg on the 44th floor with fabulous amenities. Across the street from Ala Moana Shopping Ctr & Ala Moana Beach Park, offering breathtaking views of Waikiki, Ala Moana Beach Park, ...
Registration Number: GE-053-211-1360-01, TA-053-211-1360-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Aloha E Komo Mai. Welcome to our vacation rental at the Waikiki Banyan.
Our unit, Waikiki Banyan Rental, is a LEGAL short term vacation rental at the Waikiki Banyan. (Registered with the C&C of Honolulu, Permit # ...
Registration Number: 2212-CCH-0749, TA-011-293-0816-01, 260250050777, GE-011-293-0816-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Newly renovated 3 bedroom, 2.5 bath home on Maunalani Hts. Tastefully furnished with high quality bedroom, living room and dining room furniture. HD Smart T.V.s in living room, kitchen and bedroom. Fully functional kitchen with new dishes, ...
Registration Number: TA-187-879-4240-02, 330180700000, GE-186-879-4240-01
- 0 bedroom
- 0 bathroom
- Sleeps 6
Hawaii> Honolulu
This exquisitely decorated two bedroom Coral Strand suite provides nearly 360 degree panoramic views along the Gold Coast beyond Waikiki Beach and back around to the notorious Diamond Head.
Spectacular oceanfront unit provides tropical ...
Registration Number: TA-133-472-0512-01, 310320030032, GE-133-472-0512-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Discover one of Oahu's luxury vacation rentals with its gorgeous private pool terrace overlooking the Pacific Ocean and Diamond Head, Aloha Nalu (meaning “warm, welcoming sea”) will surely sweep you into the spirit of aloha. The villa sits ...
Registration Number: GE-019-941-5808-01, 390130060000, TA-019-941-5808-01
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Honolulu
One of a kind nearly 3000 SF (280 SQ/M) high floor custom condo with one of the best views in Waikiki. Located a mere 5 minute walk to beach and across from the world famous Hilton Hawaiian Village, this truly unique and newly remodeled unit ...
Registration Number: TA-052-041-5232-01, GE-052-041-5232-01
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Honolulu
The condo is nicely appointed. Remodeled five years ago with new cabinets and kitchen appliances. New queen size bed/mattress replaced in March 2015. A very clean and comfortable apartment. Apartment equipped with washer and dryer. air ...
Registration Number: TA-153-327-4112-01, GE-153-327-4112-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Get away from typical tourist rentals and stay in this renovated and fully restored mid-century ranch-style Hawaiian home that harkens back to another era. Originally built in 1948, this home is situated at the base of the Aina Haina valley, a ...
Registration Number: 000001336610, GE-010-603-8272-02, TA-010-603-8272-01
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Honolulu
The best Diamond Head view in all of Waikiki is yours to behold from this immaculate, beautifully furnished and well-appointed condominium. This is for you, the guest who appreciates quality and value.
A spectacular, direct Diamond Head ...
Registration Number: 260280110064, GE-212-869-3248-02, TA-212-869-3248-01
- 3 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Completely immerse yourself into the island life in this brand-new, picturesque Diamond Head luxury vacation rental. With its striking ocean views and ideal location nestled between Diamond Head and Waikiki, it’s no wonder that time moves a ...
Registration Number: 310330530000, TA-119-792-4352-01, GE-119-792-4352-01
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Honolulu
This corner unit studio has 180 Degree views of the ocean, Diamond Head, Ala Wai Canal, sunrise and Manoa valley and mountain. Studio has full kitchen, free wifi, wall mount TV, and AC. Wonderful panoramic views of Waikiki.
Building ...
Registration Number: TA-130-191-5648-02, GE-130-191-5648-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
The Landmark is Waikiki's premier condominium. The Waikiki Landmark is close to the convention center and the famous Hilton Hawaiian Village.
The timeless elegance of the architecture comprises a pair of triangular towers; the Makai ...
Registration Number: 1260140390168, GE-000-298-5984-01, TA-000-298-5984-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Those of us who love the Hawaiian Islands feel a strong sense of place. We are connected to this land (aina) and to the ocean (wai) and to the winds (makani) that bring us renewal and peace. We strive to show Aloha (love, and consideration and ...
Registration Number: TA-097-665-4848-01, 310320290031, GE-097-665-4848-02
- 0 bedroom
- 1 bathroom
- Sleeps 2
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: GE-121-769-3184-01, TA-121-769-3184-01, 260250050315, 2211-CCH-0470
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
**Looking for other accommodations, please view my other unit located in the same building #2824127**
This unit is located on a high floor of the Waikiki Sunset with sweeping Diamondhead, ocean, coastline, and park views, this condo ...
Registration Number: TA-065-133-9776-01, W10360673-01, 1260280110204
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Welcome to Waikiki Landmark, where paradise meets luxury living. Come stay at this stunning high-floor views of the Ocean, Diamond Head, and Waikiki. This exquisite 2-bedroom, 2-bathroom unit with 1 parking spot embodies the essence of island ...
Registration Number: 1260140390147, GE-101-296-6912-01, TA-101-296-6912-01
- 2 bedroom
- 2 bathroom
- Sleeps 5
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-049-534-7200-01, GE-049-534-7200-01, 260250050349, STR Permit # 1619
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
-Luxurious Penthouse Retreat in the Heart of Honolulu
Indulge in the ultimate urban oasis with our luxurious 43rd-floor penthouse condo, perfectly situated in the vibrant Ala Moana Center area between Waikiki and Kaka'ako. This prime ...
Registration Number: GE-213-689-2928-01, TA-213-689-2928-01, 230160480382
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Enjoy stunning Diamond Head views in this cozy 1-bedroom Waikiki condo, just a 5-min walk to the beach! Perfect for families or groups, the condo sleeps six with 2 queen beds and a queen sofa bed. Relax with top-notch amenities, including a ...
Registration Number: GE-166-923-0592-01, TA-166-923-0592-01, 1260250050099
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Waikiki Sunset #PH3807 – 229 Paoakalani Ave. (7-Night Minimum)
This beautiful 38th floor penthouse suite boasts dramatic unobstructed ocean and Diamond Head views! The spacious 1308 square feet of modern design is cooled by ...
Registration Number: TA-180-105-0112-01, 260280110435, 2017, GE-180-105-0112-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Waikiki Beach Tower #2302 – 2470 Kalakaua Ave.
This spacious, centrally air-conditioned two-bedroom, two-bath condo (1025 square feet plus large lanai) has a breathtaking unobstructed panoramic ocean view! Photos do NOT do this ...
Registration Number: GE-062-789-0176-01, 260230070072, TA-062-789-0176-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Fairway Villa #2217 – 2345 Ala Wai Blvd
(30-Night Minimum. ALL MONTHLY RESERVATIONS ARE SUBJECT TO APPROVAL.)
Your Home Away from Home! Enjoy this recently remodeled contemporary two-bedroom, two-bath condo (723 sqft plus 151-sqft ...
Registration Number: TA-088-347-5968-01, GE-088-347-5968-01, 260210210269
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Honolulu
Waikiki Beach Tower #3602 – 2470 Kalakaua Ave.
This spacious, centrally air-conditioned two-bedroom, two-bath condo (1025 square feet plus large lanai with table and chairs) has one of the most breathtaking panoramic ocean views in ...
Registration Number: TA-153-878-1184-02, GE-153-878-1184-02, 260230070124
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Welcome to the Bird of Paradise, similar to a boutique hotel experience in the Diamond Head neighborhood. This Honolulu vacation rental is a short drive from Waikiki, International Marketplace, Ala Moana Shopping Center, Waikiki ZOO, and many ...
Registration Number: TA-188-577-3824-01, GE-188-577-3824-01, 350140350000
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
UNBELIEVABLE VIEWS!: This 2 BR condo is on the 35th floor of the Waikiki Sunset. It offers unmatched views of Diamond Head and Waikiki Beach. No expense was spared in this condo's remodel. Bold granite highlights the custom cabinetry and ...
Registration Number: GE-125-446-1440-03, 260280110400, TA-125-446-1440-01
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Book 'em, Danno
Experience TRUE ALOHA in this condo that was home to James MacArthur, the original Danno of Hawaii Five-0, and is still owned by the MacArthur family.
This premiere property is perfectly situated on ...
Registration Number: 310330150014, GE-064-250-8800-02, TA-064-250-8800-01
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Legally permitted rental - 2450 Koa Ave #92
Located in the heart of Waikiki steps from the beach, at The Seashore. On the 9th floor of a 10 floor building, near the Hyatt Regency and the Duke Kahanamoku statue.
This beautifully and ...
Registration Number: TA-204-535-8080-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Sweeping view of Ocean and Diamond Head from beautiful 1 bedroom sparkling just renovated condo unit in the Waikiki Sunset Hotel! The location is perfectly located just 2 short blocks to the main area of Waikiki beach, International ...
Registration Number: TA-027-765-7600-01, GE-027-765-7600-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Legally permitted vacation rental - 2947 Kalakaua Ave 968115
Stunning Gold Coast Diamond Head Condo fully remodeled over a two year period.
Perfectly situated on Hawaii's Gold Coast, this beautiful home has been treated to nothing but ...
Registration Number: TA-075-331-1744-01, 310320290016, GE-075-331-1744-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Escape to the Islands!
Charming studio at Diamond head beach hotel, an oceanfront building with spectacular oceanview, direct ocean access on one side, while boarding beautiful Kapiolani park on the other side.
The unit is tastefully ...
Registration Number: GE-021-988-7616-01, TA-021-988-7616-01, 310320290033
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Let your dream tropical vacation begin when you step into the olden days of Hawai’i here at Hale Le’ahi, an illustrious and expansive 3 story plantation board and baton wood-framed estate located on the lower slopes of the world famous ...
Registration Number: TA-021-803-2128-01, GE-021-803-2128-01, 1350020240000
- 5 bedroom
- 4 bathroom
- Sleeps 10
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: 260250050708, GE-159-959-8592-01, NUC #: 90/TVU-0682 - 1990/NUC-1138, TA-159-959-8592-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Waikiki Banyan #2603-T1 – 201 Ohua Ave.
Fresh and modern, you'll love the feel of this contemporary one-bedroom air-conditioned suite (533 sq.ft. plus lanai with table and chairs) at the repeat-guest favorite Waikiki Banyan! Gorgeous ...
Registration Number: TA-145-827-0720-01, 260250050265, GE-145-827-0720-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
This magnificent 2 bedroom penthouse apartment has sweeping ocean and Diamond Head views that will enchant you.
From the garden feature at the apartment entrance to the granite bench tops, top quality kitchen appliances, leather lounge ...
Registration Number: TA-194-259-9680-01, 260200580339, GE-194-259-9680-01
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Aloha to paradise, this studio is cozy, Charming, Comfortable and locate on a high floor with a panoramic ocean and diamond head view. We are located across the street from Fort Derussy Park and Hilton Hawaiian Village Resort. You are only a ...
Registration Number: 1260070260144, GE-094-530-5600-01, TA-094-530-5600-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: 2211-CCH-0483, GE-072-451-9936-01, TA-072-451-9936-01, 260250050321
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Waikiki Banyan #3511-T1 – 201 Ohua Ave.
Unobstructed panoramic views of Diamond Head, the Pacific Ocean, and the Gold Coast from this 35th floor unit! Modern and contemporary, this air-conditioned condo has tasteful Hawaiian touches, ...
Registration Number: TA-088-347-5968-01, GE-088-347-5968-01, 260250050399
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Newly remodeled studio unit at the Diamond Head Beach Hotel and Condominiums located at the Gold Coast area. Very friendly building located on the ocean with outdoor space for watching sunset and sunbathing. Laundry facilities available ...
Registration Number: TA-119-426-4576-03, 310320290032, GE-119-426-4576-03
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
This is a beautifully appointed 2 bedroom 2 bath condo with approximately 1170 square feet of living space and 1 parking stall on the convenient lobby level. This unit is on the 41st floor (one floor below the penthouse) of one of Waikiki's ...
Registration Number: TA-171-302-2976-01, 260120100656, GE-171-302-2976-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Colony Surf #PH6 – 2895 Kalakaua Ave
(30-Night Minimum. ALL MONTHLY RESERVATIONS ARE SUBJECT TO APPROVAL. Preference given to calendar month bookings.)
NEW TO OUR INVENTORY! You'll fall in love with the amazing Diamond Head and ocean...
Registration Number: TA-105-980-5695-01, GE-105-980-5695-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
This Diamond Head Oceanfront property is a nightly rental on the island of O'ahu NUC, 90/TVU-0390
Ideally located oceanfront with the famed Diamond Head Crater as a backdrop, this Gold Coast vacation rental home offers luxury and privacy ...
Registration Number: GE-203-544-1664-01, 310360150000, TA-203-544-1664-01
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Honolulu
Waikiki Sunset #2910 – 229 Paoakalani Ave.
Enjoy BREATHTAKING colorful blues of the Pacific Ocean and the lush greenery of Diamond Head and Kapiolani Park from this 29th floor, 1-bedroom condo (534 sf interior plus 67 sf lanai) with ...
Registration Number: 260280110312, 2013, TA-129-286-7584-01, GE-129-286-7584-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
09/18/24: BUILDING NOTICE: Please be advise the building working on elevators, there will be 3 working elevators for each tower.
05/30/24: BUILDING ...
Registration Number: STR #94, 260250050053, TA-044-243-2000-01, GE-044-243-2000-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Large 529 sq ft studio on the 17th floor with diamond head and ocean views. Full size kitchen. Walk in closet. Flat screen tv. A/C. Building located just steps from Waikiki Beach, world class dining, hotels and shopping. Secured bulding ...
Registration Number: GE-036-401-8176-01, TA-036-401-8176-01, 1260260140090
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Summary:
Enjoy this tastefully decorated studio with lots of natural light and cool ocean breezes. This unit has a private lanai, great for enjoying your morning coffee or afternoon tea, and has a great view of Diamond Head, Kapiolani Park ...
Registration Number: GE-142-392-3200-01, TA-142-392-3200-01, 310320290019
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Legally permitted vacation rental - 2947 Kalakaua Ave #1401
** For your health and safety, we utilize additional Sterilization procedures between reservations.
Rooftop Penthouse - Sitting above the Pacific Ocean and nestled under ...
Registration Number: TA-199-305-2160-01, 310320290061, GE-199-305-2160-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Legally permitted vacation rental - 2947 Kalakaua Ave., 303
** For your health and safety, we utilize additional Sterilization procedures between reservations.
Whether for business or pleasure the delightful attention to detail ...
Registration Number: TA-035-754-8032-01, 310320290013, GE-035-754-8032-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
