select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 and (registration_number like 'TA-%') AND (`headline` like '%Ilikai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
***All unvaccinated guests are required to show proof of negative covid results prior to checking in the unit. Otherwise, vaccinated guests have to show vaccination info***
*Please note that our vaccination/covid test requirements are not the ... Registration Number: TA-116-827-1360-03, GE-11
1 bedroom
1 bathroom
Sleeps 3
HI> Honolulu
Ready-to-work environment from the moment of check-in with complimentary high speed wifi.
Spacious desk for work-friendly environment.
Working from the Lanai with gorgeous ocean view.
Ilikai Oceanview & Hawaiian breeze - Easy access 3rd floor ... Registration Number: TA-016-725-4016-01, GE-01
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
NICE OCEAN AND POOL VIEWS! STEPS TO WAIKIKI BEACH. Memorable meals and fireworks from the lanai. Walking distance to Ala Moana, restaurants, buses, rental cars, parks & Waikiki night life. The iconic Ilikai Suites are located at the gateway to ... Registration Number: TA-079-731-5072-01
1 bedroom
1 bathroom
Sleeps 4
HI> Waikiki
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 VIEWS!... Registration Number: TA-046-192-0768-01
1 bedroom
1 bathroom
Sleeps 4
HI> 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, 26010
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
*AVAILABILITY CALENDAR IS ALWAYS CURRENT*
Hawaii Tax ID# 112-788-8896-01
This sixteenth-floor condominium, at the Ilikai Marina, has expansive views of the Pacific ocean, harbor, Waikiki Beach and Ala Moana Beach Park.
The expansive views at ... Registration Number: TA-112-788-8896-01, GE-11
1 bedroom
1 bathroom
Sleeps 3
HI> Honolulu
Enjoy stunning sunsets from your private balcony while sipping a glass of wine. This condo stands out because it has full, direct, unobstructed ocean views overlooking the Waikiki Marina... not peek-a-boo views.
You'll see ocean views from ... Registration Number: TA-094-185-9840-01, 26010
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
Luxurious unit w/unobstructed ocean views await you at this beautiful unit on the preferred & generally higher priced side of the building. Enjoy morning coffee & afternoon sunsets on the lanai with views of the coastline, breaking waves of ... Registration Number: TA-129-931-8784-01, GE-12
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
OCEAN & SUNSET VIEWS! STEPS TO WAIKIKI BEACH. AC, WIFI. Walking distance to shopping mall, restaurants, shops, buses, rental cars, parks & Waikiki night life.
**Recent Guest Review**
Kristina
March 2020
The Ilikai is an amazing property with a... Registration Number: TA-204-025-4464-01
1 bedroom
1 bathroom
Sleeps 5
HI> Honolulu
OCEAN & LAGOON VIEWS! STEPS TO WAIKIKI BEACH. Walking distance to shopping mall, restaurants, shops, buses, rental cars, parks & Waikiki night life. The iconic Ilikai Suites are located at the gateway to Waikiki.
Remodeled.
The iconic Ilikai ... Registration Number: TA-157-990-2976-01
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
STUNNING OCEAN, LAGOON VIEWS! STEPS TO WAIKIKI BEACH. Memorable Meals and fireworks from the Lanai. Walking distance to shopping mall, restaurants, shops, buses, rental cars, parks & Waikiki night life.
**Recent Guest Reviews**
Anne
March 2020
... Registration Number: TA-200-936-2432-01
1 bedroom
1 bathroom
Sleeps 4
HI> Waikiki
As soon as you open the door you will be blown away by gorgeous and unobstructed views of the Pacific Ocean, Ala Wai Marina and City of Honolulu, Ala Moana beach and green mountains in the background. This elegant residence is located next to ... Registration Number: TA-007-717-6192-01, GE-00
1 bedroom
1 bathroom
Sleeps 5
HI> Honolulu
MEMORABLE OCEAN, LAGOON VIEWS! STEPS TO WAIKIKI BEACH. Meals and fireworks from the Lanai. Walking distance to shopping mall, restaurants, shops, buses, rental cars, parks & Waikiki night life.
Beautiful remodeled condo. Fully equipped. AC, ... Registration Number: TA-047-601-1008-01
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
Panoramic OCEAN & SUNSET VIEWS! STEPS TO WAIKIKI BEACH. Memorable Meals from the Lanai. Walking distance to shopping mall, restaurants, shops, buses, rental cars, parks & Waikiki night life.
The iconic Ilikai Suites are located at the gateway ... Registration Number: TA-023-906-0992-01
1 bedroom
1 bathroom
Sleeps 4
HI> Waikiki
STUNNING OCEAN & Lagoon VIEWS! STEPS TO WAIKIKI BEACH. Memorable Meals and fireworks from the Lanai. , shops, buses, rental cars, parks & Waikiki night life.
Beautiful remodel. Fully equipped and very clean. AC, cable, WIFI, 24/7 Security, ... Registration Number: TA-200-936-2432-01
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
