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, 20
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
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
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
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
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
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
Summary:
Experience the perfect blend of style and comfort in this charming one-bedroom unit located in the heart of Island Colony, Hawaii. This thoughtfully designed apartment features a complete kitchen, providing everything you need for a ...
Registration Number: 1260210260506, GE-144-283-3920-01, TA-144-283-3920-01
- 1 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Summary:
This luxuriously appointed condo effortlessly combines modern amenities with a tropical ambience, making it ideal for families, business trips or a wonderful Waikiki getaway. Fully renovated in June 2022, this abode offers an ...
Registration Number: TA-192-823-9616-02, 1260210260396, GE-192-823-9616-02
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
