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 '%island colony%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Renovated condo in 16th floor!
As you enter, you will find a fully equipped kitchen. In the condo there are two twin size beds for your full recharge. Enjoy the weather protected lanai for morning or evening hangouts.
The 6th floor...
Registration Number: 1260210260178, GE-013-474-3552-01, TA-013-474-3552-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The location is perfect! Walk to the Royal Hawaiian Shopping Center in about 5 minutes and the newly renovated International Marketplace in about 5-7 ...
Registration Number: GE-162-212-4032-01, TA-162-212-4032-01, 260210260166
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honoluu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 42nd Floor
LIVING AREA (308 sq.ft not including lanai)
- 1 Full Bed & 1 Sofa Bed
- Flat...
Registration Number: TA-131-191-4496-01, GE-131-191-4496-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Property Management provided by Alohana Realty LLC. If the property you selected to book is unavailable or if you have further questions please feel free to contact us before booking online.
**THIS IS A LEGAL VACATION RENTAL**Island ...
Registration Number: GE-148-674-7648-01, TA-148-674-7648-01, 260210260063
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
View: Partial Ocean, Mountain, City, Canal, and Rainbow
Maximum Number of Guests: 4
Minimum Length of Stay: 2 Nights
Parking: No (There is condominium parking, but guests must check the availability and pay the condo directly. There ...
Registration Number: 126021026558, TA-156-024-6784-01, GE-156-024-6784-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 30th Floor.
LIVING AREA (424 sq.ft not including lanai)
- 1 King & 1 Single Sofa ...
Registration Number: 260210260452, TA-133-349-5808-01, GE-133-349-5808-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 38th Floor.
LIVING AREA (316 sq.ft not including lanai)
- 1 Queen Bed & 1 Single Sofa ...
Registration Number: TA-133-349-5808-01, GE-133-349-5808-01, 260210260618
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Spacious 21st floor One Bedroom condo with full kitchen, AC, lanai, king size bed and 2 sleeper sofa. Located within walking distance to the shopping centers, beach activities.
The first step into beautifully arranged living room with the ...
Registration Number: GE-060-827-0848-01, 1260210260285, TA-060-827-0848-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Property Management provided by Alohana Realty LLC. If the property you selected to book is unavailable or if you have further questions please feel free to contact us before booking online.
**THIS IS A LEGAL VACATION RENTAL**
Studio ...
Registration Number: TA-107-068-1088-01, 1260210260056, GE-107-068-1088-01
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 12th Floor.
Living Room/Bedroom Area (570 sq.ft not including lanai)
- 1 Queen Bed and 1 ...
Registration Number: 260210260125, TA-015-818-9056-01, GE-015-818-9056-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Hon
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 39th Floor.
LIVING AREA (316 sq.ft not including lanai)
- 2 Full Beds
- Dining Table ...
Registration Number: GE-111-456-0512-01, TA-111-456-0512-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Bright and simple 29th floor 1 Bedroom, Center of Waikiki with kitchen, a queen size bed and two sleepers and a balcony overviewing city, mountains and partially the ocean is located close to dining, shopping and beach activities!
As you ...
Registration Number: GE-119-726-1824-01, TA-119-726-1824-01, 260210260427
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 16th Floor.
Living Area (313 sq.ft not including lanai)
- 1 Queen Bed
- Dining table ...
Registration Number: GE-186-426-8288-01, TA-186-426-8288-01, 186426828801
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 34th Floor.
LIVING AREA (302 sq.ft not including lanai)
- 2 Full Beds
- 1 Work ...
Registration Number: TA-161-178-6752-01, GE-161-178-6752-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
High floor, Newly furnished Condo at Island Colony IC3711
Island Colony is a few steps away from the building is a large concentration of shopping centers, such as Waikiki Shopping Plaza, BeachWalk, and the International ...
Registration Number: TA-083-027-4048-01, GE-083-027-4048-01, 1260210260596
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
A 33rd-floor studio unit with breathtaking mountain, canal, city views. Within walking distance to the famous Waikiki Beach.
This unit is conveniently located in the high-rise Island Colony building in central Waikiki, within walking ...
Registration Number: GE-163-690-3424-04, 260210260522, TA-163-690-3424-02
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
The Island Colony located at 445 Seaside Ave on the 9th Floor.
Living Area (313 sq.ft not including lanai)
- 1 Queen Bed
- Dining table for...
Registration Number: TA-069-200-4352-01, GE-069-200-4352-01, 260210260058
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Property Management provided by Alohana Realty LLC. If the property you selected to book is unavailable or if you have further questions please feel free to contact us before booking online.
**THIS IS A LEGAL VACATION RENTAL**
Studio ...
Registration Number: 1260210260035, GE-175-928-5760-02, TA-175-928-5760-02
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
