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
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: GE-162-212-4032-01, TA-162-212-4032-01, 260210260166
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honoluu
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
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: TA-131-191-4496-01, GE-131-191-4496-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
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
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.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: TA-193-880-5248-01, GE-193-880-5248-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: 260210260452, TA-133-349-5808-01, GE-133-349-5808-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**
Studio ...
Registration Number: TA-107-068-1088-01, 1260210260056, GE-107-068-1088-01
- 0 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: 1260210260035, GE-175-928-5760-02, TA-175-928-5760-02
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
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.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
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.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
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.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: GE-058-580-3264-01, 260210260412, TA-058-580-3264-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: TA-176-608-7168-01, GE-176-608-7168-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Hon
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: TA-161-178-6752-01, GE-161-178-6752-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
BUILDING NOTICE: Temporary Pool/Jacuzzi Closure. Annual preventative maintenance, cleaning & repairs to the 6th floor Swimming pool, Jacuzzi & Pool Deck...
Registration Number: TA-126-272-9728-01, GE-126-272-9728-01, 260210260283
- 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
