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, 30
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
This home SLEEPS A MAXIMUM OF 6 PEOPLE, including all children.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
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
This property is a legally established dwelling that is rented for periods of 30 days or more at any one time. Contact us for more details. Registration Number: TA-208-472-4736-01, GE-208-472-4736-01, 530130120000
3 bedroom
2 bathroom
Sleeps 8
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
Summary:Registration Number: TA-161-910-1184-01, 530080020073, GE-161-910-1184-01
1 bedroom
1 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
**This property may not be rented for less than 30 consecutive days. Rental prices will not be reduced or adjusted based on the number of days the rental is actually used or occupied**Registration Number: 530080280000, GE-146-422-3232-01, TA-146-422-3232-01
4 bedroom
4 bathroom
Sleeps 12
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
Summary:Registration Number: GE-019-698-6369-10, 530080020077, TA-019-698-6369-10
1 bedroom
1 bathroom
Sleeps 7
Hawaii> Hauula
When you stay at the Blue Surf Paradise, you have direct access to the beach and are close to great attractions in the North Shore. It's about a mile to the Polynesian Cultural Center and many beautiful beaches. This well appointed home is close... Registration Number: 380360410004, TA-203-312-8864-01, GE-300-210-4812-01
4 bedroom
3 bathroom
Sleeps 9
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
Welcome to our charming 3-bedroom, 2-bath vacation rental home in the picturesque town of Hauula, Hawaii. Nestled in the heart of this tropical paradise, our property offers the perfect blend of relaxation, convenience, and Hawaiian ... Registration Number: TA-013-359-1231-01, GE-043-379-4132-01, 850054130000
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Hauula
Mai Tide is a beautiful North Shore Hau'ula beach guest home with 30'+ of ocean frontage, spectacular sunrises & majestic mountain views. Dine on the lanai with turquoise ocean waters 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
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hauula
The Space:Registration Number: GE-083-242-8032-01, TA-083-242-8032-01, 570010130001
4 bedroom
3 bathroom
Sleeps 14
Hawaii> Hauula
Enjoy a slower pace in our family-friendly home, conveniently located only eight houses from the ocean in a local neighborhood close to Laie. We invite you to relax in our comfortable beds and huge cozy couch, or recline on the lanai chaise ... Registration Number: 540120730000, GE-210-095-3088-01, TA-210-095-3088-01
3 bedroom
2 bathroom
Sleeps 9
Hawaii> Hauula
For the traveller seeking a more authentic experience of Hawai`i is Hale Makani Kai, "home of the gentle ocean breeze." Ideally located for experiencing the unique beauty and culture of true Hawai`i, this home is found on the "beautiful side of ... Registration Number: GE-018-249-9328-01, TA-018-249-9328-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hauula
Sandy beachfront home shows extreme attention to detail inside and out, immaculately cleaned and maintained. This listing is for renting both the Top and Bottom Units together. Please note that there's a third, tenant-occupied unit onsite. They ... Registration Number: GE-212-701-5936-01, TA-212-701-5936-01
3 bedroom
4 bathroom
Sleeps 9
Hawaii> Hauula
Enjoy ocean breezes, spectacular unobstructed ocean views while listening to the waves from this Beach Front 1BR/1BA 750 sq.ft. beachfront condo (including a private lanai with ocean view.)Registration Number: 530080020058, GE-118-364-0064-02, TA-118-364-0064-02
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Hauula
*PER COUNTY REGULATION THIS PROPERTY ONLY ACCEPTS 1 BOOKING PER 30 DAYSRegistration Number: TA-054-655-7952-01, GE-054-655-7952-02
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Hauula
