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: TA-098-033-4592-01, 530130240000, GE-098-033-4592-01
4 bedroom
3 bathroom
Sleeps 6
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 4
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
Summary:Registration Number: GE-161-910-1184-01, TA-161-910-1184-01, 530080020073
2 bedroom
1 bathroom
Sleeps 8
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
Please reach out to us prior to booking.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
Please reach out to us prior to booking.Registration Number: 620290320000, TA-013-153-3035-01, GE-102-240-1014-01
3 bedroom
2 bathroom
Sleeps 8
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
Registration Number: GE-018-249-9328-01, TA-018-249-9328-01
3 bedroom
2 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
5 bedroom
3 bathroom
Sleeps 10
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-123-456-7890-01, W1234
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
Welcome to our tranquil three-bedroom vacation retreat nestled in the heart of nature's paradise. Each bedroom boasts a comfortable queen bed, ensuring a restful night's sleep for everyone. The master bedroom features a convenient in-suite full ... Registration Number: 540150290000, GE-117-099-6598-01, TA-117-099-6598-01
4 bedroom
2 bathroom
Sleeps 8
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
Immaculate, newly renovated, ocean front condo- relaxing retreat for couples!Registration Number: GE-166-650-6752-01, TA-166-650-6752-01, 005003008002
2 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
Great for a romantic getaway, this Beautiful One Bedroom Beachfront End Unit does nothing but amaze anyone who stays here. Wake up early, walk out onto the wrap around lanai and see in front of you the dramatic pinks, oranges and yellows of the ... Registration Number: GE-166-650-6752-01, TA-166-650-6752-01, 005003008002
2 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
The property is located just steps from the beautiful ocean in an newly remodeled home in a 1st floor private unit roughly 900 sq ft with full kitchen with large spacious countertops with stainless steel appliances, refrigerator, viking cooktop,... Registration Number: GE-074-164-8896-01, TA-074-164-8896-01
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Hauula
Summary:Registration Number: GE-178-528-1024-01, TA-178-528-1024-01, 300569448898
6 bedroom
2 bathroom
Sleeps 16
Hawaii> Hauula
This is the most perfect white sandy beach and welcoming turquoise water on the North Shore and perhaps the whole island of Oahu, where you can snorkel all day protected by the reef. You can float for hours, stand up paddle or kayak. Lounge ... Registration Number: TA-129-587-4560-01, GE-129-587-4560-01, 153008002009
2 bedroom
1 bathroom
Sleeps 4
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
Experience the charm of Aloha Studio, nestled in a serene North Shore neighborhood. This cozy retreat features a queen bed and an air mattress for extra sleeping space, accommodating 2-3 guests comfortably.Registration Number: TA-080-614-5682-01, GE-080-614-8954-01, 560040280000
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hauula
Please reach out to us prior to booking.Registration Number: TA-003-640-6800-01, 280090160000, GE-001-450-2900-01
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Hauula
Please reach out to us prior to booking.Registration Number: 310330010000, TA-045-308-1052-01, GE-031-109-7131-01
4 bedroom
1 bathroom
Sleeps 9
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
Oceanfront property is located steps from the Ocean in an upstairs large spacious private apartment with private entrance roughly 800 sq ft with breathtaking views of the Ocean, Mountains, Sea Turtles and other marine life out the large windows ... Registration Number: GE-074-164-8896-01, TA-074-164-8896-01
1 bedroom
1 bathroom
Sleeps 2
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
DescriptionRegistration Number: TA-080-614-8096-01, 540040190000, GE-080-614-8096-01
4 bedroom
2 bathroom
Sleeps 10
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
