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 (city = 'waimanalo' OR postalCode = 'waimanalo' OR region like '%|waimanalo|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Make unforgettable memories in this secluded, polynesian-styled beachfront estate situated on the eastern side of Oahu next door to the famous Magnum PI Robin's nest. Flanked by the majestic Ko’olau mountains on one side and a reef-protected ... Registration Number: GE-082-372-8128-01, TA-082-372-8128-01, 410020090000
3 bedroom
5 bathroom
Sleeps 8
Hawaii> Waimanalo
"Hale Pohaku" - Waimanalo is a beautiful beachfront home on one of Oahu's most spectacular beaches; Waimanalo. Listed on the Register of Hawaii's Historic Homes this classic restored beach house was built of lava rock during the reign of King ... Registration Number: TA-059-395-6864-01, 410040420000, GE-059-395-6864-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Waimanalo
This rental is across the street from one of the nicest beaches on the island. Don't take our word for it check out the pictures of Waimanalo Beach and Bellows Beach. Registration Number: TA-002-351-5136-01, GE-002-351-5136-01, 410070130000
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
We are literally oceanfront! Registration Number: GE-174-700-7488-01, TA-174-700-7488-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimanalo
“Notice: In compliance with the Revised Ordinances of the City and County of Honolulu, the Property will be made available for your use and occupancy for a period of thirty (30) days.” Registration Number: GE-063-319-9104-01, TA-063-319-9104-01, 141002002000
9 bedroom
8 bathroom
Sleeps 20
Hawaii> Waimanalo
This home includes ocean views, a rooftop deck, and a heated pool. Enjoy breathtaking views of the majestic lava-formed Ko'olau mountain range and only steps to the # 1 rated beach In the world (2015), Waimanalo Beach, famous for the filming of ... Registration Number: TA-106-722-5088-01, 410060570000, GE-106-722-5088-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Waimanalo
Note:Registration Number: TA-111-933-9520-01, GE-111-933-9520-01, 1410060400000
7 bedroom
4 bathroom
Sleeps 10
Hawaii> Waimanalo
Nestled in Waimanalo, this 2-bedroom, 1-bathroom vacation home brings it all within your reach. Fans of nature and the outdoors will love being within a 5-minute walk of Waimanalo Beach and 8 minutes by car from Bellows Field Beach Park. If ... Registration Number: 410050460000, TA-076-575-7440-01, GE-076-575-7440-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
Commonly known as the Paniolo countryside, you could pass right through the small town of rural Waimanalo on the southeastern corner of O’ahu and never realize what you missed. Nestled in this quaint village is a hidden gem, Hale Waimanalo, ... Registration Number: 410060870000, TA-214-325-5040-01, GE-214-325-5040-01
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Waimanalo
Right across the street from one of Oahu's most beautiful and uncrowded beaches voted # 1 in the USA.Registration Number: TA-130-535-0144-01, GE-130-535-0144-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waimanalo
Uncover the allure of Waimanalo and lose yourself in the tropical paradise of Waimanalo Beach, where our exquisite cottages and breathtaking surroundings await. Embark on a journey of discovery by indulging in the myriad of exciting activities ... Registration Number: TA-576-480-6660-03, W45054461-01, 410070650000
9 bedroom
9 bathroom
Sleeps 24
Hawaii> Waimanalo
Aloha, this Premier Partner property is exclusively available for periods of thirty (30)-days or more. Questions, please contact us for the dates you are interested in and we will respond wiki-wiki (fast !).Registration Number: TA-120-590-3360-01, GE-120-590-3360-01, 410700023000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waimanalo
The Beach Haven is a beautiful home that can make your vacation very special. Only steps from one of the best beaches in Hawaii, you will feel the ocean breeze surround you. The home is treetop level which provides views of the ocean and of the ... Registration Number: 410050460000, TA-076-575-7440-01, GE-076-575-7440-01
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimanalo
300 feet from the Exquisite Waimanalo Beach. Fully furnished 3 bedroom, 2.5 bath home with everything you need for an ideal Hawaiian vacation! We are delighted to be able to continue to provide a truly Aloha spirit inspired vacation to new ... Registration Number: 411234567892, GE-142-816-0512-01, TA-142-816-0512-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimanalo
Minimum 30 day or longer booking only. Newly renovated with full, quiet, split air conditioning. Nestled directly across from one of the most beautiful white sand beaches on Oahu, Paradise Found Beachside Suite offers peace, tranquility and ... Registration Number: GE-203-731-1488-01, 410060130000, TA-203-731-1488-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waimanalo
Aloha, this unique Premier Partner property is exclusively available for periods of thirty (30) days or more. Questions, please contact us directly for the dates you are interested in and we will respond wiki-wiki (fast !)Registration Number: TA-120-590-3360-01, GE-120-590-3360-01, 410700023000
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Waimanalo
WE ARE LITERALLY ON THE WATER! Registration Number: GE-174-700-7488-01, TA-174-700-7488-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimanalo
This spectacular property is located beach front and has the most amazing ocean views, lots of room to enjoy this pristine beach environment and an ultimate decor with a french touch. All the bedrooms enjoy a private, en-suite bath and all have ... Registration Number: TA-199-621-0176-01, GE-199-621-0176-01
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Waimanalo
Located in the Waimanalo Beach Lots, which is only 25 minutes from Honolulu and Waikiki. Waimanalo Beach is one of the largest, cleanest, and most private beaches on Oahu. This white sand beach is very safe (referring to the ocean/surf) so you... Registration Number: 410070800000, TA-002-351-5136-01, GE-002-351-5136-01
2 bedroom
2 bathroom
Sleeps 9
Hawaii> Waimanalo
Charming cottage in a very quiet neighborhood next to the Ko'olau mountains. The lush flora are encapsulated in yard where you can sit back and enjoy the Sea air. There are bicycles you can ride to the small bodega store to get any necessities... Registration Number: 410070780000, TA-173-277-0304-01, GE-173-277-0304-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimanalo
