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 '%ilikai marina%') AND sleeps>=1 order by rating desc LIMIT 0, 30
This condo at the Ilikai Marina is located on the 3rd floor. For those of you that don't like high floors and like to feel close to the ground overlooking the beautiful Pacific Ocean and marina. You'll be able to wake up to the beautiful and ...
Registration Number: TA-159-542-0672-01, GE-159-542-0672-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
NEW ON THE RENTAL MARKET AFTER A COMPLETE DESIGNER RENOVATION!
BY FAR THE BEST LOOKING UNIT IN THE ENTIRE BUILDING!
We're accepting reservations on an on-going basis.
Superb Location next to the water!
Completely remodeled ...
Registration Number: GE-049-737-5232-01, TA-049-737-5232-01, 260100020120
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Welcome to the family-friendly Ilikai Marina Hotel! On the 12th floor, this unit faces west, treating you to stunning ocean and harbor views from your bedroom and living room. Just a few steps away, indulge in the famous beaches' warm sands and ...
Registration Number: 260100020134, GE-156-537-0368-01, TA-156-537-0368-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Summary:
Welcome to our oceanfront home offering stunning ocean views and Hawaiian sunsets, all within walking distance to the beach. Whether you're seeking adventure, family fun, or relaxation, this is the perfect destination while visiting ...
Registration Number: GE-040-719-3088-01, 260100020114, TA-040-719-3088-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Welcome to my place here in Waikiki with a queen bed in the bedroom and a queen bed sofa couch. There is a solid sliding door between the bedroom and the living room area. (wood door)
The entrance to Ilikai Marina Condo is on Hobron Lane ...
Registration Number: GE-093-328-7936-01, TA-093-328-7936-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Hawaii tax ID#: TA-167-299-1232-01
TMK: 260100020032
STR Permit #: 2211-CCH-0297. Expires 11/16/24
EVERY LEGAL SHORT TERM RENTAL IN HAWAII MUST HAVE AN OFFICIAL STR PERMIT NUMBER. OURS IS LISTED ABOVE. YOU CAN BOOK AND STAY ...
Registration Number: GE-167-299-1232-01, TA-167-299-1232-01, 260100020032
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Great value for a superb oceanfront view condo in Waikiki. Close to beaches, Hilton Lagoon, surfing, restaurants and a short walk to Ala Moana Shopping Center. • Waikiki Oceanfront Location• Golfing, Tennis, Swimming, Snorkeling & Surfing ...
Registration Number: GE-177-103-6672-01, 260100020051, TA-177-103-6672-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
Enhanced Protocols : We have revised our protocols to include contactless check-in & check-out, professional EPA approved non-toxic undetectable ...
Registration Number: 126102229000, GE-191-015-5776-01, TA-191-015-5776-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
The Ilikai Marina has centrally located between 2 of the island's largest shopping, dining, and entertainment districts Waikiki and Ala Moana. You have several dining options in the building as well with Chart House, Outback Steakhouse, and Red...
Registration Number: TA-024-483-6864-01, 1260100020048, GE-024-483-6864-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Located within the Ilikai Marina, this condo is located on the 9th floor. You'll love the convenient location in Waikiki, close to the beach, Ala Moana Shopping Center & the Waikiki strip!
KITCHEN
- Refrigerator
- Range/Oven
-...
Registration Number: TA-099-292-4160-01, GE-099-292-4160-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
180 Ocean Degree Views from this Beautiful End Unit
Small Oceanview Lanai with Sliding Door
Beautiful New Travertine Floors Throughout
Beautiful Koa Furniture, Window Coverings, & Linens
Foldout Queen Sofa with Koa Frame
Generous ...
Registration Number: 260100020136, GE-170-946-1504-01, TA-170-946-1504-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Full remodel 2024! New kitchen, bathroom, all new appliances and furniture.180 Ocean Degree Views. Hardwood Floors, tile bathroom. Small Oceanview Lanai with sliding door. King Size Bed with 800 Thread Count Sheets. Fold-out Queen Size Sleeper ...
Registration Number: 260100020111, GE-098-476-2368-01, TA-098-476-2368-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Great value for a superb oceanfront view condo in Waikiki. Close to beaches, Hilton Lagoon, surfing, restaurants and a short walk to Ala Moana Shopping Center. • Waikiki Oceanfront Location• Golfing, Tennis, Swimming, Snorkeling & Surfing ...
Registration Number: GE-177-103-6672-01, 260100020132, TA-177-103-6672-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
This condo recently had their kitchen and bathroom renovated with all brand new appliances! Looks and feels AMAZING! This condo is located in the Ilikai Marina on the 6th floor with a mesmerizing view of the ocean overlooking the yacht harbor....
Registration Number: GE-193-846-8864-01, TA-193-846-8864-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
UNOBSTRUCTED SUNSET/OCEAN VIEWS! STEPS TO WAIKIKI BEACH. Walk to parks, shopping mall, restaurants, shops, buses, rental cars & Waikiki night life. Parking available. No smoking room. Clean and fresh high floor.
UNOBSTRUCTED SUNSET/OCEAN ...
Registration Number: GE-046-192-0768-01, TA-046-192-0768-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
The Ilikai Marina is centrally located between 2 of the islands largest shopping, dinning, and entertainment districts Waikiki and Ala Moana. You have several dining options in the building as well with Chart House and Red Lobster on the ground...
Registration Number: TA-010-375-2704-01, GE-010-375-2704-01, 1260100020050
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Location and Amenities, that's what this studio is about. Directly next to the Prince Waikiki Hotel and a 15-20minute walk to the Convention Center - you are located at the gateway to Waikiki.
You won't need anything but your ...
Registration Number: GE-156-762-3168-01, TA-156-762-3168-01, 260100020241
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
STUNNING OCEAN & SUNSET VIEWS! STEPS TO WAIKIKI BEACH. Walking distance to parks, shopping mall, restaurants, shops, buses, rental cars, parks & Waikiki night life. New Sofa bed recently installed. Parking available. No smoking ...
Registration Number: GE-194-253-8240-01, TA-194-253-8240-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Waikiki
Located within the Ilikai Marina, this condo is located on the 18th floor. You'll love the convenient location in Waikiki, close to the beach, Ala Moana Shopping Center & the Waikiki strip! On the top floor of the Ilikai Marina, you'll have ...
Registration Number: GE-206-695-6288-01, TA-206-695-6288-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Located within the Ilikai Marina, this condo is located on the 17th floor. You'll love the convenient location in Waikiki, close to the beach, Ala Moana Shopping Center & the Waikiki strip!
KITCHEN
- Refrigerator
- Electric ...
Registration Number: GE-206-695-6288-01, TA-206-695-6288-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Ilikai Marina building is ideally situated in a privileged location in Waikiki, just steps away from the picturesque Hilton Hawaiian Village lagoon....
Registration Number: TA-067-365-4784-01, GE-067-365-4784-01, 260100020036
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
One of the best values for an oceanfront condo in Waikiki. Close to great Honolulu activities and a short walk across the Ala Wai Canal for quick access to Ala Moana Beach Park and Shopping Center.
Faces directly west for sunset views ...
Registration Number: TA-113-797-5296-01, GE-113-797-5296-01, 260100020049
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Relaxing condo with a beautiful ocean front view! • Waikiki Oceanfront Location• Golfing, Tennis, Swimming, Snorkeling & Surfing Nearby• Direct Oceanfront Condos• Panoramic Views with Incredible Sunsets• Walk to Groceries & Shopping•...
Registration Number: TA-111-177-5232-01, GE-111-177-5232-01, 260100020078
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Completely redesigned and renovated unit, our large 1BR suite, kind bed, sofa bed in the living room, w spectacular Ocean views from balcony, new kitchen, located at The Ilikai Marina Condo. The best views in Honolulu w/ Ala Moana harbor ...
Registration Number: TA-032-035-4304-01, GE-032-035-4304-01, 260100020028
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Located within the Ilikai Marina, this condo is located on the 3nd floor. As you stand on the lanai of this condo, you look straight out to see the Ala Wai Yacht Harbor below and turquoise ocean waters beyond. If you love to surf, this is the ...
Registration Number: TA-048-703-0784-01, GE-048-703-0784-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Wow, loving the new kitchen & bathroom! Just finished a kitchen and bathroom renovation to convert the tub to a shower and freshly painted!
If you're looking for the best deal in town, look no further! This roomy studio condo is located ...
Registration Number: TA-022-570-5984-01, GE-022-570-5984-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Panoramic, 180 degree Ocean, Harbor and Sunset View
Spacious 1 Bedroom, 1 Bath Condo
Fully Equipped Gourmet Kitchen
Beautiful Rattan Upholstered Furniture
Very Comfortable Full King Size Bed
Foldout Queen Size Sleeper Sofa
Free...
Registration Number: TA-127-123-0464-01, GE-127-123-0464-01, 260100020115
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Just installed new flooring and painted the whole unit!
Great property with amazing 180 degrees ocean view! Enjoy couple minute walk to the beach. Restaurants, bars, all the attractions are just within few minutes walk! Next to the bus ...
Registration Number: TA-102-471-0656-01, GE-102-471-0656-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Firework! Great Ocean View! Ilikai Marina (IM92)
In the sun
Feel the sun
Walk barefoot on the sand
Feel the Ocean
Relax
Everyday sunny day
Soak up Nature While In Hawaii!!
This modern studio is bright thanks to the top to...
Registration Number: 260100020060, GE-073-540-2496-01, TA-073-540-2496-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
STUNNING OCEAN & SUNSET VIEWS! STEPS TO WAIKIKI BEACH. Walking distance to parks, shopping mall, restaurants, shops, buses, rental cars, parks & Waikiki night life,.
The Ilikai Marina is located at the gateway to Waikiki.
650 ...
Registration Number: GE-099-604-8896-02, TA-099-604-8896-02
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Waikiki
