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, 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
Note:Registration Number: TA-111-933-9520-01, GE-111-933-9520-01, 1410060400000
8 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
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
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
3 bedroom
2 bathroom
Sleeps 4
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
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
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
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
Our property includes a 4 bedroom main house as well as a 2 bedroom cottage that sleeps up to 14 guests comfortably. The property includes ocean views, a rooftop deck, a heated pool, and a hot tub. You will enjoy breathtaking views of the ... Registration Number: TA-106-722-5088-01, 410060570000, GE-106-722-5088-01
6 bedroom
4 bathroom
Sleeps 14
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
Note:Registration Number: TA-111-933-9520-01, GE-111-933-9520-01, 1410060400000
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimanalo
Take a journey back in time at Hawaii Hobbit House. This cozy, yet abundant luxury villa is shrouded in charm as well as amenities for your whole party to enjoy. Equipped with 3 bedrooms, 3 full bathrooms and accommodating 12 people, you and ... Registration Number: TA-030-510-1312-01, 1410070640000, GE-030-510-1312-01
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Waimanalo
Note:Registration Number: TA-111-933-9520-01, GE-111-933-9520-01, 1410060400000
5 bedroom
3 bathroom
Sleeps 8
Hawaii> Waimanalo
Our cottage, in the Waimānalo Beach Lots, is available starting early April.Registration Number: TA-000-786-0736-01, GE-000-786-0736-01, 41006059
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
Welcome to Mana Kai at Waimanalo by Gather, your ultimate beachfront escape nestled along the pristine shores of Hawaii’s East Coast. Offering an unforgettable retreat with four spacious bedrooms, this coastal oasis is perfect for families or ... 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
