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, 20
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
Welcome to your dream getaway at Maui Kaanapali Villas! Nestled along stunning North Kaanapali Beach, this charming 1-bedroom, 1-bathroom condo promises a rejuvenating retreat amidst ocean views and spectacular West Maui Sunsets. Maui Kaanapali ...
Registration Number: GE-170-583-7568-01, TA-170-583-7568-01, 440060110079
- 1 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Lahaina
A Note About The $995/nt Rate: It is only for dates that are very close to the current date that I have not been able to book so I lower the price to encourage someone to take it on short notice, for instance between today and a month from now, ...
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
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 1000 feet (.2 miles) from Sunset Beach ...
Registration Number: 1590210120000, GE-192-062-6688-01, TA-192-062-6688-01
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
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 plantation doors that slide. The cottage is...
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
5 Bedroom, 3 Bathroom home with Den and Huge Kitchen on a Private Acre Lot
**PARADISE FOUND!!** If you are looking for a fun, private home with ultra plush comforts and well appointed modern beach decor for your North Shore vacation, this...
Registration Number: 1590120150000, TA-212-461-1584-01, GE-212-461-1584-01
- 5 bedroom
- 4 bathroom
- Sleeps 10
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
