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 (`headline` like '%sunset beach%') AND sleeps>=1 order by rating desc LIMIT 0, 50
This upstairs, 3-bedroom 2-bath home, with its spacious open floor plan, oversized rooms, vaulted high ceilings, and panoramic windows gives you sweeping views of the Pacific Ocean and is backdropped by the rugged Ko'olauloa Mountains. Sit ...
Registration Number: 5901400900000, TA-104-468-6848-01, GE-104-468-6848-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
A Truly Luxury Beachfront House Located on World Famous Sunset Beach on Oahu's North Shore. Named 'Paradise on the Beach' at Sunset Beach by many guests this premier Direct Beachfront two-story, two large bedrooms, three full baths, executive ...
Registration Number: GE-194-002-5344-01, TA-194-002-5344-01
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Sunset Beach
Sunset Beach Retreat is only a one block walk to the famous Sunset Beach and Backyards on the North Shore of Oahu.
This spacious upstairs 3 bedroom, 2 bath home has a fully equipped kitchen, stocked linens, two lanais, large fenced in ...
Registration Number: 590010040006, GE-131-721-4208-01, TA-131-721-4208-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
Unit B1 is Ocean Front at Sunset Hale. Newly updated 2-bedroom 2.5-bath ground floor oceanfront unit.
The Kitchen, living room and dining area are all facing out to beautiful ocean and sunset views! Enjoy these views from inside or ...
Registration Number: 590020080000, TA-098-696-8064-01, GE-098-696-8064-01
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Haleiwa
ID TA # 006-537-8816-01
GE # 006-537-8816-01 This property is a legal vacation rental and all city ordinance are fallowed.
Newly installed air conditioning, splitters in each bedroom and living room with each having their own remote ...
Registration Number: TA-006-537-8816-01, 15900300500, GE-006-537-8816-01
- 4 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Haleiwa
Welcome to the Green Pineapple, a gorgeous 3 bedroom estate on Oahu's North Shore. Take a walk down memory lane in style, as this gorgeous North Shore Oahu estate has all the charm of Hawaii's vintage era, in this two-story, luxury home. *400mb...
Registration Number: 590220050000, TA-197-424-3328-01, GE-197-424-3328-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
A Kaimaina home that is nestled on a quiet dead end street in the coveted neighborhood of Sunset Point. This one of a kind home has been in our family for over 70 years. Remodeled and completely refurbished, this is “country living” at ...
Registration Number: GE-090-863-2064-01, TA-090-863-2064-01, 590010600000
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Haleiwa
**In compliance with Hawaii vacation rental laws, this property is held for your exclusive use for a period of 30 days or greater regardless of your actual length of stay. Please inquire for details or questions regarding availability.
E ...
Registration Number: 159013018000, GE-193-050-8288-01, TA-193-050-8288-01
- 4 bedroom
- 5 bathroom
- Sleeps 9
Hawaii> Haleiwa
Welcome to Surfer’s Paradise by Gather, the ultimate beachfront vacation rental on the North Shore of Oahu. With its clean, spacious, and uncluttered space, Surfer’s Paradise is the perfect place to enjoy every second of your vacation.
...
Registration Number: 590020340000, TA-155-479-0912-01, GE-155-479-0912-01
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Haleiwa
Escape to the stunning shores of Haleiwa and experience the ultimate beachfront getaway at our charming vacation rental. Nestled along the pristine sands and turquoise waters, this slice of paradise offers an idyllic setting for relaxation, ...
Registration Number: TA-020-402-3296-01, GE-020-402-3296-01, 59002009
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Haleiwa
A Note About The $1,150/nt Rate: It is calculated as $1,350/nt with a 15% discount that brings it to exactly $1,147.50/nt. Please note, It is only for dates that are very close to the current date that I have not been able to book so I lower ...
Registration Number: 590030670000, GE-029-109-0432-01, TA-029-109-0432-01
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Haleiwa
Welcome to the Green Pineapple, a gorgeous 3 bedroom estate on Oahu's North Shore with an attached studio apartment. Take a walk down memory lane in style, as this gorgeous North Shore Oahu estate has all the charm of Hawaii's vintage in this ...
Registration Number: 590220050000, TA-197-424-3328-01, GE-197-424-3328-01
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Haleiwa
Contact Host for availability
Hawaiian plantation living. The acre estate is located across from the world famous Sunset Beach, prime location. The Lanai is a 2 bedroom (Queen) 1 bth on Sunset Beach, North Shore, Oahu. The Lanai is ...
Registration Number: 1590210120000, GE-192-062-6688-01, TA-192-062-6688-01
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
Contact Host for availability
Located on a gated acre in the heart of Sunset Beach on Oahu’s North Shore. The Cottage has 1 bedroom King bed with ensuite bathroom and an additional queen bed (pune'e). The room can be divided by ...
Registration Number: 1590210120000, GE-192-062-6688-01, TA-192-062-6688-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Haleiwa
Hawaii Tax I.D for both TAT & GET. No. 027-702-8864-01
Our oceanfront home, Kawela Kai, is located on the North Shore of Oahu in between Sunset Beach and the Turtle Bay Resort. The stretch of beach where we are located is beautifully ...
Registration Number: GE-027-702-8864-01, TA-027-702-8864-01, 570050090002
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
For rental details please 'Contact host' using blue link on right of page. To comply with C&C requirements, rental is a fixed amount for any stay period up to 30 days. Under local ordinances, our house is classified as a long-term rental. ...
Registration Number: GE-083-788-8000-01, 59021002, TA-083-788-8000-01
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Haleiwa
Nestled on the shoreline of the iconic Sunset Beach on Oahu’s North Shore, is Blue Mahina. This custom 4BD, 3BA home offers nearly 3000 sqft. of open living space with captivating ocean views of the Pacific Ocean.
After entering ...
Registration Number: GE-002-385-3056-01, TA-002-385-3056-01, 590020240000
- 4 bedroom
- 3 bathroom
- Sleeps 11
Hawaii> Haleiwa
3 minute walk to sunset beach, this cottage is on an acre parcel; away from it all but so close to the warm pacific ocean at world famous sunset beach on Oahu's north shore! It is across the street from sunset beach and the address is on Kam ...
Registration Number: GE-083-767-9104-01, TA-083-767-9104-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
Aloha & Welcome to our Ocean/Beach front Backyards Bungalow:) If you are looking for paradise look no more! This beachfront backyard bungalow is located just to the right of famous Sunset Beach in the heart of the world famous North Shore on a ...
Registration Number: TA-131-007-5392-01, GE-131-007-5392-01
- 3 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Haleiwa
30 DAY RENTAL*****A beautiful, peaceful, private north shore estate, 300 ft from sunset beach. The main house has 4 bedrooms and 4 bathrooms and sleeps 8 in beds. You may rent the cottage for an additional 2 people-check availability-it's called...
Registration Number: 159014004000, GE-083-767-9104-01, TA-083-767-9104-01
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Haleiwa
Welcome to world famous Sunset Beach in North Shore Oahu, Hawaii. Settle into your beautifully restored beach house, with 50ft of spectacular white sandy beach frontage. Listen as the waves roll in, a gull cries and a gentle breeze stirs the ...
Registration Number: GE-111-146-3963-01, TA-111-146-3936-01
- 5 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Haleiwa
FREE daily activities included with your stay value up to $570! Complimentary admission, every day of your stay: Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day Electric Bike Rental. ...
Registration Number: GE-170-583-7568-01, TA-170-583-7568-01, 440060110079
- 1 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Lahaina
*Available for monthly rentals only* Welcome to Sunset Point, the most exclusive neighborhood on Oahu's famous North Shore. Located at Sunset Beach (V-land) our spectacular home offers unique qualities seen in very few North Shore homes: ...
Registration Number: GE-196-566-0160-01, TA-196-566-0160-01
- 4 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Haleiwa
Aloha & Welcome to our Hula Hut:) If you are looking for paradise look no more! This cozy home is located on Sunset Beach in the heart of the world famous North Shore on a private sand beach that stretches miles in both directions. One of the ...
Registration Number: TA-131-007-5392-01, GE-131-007-5392-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Haleiwa
This is a great studio with private back yard and lanai. Comes with a queen bed, couch that pulls out to a bed, fully stocked kitchen, bathtub, and gas grill. Laundry is on-site, and gated parking for your car. Newly installed split AC system...
Registration Number: TA-007-468-4416-01, W65442690-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Haleiwa
This is a beautifully furnished studio, only a 2 min walk to the waves. In a quiet neighborhood, on the ocean side of Kam Hwy. Brand new Air Conditioner, Heavenly!
The kitchen is fully equipped, the microwave doubles as a conventional oven....
Registration Number: TA-007-468-4416-01, W65442690-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Haleiwa
The Treehouse at Sunset Beach is a one bedroom guesthouse with the most spectacular 180 degree views of the North Shore of Oahu from Velzyland to Ka'ena Point. The guesthouse is situated on 0.5 acre property that is located at the end of a gated...
Registration Number: TA-123-214-0800-01, 590060300000, GE-123-214-0800-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Haleiwa
Enjoy the luxury of a short walk to the beach while staying up a gorgeous secluded valley. This studio has it all. The location is idea for surfing, swimming, hiking and dinning at the newly remodeled Turtle Bay resort located just 5 minutes ...
Registration Number: 580050880000, GE-002-649-9072-01, TA-002-649-9072-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
Beach tree house feel , have sunset drinks over looking sunset beach or watch the world famous sunset surf break, totally separate 2nd story beach house with loft that the kids love, 2 minute walk to the beach one way or the sunrise shack the ...
Registration Number: 1590020010000, TA-005-909-0432-01, GE-005-909-0432-01
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Haleiwa
Sunset beach is waiting for you at this 3-bedroom house. This lovely property features 1 king bed, 1 queen bed, and 1 bunk bed, perfect for a pleasant stay. With 2 bathrooms equipped with a bidet and shower, guests will feel right at home. ...
Registration Number: GE-002-649-9072-01, 15800508, TA-002-649-9072-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
OUR FIVE STAR REVIEWS SAY IT ALL- SO PLEASE TAKE THE TIME TO READ THROUGH THEM!
If peaceful island living, ocean views, and untouched north shore Oahu beauty are what you are looking for on your Hawaii vacation, you have found the right ...
Registration Number: 571410010000, TA-200-438-5792-01, GE-200-438-5792-01
- 4 bedroom
- 4 bathroom
- Sleeps 12
Hawaii> Kahuku
THE BEST BEACHFRONT LOCATION ON SUNSET BEACH! This is the private and secure luxury home the discriminating traveler and/or family will feel 'one' with the ocean and is right on the sands of world famous SUNSET BEACH. Spectacular sunsets, never ...
Registration Number: GE-201-477-7344-01, TA-201-477-7344-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
Paradise Found!! Enjoy a unique Hawaiian experience. Ocean front lava rock beach house has gorgeous ocean sunset and coastline views from all major rooms in our home. Enjoy our Hawaiian style home and relax on the outdoor lenai while enjoying...
Registration Number: TA-185-574-8096-01, 840070100000, GE-185-574-8096-01
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Waianae
Welcome to Hale Kimo by Gather, nestled on the iconic shores of Sunset Beach in O'ahu, Hawaii. This grand beachfront estate is perfectly situated to offer something for every visitor in any season. From exhilarating surf breaks in the winter to ...
Registration Number: 590011030000, GE-114-498-3552-01, TA-114-498-3552-01
- 6 bedroom
- 4 bathroom
- Sleeps 12
Hawaii> Haleiwa
Newly renovated , Sunset Beach studio
Private beach access , mini bar kitchen everything you need to cook basic meals , full size fridge .
Walk the north shore bike path to pipeline or grab some lunch at teds bakery . ...
Registration Number: TA-005-909-0432-01, GE-005-909-0432-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
Welcome to Sunset Beach Island Retreat by Gather, your perfect Hawaiian getaway on Oahu’s North Shore. This spacious three-bedroom, three-bathroom home comfortably accommodates up to six guests and offers modern luxury and family-friendly ...
Registration Number: GE-127-081-0624-01, TA-127-081-0624-01, 590150190000
- 3 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Haleiwa
