select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 and (registration_number like 'TA-%') AND (city = 'hauula' OR postalCode = 'hauula' OR region like '%|hauula|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This is the story of a little beach house doing its part in preserving a sanctuary on this stretch of sand for the endangered Hawaiian monk seals. For many years the beach in front of the house was known as the home of a famously beloved seal ... Registration Number: TA-063-051-2128-01, GE-06
3 bedroom
3 bathroom
Sleeps 8
HI> 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, 54009
3 bedroom
2 bathroom
Sleeps 10
HI> 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-17
1 bedroom
1 bathroom
Sleeps 5
HI> 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.
You don't ... Registration Number: TA-135-007-2832-01, 53008
1 bedroom
1 bathroom
Sleeps 4
HI> 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.
Close to world famous North Shore, Turtle Bay Resort, popular surf breaks, snorkeling spots, sea turtles,... Registration Number: TA-185-358-7456-01, 15400
3 bedroom
2 bathroom
Sleeps 6
HI> Hauula
Located off the beaten path on East Oahu, and tucked snugly between the Ko'olau mountains and the sleepy shores of Punalu'u, this boutique style home offers a charming three bedroom house, where you and yours can relax in style and comfort.
The... Registration Number: TA-117-066-9568-01, GE-11
3 bedroom
2 bathroom
Sleeps 8
HI> Hauula
“Our Piece Of Paradise" is a BEACHFRONT, GROUND FLOOR WALK-OUT with an unobstructed OCEANFRONT view. On a beautiful, quiet, sandy beach that is only 30 steps away. Our beach is reef protected, meaning the waves break offshore. So the ones ... Registration Number: TA-190-402-5600-01 GE-
1 bedroom
1 bathroom
Sleeps 5
HI> Hauula
Cool ocean breezes, tropical trade winds, pristine secluded beach and awesome ocean view will welcome you for your Hawaiian Holiday Vacation.
This modern vacation condo "Hale Luana" (pronounced ha-lay loo-ah-nah) is located in Punalu'u on ... Registration Number: TA-154-300-2112-01, GE-15
1 bedroom
1 bathroom
Sleeps 4
HI> Hauula
Notes
• Comfortable accommodations for a maximum of 6 guests. (adults and children). Only registered guests allowed on property.
• No visitors. No sub-leasing of the home and property. Maximum 2 vehicles on property.
• Absolutely no ... Registration Number: TA-207-438-4384-01, GE-20
2 bedroom
1 bathroom
Sleeps 6
HI> 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: TA-166-650-6752-01, 00500
1 bedroom
1 bathroom
Sleeps 2
HI> Hauula
Hale Moana Mele is a delightful large vacation rental home located on one of Oahu's nicest sandy bottomed swimming beaches. Hale Moana Mele is a fully furnished 4 bedroom home which fronts a sandy beach and ocean front yard complete with coconut... Registration Number: TA-107-854-0288-01, GE-10
4 bedroom
4 bathroom
Sleeps 8
HI> 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-01
1 bedroom
1 bathroom
Sleeps 2
HI> Hauula
**Please note In order to remain compliant with city and county zoning regulations this property is only rented for periods of 30 days or more to one party. Feel free to contact us with any questions.
Description
You'll love being minutes away ... Registration Number: TA-105-635-9424-01
4 bedroom
3 bathroom
Sleeps 9
HI> Hauula
Direct beachfront! Like the photos? The real thing is even better! Seriously, we hear that exact sentiment regularly from guests. Situated a mere 21 feet from our property seawall, the unit feels a bit like being on a boat, with the water in ... Registration Number: TA-212-701-5936-01, GE-21
1 bedroom
3 bathroom
Sleeps 3
HI> Hauula
GE/TA 203 430 0928 01
Welcome to the Peaceful Side of Oahu - far away from the crowds at Waikiki. Our one bedroom, 4th floor condo in the Pat's at Punaluu building has it all! You will find a complete kitchen with updated cabinets and ... Registration Number: TA-203-430-0928-01, GE-20
1 bedroom
1 bathroom
Sleeps 5
HI> Hauula
