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 '%Marine Surf Waikiki%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Want to be steps from Waikiki beach, fitness venues, and shopping, while having the advantage of a well-equipped kitchen for healthy meals? We offer fully renovated, upscale studio with all the conveniences of home. Fitness and ...
Registration Number: GE-130-575-5648-01, TA-130-575-5648-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
If you want to be steps from Waikiki Beach and have the convenience of a well-equipped kitchen for healthy meals, and FREE parking, then stay in our sleek, upscale studio.
We are located near the Royal Hawaiian and close to the beach, ...
Registration Number: TA-130-575-5648-01, GE-130-575-5648-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
The Aloha Hula!! Marine Surf Waikiki!(MS02). Feel Hawaiian Air inside the Room. Enjoy the Hula theme room with the great price!!
The room rate is less than a half of other Central Waikiki Hotels!! Also a full kitchen and a free parking is ...
Registration Number: TA-183-153-5616-01, 260190010110, GE-183-153-5616-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Marine Surf Waikiki Studio is a spacious open concept which features a full kitchen, lanai balcony, 2 full size beds and a dining table for 4. Bathroom is a tub/shower combination very clean and with good water pressure! Parking can be $40 per ...
Registration Number: TA-110-812-7744-02, GE-110-812-7744-02, 260190010070
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
The cheapest vacation rental rooms in Marine Surf Waikiki!!
The room rate can be less than a half of other Marin Surf rooms. And less than a third of other hotels in Central Waikiki!!
This room is not like hotels, we have the full kitchen ...
Registration Number: TA-128-248-2176-01, GE-128-248-2176-01, 260190010105
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Marine Surf Waikiki Studio is a spacious open concept which features a full kitchen, lanai balcony, 2 full size beds and a dining table for 4.
Bathroom is a tub/shower combination very clean and with good water pressure!
Parking can be $40...
Registration Number: GE-146-035-9168-01, TA-146-035-9168-01, 260190010149
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Summary:
((( Parking Included )))
Marine Surf Waikiki is truly in the "heart of Waikiki"....
This spacious studio has all the comfort of a vacation rental offering....
Comfortable beds (2 full size)...
Full Kitchen....
A lanai ...
Registration Number: GE-146-035-9168-01, TA-146-035-9168-01, 260190010212
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
