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 (city = 'waimanalo' OR postalCode = 'waimanalo' OR region like '%|waimanalo|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
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
2 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
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. Registration Number: 410050460000, TA-076-575-7440-01, GE-076-575-7440-01
3 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
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
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
2 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
3 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
This incredibly located and luxurious ocean-view Hawaiian beach cottage is just steps to 2015's # 1 rated beach in the USA, Waimanalo Beach. In a quiet Hawaiian village, you'll awaken to the sounds of birds, the fragrance of flowers, and waves Registration Number: TA-106-722-5088-01, 410060570000, GE-106-722-5088-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
This property is offered as a monthly rental. Although full-month occupancy is not required, the monthly rate remains fixed and applies in full, regardless of the number of nights stayed, as prorated rates are not available.Registration Number: 410020180000, GE-164-021-9648-01, TA-164-021-9648-01
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Waimanalo
Welcome to our tropical home, a bright and breezy retreat just one block from Oahu's most beautiful beach: four miles of soft sand and gentle waves await you! From the house and patio, you’ll enjoy breathtaking sunset views over the Ko’olau Registration Number: TA-125-245-1840-01, 410070830000, GE-125-245-1840-01
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Waimanalo
