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, 50
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
3 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
8 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
4 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
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
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
2 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
4 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
3 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
3 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
4 bedroom
2 bathroom
Sleeps 10
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
Escape the hustle & bustle of Waikiki crowds & enjoy beautiful sunrises, private beach, calm waters & experience amazing turtles sunbathing on the beach. Relax & experience a memorable vacation in our modern beach front condo w/ an Elegant ... Registration Number: 530080020028, GE-160-333-6704-01, TA-160-333-6704-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
30 day rentals. Contact host for details.Registration Number: 540030030003, GE-047-611-2384-01, TA-047-611-2384-01
6 bedroom
3 bathroom
Sleeps 12
Hawaii> Hauula
Stunning Ocean Views Condos in Oahu at Punaluu offers accommodations in Hauula, a 11-minute walk from Punalu'u Beach Park and 4.4 miles from Polynesian Cultural Center. This beachfront property offers access to a balcony and free Wifi. The condo... Registration Number: 390180030116, TA-053-048-2132-01, GE-053-048-2132-01, W92837736-09
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Hauula
Recently remodeled, Kalae Villa is an ideal gathering place for larger groups or those who want room to spread out. Enjoy easy access to a sandy beach. Centrally located for easy access to North Shore and Windward (East) Shore sights and ... Registration Number: 530010410000, GE-687-333-7415-01, TA-687-333-7415-01
5 bedroom
3 bathroom
Sleeps 14
Hawaii> Hauula
An ideal gathering place for larger groups or those who want room to spread out. Short walking distance to a sandy beach. Centrally located for easy access to North Shore and Windward (East) Shore sights and activities, within roughly 25 miles ... Registration Number: 530040440000, TA-441-268-9826-01, GE-441-268-9826-01
5 bedroom
3 bathroom
Sleeps 14
Hawaii> Hauula
This charming vacation home on the North Shore of Oahu offers the perfect blend of comfort and adventure. With air conditioning throughout, the 3-bedroom, 2-bath home provides a cool retreat after days spent enjoying the island. Located just ... Registration Number: 620290320000, TA-013-153-3035-01, GE-102-240-1014-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Hauula
Registration Number: GE-018-249-9328-01, TA-018-249-9328-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hauula
This home's 1700 square feet interior is newly remodeled and has 3 bedrooms, 2 bathrooms. Two bedrooms have queen size beds and one bedroom has 2 twin beds. The bedrooms and living rooms have ceiling fans or you can enjoy the cool ocean ... Registration Number: TA-052-251-8528-01, GE-052-251-8528-01, 540090220000
4 bedroom
2 bathroom
Sleeps 10
Hawaii> Hauula
This beautiful, beachfront cottage was recently remodeled in 2021 and converted from a 1-bedroom unit into a spacious, loft studio that can accommodate 4 people. The kitchen and bath feature upgrades that include new fixtures, appliances, marble... Registration Number: TA-184-219-6480-01, GE-184-219-6480-01, 1530080020136
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Take an escape to the peaceful side of the island . Park-like setting with beach on one side and beautiful green mountains on the other. Wake up to a beautiful sunrise or sleep in after a long day of activities, lots of hiking trails.Registration Number: TA-135-007-2832-01, 530080020027, GE-135-007-2832-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
If you are looking for Paradise, this is IT. The location of this Little Cottage is 30 feet out the front door to the most beautiful sandy beach. The snorkeling and fishing are exciting. There is a pool on the property, exercise room, Bar-B-Q... Registration Number: GE-191-932-4160-01, TA-191-932-4160-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hauula
Tucked away on the most beautiful side of the Island, our spacious, 700 square ft., and comfortable country hideaway is located 45 minutes from Honolulu/Waikiki. Surrounded by the ocean and the mountains, you will experience the timeless beauty ... Registration Number: TA-018-249-9328-01, GE-018-249-9328-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hauula
This house is all about being right on the water. A seawall provides protection from waves at high tide. A North Shore Beach House with the comforts of a nice home ideal for long term stays. Local pickle ball played right down the street. ... Registration Number: 530130340000, TA-191-655-3216-01, GE-191-655-3216-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hauula
You found it! A large 1,100 square foot, two bedroom, nicely upgraded beachside vacation rental that can sleep up to 8 guests and is fully stocked. The Beach Lane Hale (pronounced 'Ha-Lay') is a perfect place to relax, enjoy the beautiful beach ... Registration Number: TA-048-719-5648-01, GE-048-719-5648-01, 540030230000
3 bedroom
1 bathroom
Sleeps 8
Hawaii> Hauula
This cozy beach house is backed directly up to your own private beach walking trails beautiful palm trees surround you. Located within a short distance of the Polynesian Culture Center and BYU-Hawaii and downtown and great food and within a ... Registration Number: GE-178-528-1024-01, TA-178-528-1024-01, 300569448898
5 bedroom
3 bathroom
Sleeps 16
Hawaii> Hauula
BEACHFRONT* GROUND FLOOR *WALK-OUT Registration Number: GE-108-734-2080-01, TA-108-734-2080-01, 1530080020011
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Hauula
Bring the whole family to this great place with lots of room for fun.Registration Number: GE-177-878-8528-01, 540030030009, TA-177-878-8528-01
6 bedroom
3 bathroom
Sleeps 13
Hawaii> Hauula
Summary:Registration Number: GE-140-087-5520-01, TA-140-087-5520-01
4 bedroom
2 bathroom
Sleeps 14
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
8 bedroom
5 bathroom
Sleeps 25
Hawaii> Hauula
New remodeled kitchen and bath in this condo with an ocean view and located on a beautiful beachfront that has the best snorkel and swimming beach protected by a reef, with lawn like carpet and a Sandy beach where you can swim with turtles or ... Registration Number: 125300800200, GE-180-965-2224-01, TA-180-965-2224-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Welcome to this charming 3-bedroom home located in the beautiful coastal town of Hauula, Hawaii, just one block away from the sandy shores of the beach. This property offers a wonderful blend of island living and modern comforts, making it an ... Registration Number: 310330010000, TA-045-308-1052-01, GE-031-109-7131-01
4 bedroom
1 bathroom
Sleeps 9
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
This 5000 square foot spacious home with an open floor plan is excellent for families, friends, and co-workers to gather and enjoy each others company. It has 7 bedrooms, 5 bathrooms, A/C, and a private pool. Tropically landscaped grounds with a... Registration Number: TA-023-141-3760-01, 530150340000, GE-023-141-3760-01
8 bedroom
5 bathroom
Sleeps 25
Hawaii> Hauula
Registration Number: GE-056-023-8592-02, TA-056-023-8592-02
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
This 5 star charmer, 3 bedroom/3 bath North Shore Villa is a romantic oceanfront villa located between the beautiful Koo’lau Mountains and Hauula bay. Sights of a seemingly endless Pacific Ocean reaching towards a tranquil blue sky and 180 ... Registration Number: GE-009-149-2352-01, TA-009-149-2352-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Hauula
Pats at Punaluu. If you are looking for a quiet Oahu country beach, then drive an hour from Honolulu airport through spectacular mountains and ocean views to the small town of Punaluu along the country coast of Oahu. Beach Cottage is located ... Registration Number: TA-143-890-4320-01, GE-143-890-4320-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hauula
This beach cottage is one of a kind. Please contact vrbo or owner for dates you are interested in if you don’t see availability. This cottage is so calming to just sit there and watch the ocean which is only steps away. Turtles frequently ... Registration Number: 530080020128, TA-007-640-8832-01, GE-007-640-8832-01
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
This remodeled cozy cottage sits on a private white sand, reef-protected beach. Great for snorkeling and safe for kids! You'll enjoy swimming with turtles an a variate of colorful fish! Just sit on the serene sandy beach, enjoy the lush ... Registration Number: 530080020055, GE-136-395-0080-01, TA-136-395-0080-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
The beautiful beachfront North shore condo with AC is the perfect place for a tropical family getaway. With the beach seconds away and the tropical beauty of the mountains/greenery, it is a pictorious view. Laie, the Polynesian Cultural Center, ... Registration Number: 530080020041, GE-114-389-6576-01, TA-114-389-6576-01
2 bedroom
1 bathroom
Sleeps 8
Hawaii> Hauula
This RARE CORNER Breezy OCEAN/SAND BEACHFRONT studio promises an unforgettable Hawaiian retreat - the perfect way to immerse yourself in tropical bliss.Registration Number: TA-210-067-6096-01, GE-210-067-6096-01, 530080010063
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hauula
Experience the best of Hawaii at our oceanfront one bedroom condo in beautiful, a wonderful place to spend time relaxing and becoming refreshed. Registration Number: TA-053-711-8720-01, GE-053-711-8720-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
