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 = 'hauula' OR postalCode = 'hauula' OR region like '%|hauula|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Old Hawaii at its best! Enjoy the updated traditional home nestled within a fully enclosed and private one-acre tropical estate! Chase the keiki (kids) or grandkids on the spacious lawns, or just enjoy the many tropical trees and ... Registration Number: TA-098-825-1136-01, 1530150070000, GE-098-825-1136-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Hauula
This two bedroom, one bath country cottage on Oahu’s scenic North Shore with a full kitchen and family room is perfect for any family desiring a gorgeous and secluded home base for either a relaxing or exciting tropical vacation--or both! The... Registration Number: TA-098-825-1136-01, 1530150070000, GE-098-825-1136-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Hauula
Contact Host: We may have your dates available! Registration Number: GE-042-619-8016-01, 530213660000, TA-042-619-8016-01
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Hauula
Location: Hauula, North Oahu, Oahu, Hawaii, USA (LOCATED TWO MILES EAST OF THE POLYNESIAN CULTURAL CENTRE) Registration Number: TA-169-887-5392-01, GE-169-887-5392-03, 1540090060000
7 bedroom
3 bathroom
Sleeps 18
Hawaii> hauula
[A legal rental of minimum 30 days]Registration Number: TA-063-051-2128-01, 000054009028, GE-063-051-2128-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Hauula
Come relax at this sandy beachfront home in Hau'ula. This listing is for renting both the Top and Bottom Units together. Please note that there's a third, 2 tenant-occupied unit onsite and absolutely no unregistered guests are allowed on the ... Registration Number: GE-212-701-5936-01, TA-212-701-5936-01
4 bedroom
4 bathroom
Sleeps 9
Hawaii> Hauula
Ocean front home on the popular North Shore. Perfect for snowbirds, AARP members, retired people, International travelers, or just anyone who can escape for a lengthier time period. Enjoy the breathtaking oceanview from the living room. Lay out ... Registration Number: GE-208-798-1056-11, TA-208-798-1056-11
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Hauula
Located on the countryside of Oahu between the ocean & the mountains, you will be in the perfect spot to experience what Oahu has to offer.Registration Number: 1540020200000, TA-185-358-7456-01, GE-185-358-7456-01
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Hauula
Enjoy the beautiful newly renovated Hale Nalu Hawaii unit. This spacious 1-bedroom is over 1,100 square feet of living area with a large kitchen/dining room, spacious bedroom and central room for movies and lounging. We're less than a 1 minute ... Registration Number: TA-178-438-3488-01, GE-178-438-3488-01
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Hauula
Enjoy oceanfront living! Wake to the sun rising over the ocean, feel the sand between your toes and wade in the warm waters as you walk on the sandy beach just steps from the condo. Watch the waves break on the offshore reef.Registration Number: GE-029-106-6880-01, TA-029-106-6880-01, 1530080010076
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Ultimate tropical escape into your own paradise. This one-of-a-kind Beachfront House on the northeast shore of Oahu is wonderful for swimming, extensive beach walks, or just sitting on the porch watching the sun rise right in front of the ... Registration Number: GE-162-525-8496-01, TA-162-525-8496-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hauula
This is a modern comfy oceanfront North Shore home w/ AC located near Hauula Bay, Kualoa Ranch, and Polynesian Cultural Center.Registration Number: TA-027-178-1888-01, GE-027-178-1888-01, 530020510000
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Hauula
In the heart of Punalu'u you will find a gorgeous 2 bedroom 1 bath home fully gated with AC in bedrooms and steps from a white sandy beach. There are views of the ocean and of the Koolau mountain range. This secluded property has an outdoor ... Registration Number: TA-108-394-0864-01, GE-108-394-0864-01, 1530050700008
2 bedroom
1 bathroom
Sleeps 7
Hawaii> Hauula
Our Seaside Property is a gem on the east side of Oahu that sits just a block away from the warm blue tropical water of Hawaii. This property is away from the busy and noisy Waikiki tourist area. You'll live among the locals to experience the ... Registration Number: TA-204-742-7787-01, GE-802-351-8791-01, 530051020000
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Hauula
Escape to paradise with this beautiful 2 bedroom, 2 bath unit on Oahu's stunning Windward North Shore. Located on a quiet street near beach access to a gorgeous sandy beach, this newly renovated unit offers the ultimate Hawaiian experience! ... Registration Number: 540030030000, GE-031-606-9221-01, TA-031-606-9221-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Hauula
Stay home and just enjoy the beach in your front yard or the nice quiet Koolau mountain range in your backyard. Or head out near by and enjoy some Oahu activities; Polynesian Cultural center, Kualoa Ranch, horse back riding, North Shore, ... Registration Number: TA-018-423-1424-01, GE-018-423-1424-01, 53005036
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Hauula
Relax with the whole family at this peaceful place to stay. Right across the beach. Stroll in the morning or a nice afternoon walk. Catch the sunrise or see the sky light up from the sunset. Hear the crash of the waves as you fall asleep. Surf, ... Registration Number: TA-018-423-1424-01, GE-018-423-1424-01, 53005036
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Hauula
Welcome to Mau Loa Hale, a private island-style home nestled between the Ko'olau Mountains and the sparkling Pacific Ocean, offering the best of both worlds. Situated on 5 acres of land, this magnificent property provides ample space to relax ... Registration Number: TA-137-393-1520-01, GE-137-393-1520-01, 530090750001
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Hauula
Oceanside Estate offers 7 bedrooms, pool and large yard. This peaceful country home is just a couple minutes from an amazing beach. Perfect for large families who want to stay together but still have their own space.Registration Number: 615091610000, GE-071-386-1503-01, TA-057-319-2683-01
7 bedroom
5 bathroom
Sleeps 25
Hawaii> Hauula
Seabreeze is a beautiful North Shore Hau'ula beach home with 110' of ocean frontage, spectacular sunrises & majestic mountain views. Dine on the lanai with turquoise ocean water so close you can almost touch it. Our home overlooks the sea & ... Registration Number: TA-199-128-8320-01, 540120440000, GE-199-128-8320-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hauula
