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, rewritten_description,
(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, 50
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
- 1 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
- 2 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
- 1 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
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Welcome to Island Colony 715 - Your Charming Studio with Pool View!
Discover the allure of Waikiki at Island Colony 715, a delightful studio with a pool view. Located at 445 Seaside Ave #715, Honolulu, HI 96815, this private rental by
Registration Number: GE-063-187-5072-01, TA-063-187-5072-01, 1260210260019
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Waikiki Serenity in the Heart of Honolulu at Island Colony Apartment 1520
Welcome to your tropical retreat at Island Colony Apartment 1520, a 1-bedroom suite with a full kitchen and city view. Located in the heart of Waikiki at 445
Registration Number: TA-146-595-9424-01, 1260210260164000, GE-146-595-9424-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Fantastically located Waikiki studio with spacious lanai overlooking the ocean, Waikiki cityscape, and palm-tree dotted Ala Wai canal. As part of the luxurious Island Colony community, our guests indulge in the spacious sun deck, bbq area,
Registration Number: GE-161-842-1248-01, TA-161-842-1248-01, 260210260549
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
