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, rewritten_description,
(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
[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
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
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
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
Summary:Registration Number: TA-002-506-5984-01, 530130260000, GE-002-506-5984-01
6 bedroom
5 bathroom
Sleeps 12
Hawaii> Hauula
Important Notice: This property is available for rental with a minimum stay of 30 consecutive days. Rental prices remain fixed and are not subject to reduction based on the length of stay.Registration Number: 1530080280000, TA-146-422-3232-01, GE-146-422-3232-01
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Hauula
Summary:Registration Number: GE-211-142-4000-01, TA-211-142-4000-01, 530140110002
3 bedroom
1 bathroom
Sleeps 8
Hawaii> Hauula
Work from Paradise or Enjoy Family Time by the BeachRegistration Number: 530080010062, TA-127-871-1296-01, GE-127-871-1296-01
1 bedroom
1 bathroom
Sleeps 5
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
Please send us an inquiry if you have any questions.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
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
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
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
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
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 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
Here is the adorable beach bungalow you've been looking for. Located on the southern border of Laie on the northeast shore of Oahu, this old-time beach cottage was lovingly restored back to original. The 425 square foot private cottage has a Registration Number: TA-048-719-5648-01, GE-048-719-5648-01, 540030230000
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hauula
This 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 mountains, and 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
You will discover the true Hawaii as you stay in this 3-bedroom, 1-bath Hawaiian home in Hauula. This newly-renovated bottom floor unit (the caretaker lives upstairs) getaway that sleeps 8-9 guests and is located about 5 minutes away from Registration Number: GE-005-205-9504-01, TA-005-205-9504-01, 680110490000
4 bedroom
1 bathroom
Sleeps 9
Hawaii> Hauula
- Heavenly Hauula - This serene 2 story beachfront home offers a taste of old Hawaii's plantation style living with all the modern conveniences. After entering the small lane, you'll find Heavenly Hauula tucked away behind a private gate. Registration Number: 540030490000, GE-096-036-7616-01, TA-096-036-7616-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Hauula
*** IN ACCORDANCE WITH LOCAL LAWS we do 1-rental per 30 days, although guest does not have to stay 30 days. **** inquire to host for information.Registration Number: GE-321-654-0987-01, TA-321-654-0987-01, 530080140000
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Hauula
Aloha House has it all! In a nice local neighborhood, you will get the real Hawaii experience. Enjoy the proximity to great beaches and restaurants. Registration Number: TA-080-614-5682-01, GE-080-614-8954-01, 560040280000
3 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
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
Our Hawaiian Paradise Vacation awaits you here in our uniquely remodeled Hale. Be a part of our Ohana and escape to the Hawaiian countryside, leaving behind the hustle and bustle of Waikiki with its crowded beaches and enjoy a private serene Registration Number: 530080020028, TA-182-961-9712-01, GE-182-961-9712-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Newly remodeled unit just made available for rentals. This was our home in the 70's and now our vacation home. We want you to enjoy old Hawaii the way we did when we were first married. This is a quiet get away from the hustle of Waikiki but Registration Number: 530080020047, TA-116-453-7856-01, GE-116-453-7856-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
*30 day rental. Please inquire. Registration Number: 540050150000, GE-053-056-1536-01, TA-053-056-1536-01
3 bedroom
1 bathroom
Sleeps 7
Hawaii> Hauula
*MINIMUM 30 DAYS STAY REQUIRED* Charming apartment with full kitchen, queen bed, and second (futon) bed in living room. Gorgeous panoramic Pacific Ocean views from the lanai! Enjoy all that beautiful Oahu has to offer without all the crowds. Registration Number: TA-022-548-6336-01, GE-022-548-6336-01, 153008002606
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Beautiful ocean views surround you in this romantic cottage on a secluded, white, sandy beach. Relax in comfort and listen to the waves gently breaking on a distant reef. Sunrises and moonrises on the horizon will surprise and amaze you. This Registration Number: TA-184-219-6480-01, GE-184-219-6480-01, 1530080020125
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Escape the crowed city, and come enjoy some time in the country. Relax in our updated island style condo in the iconic Pat's at Punaluu beachfront residence. This tastefully decorated unit has been fully outfitted with most everything you could Registration Number: TA-182-961-9712-01, GE-182-961-9712-01, 530080020068
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Hauula
This Unique and private beachfront condo with turtles everywhere and its a perfect swimming beach. Come enjoy the newly remodeled oceanview unit that has a wonderful ocean crossbreeze from the trade winds and air conditioning also now we are Registration Number: 125300800, GE-180-965-2224-01, TA-180-965-2224-01
2 bedroom
1 bathroom
Sleeps 4
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
