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 = '96712' OR postalCode = '96712' OR region like '%|96712|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
****Please note this property may not be rented for less than 30 consecutive days. Rental prices will not be reduced or adjusted based on the number of days the rental is actually used or occupied. Please inquire regarding availability for stays...
Registration Number: GE-179-329-6384-01, TA-179-329-6384-01, 161009013000
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Haleiwa
Beautiful, fully renovated, 3/1 on very private acre above Ke Iki Beach. House is classic, old Hawaii style with sweeping ocean views. Short walk or bike ride to Pipeline, Waimea, Sunset. Easy access via bike path to miles of world class surf ...
Registration Number: 590080020000, GE-018-615-0912-01, TA-018-615-0912-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
Private, detached, gorgeous, newly built, fully equipped, one bedroom guest house ready and waiting to be booked by you! Superb location! Short walk to beautiful parks and beaches and minutes drive to historic Haleiwa Town.
This ...
Registration Number: GE-103-420-9280-01, TA-103-420-9280-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
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
TAX ID #TA057-911-5008-02 GE#057-911-5008-02 Our guest area is a great place to come and work from our guest area (we have excellent internet connection and a relaxed computer station counter) we accept 30 nights or longer. For your safety and ...
Registration Number: TA-057-911-5008-02, GE-057-911-5008-02, 590040550000
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
PLEASE SEE BELOW FOR OWNER INFORMATION.
Nestled in the Sunset Point neighborhood, this house is in a great location to surf and enjoy Hawaii's Sunset Beach coast line. We are truly on Sunset Point the ocean side of Kamehameha highway ...
Registration Number: TA-087-037-5424-01, GE-087-037-5424-01, 580030640000
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Sunset Beach
Enjoy this Lovely Tropical Paradise!! Registration Number: GE-096-482-7136-01, TA-096-482-7136-01 ********************************************************************************************************...
Registration Number: TA-096-482-7136-01, GE-096-482-7136-01
- 5 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Haleiwa
Welcome to Hale 4 U! Walk thru the front door to be greeted by a breathe taking panoramic view of the most unique coastline in the Hawaiian Islands. Nestled in a gated community that features a private bay, this beachfront 5 bedroom, 3-5 ...
Registration Number: TA-070-626-9184-01, GE-070-626-9184-01
- 5 bedroom
- 4 bathroom
- Sleeps 9
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
Aloha,
Thank you for considering Sunset Hale!
In compliance with the city guidelines regarding vacation rentals in our area, this property is rented once per 30 days. Please contact us directly regarding your requested dates, and ...
Registration Number: GE-010-526-5152-02, TA-010-526-5152-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
Inquire about available dates even if shown as blocked, as some dates are blocked for homeowners and they are flexible.
This lovely home is located on one of the most spectacular and quiet beaches on the North Shore. Turtles are ...
Registration Number: 0040960000000, TA-020-234-2400-01, GE-020-234-2400-01
- 4 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Haleiwa
This Cottage by the Sea is the perfect getaway for your Hawaiian Vacation. This 3 bedroom 2 bath completely remodeled home sparkles like New, in walking distance to the Best North Shore Beaches, Attractions, and Eateries. After a long day at the...
Registration Number: TA-057-752-7808-01, 580050180003, GE-057-752-7808-01
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Haleiwa
This amazing timber frame home provides privacy and luxury on the beach. A central courtyard garden creates a zen atmosphere while the fully appointed kitchen allows the creation of gourmet meals or just mai tais to enjoy overlooking beautiful ...
Registration Number: 159003007000, GE-026-381-5168-01, TA-026-381-5168-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
Hale Malina is a luxury custom home is located in a quiet gated community directly beachfront on Sunset Beach, with miles of beautiful sandy beach and world class surf break right outside your door.
The property is 20,000 sq. ft and has ...
Registration Number: GE-024-872-9600-01, TA-024-872-9600-01
- 3 bedroom
- 3 bathroom
- Sleeps 12
Hawaii> Haleiwa
ALOHA!
Welcome to our beachfront villa!
This three bedroom home on Oahu's North Shore is a great place to get away from the hustle and bustle of City life. It's only a forty minute drive from Honolulu Airport - located in a gated ...
Registration Number: TA-089-262-8992-01, 590030240005, GE-089-262-8992-01
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Haleiwa
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
The unit has a TV with cable, internet connection, a full kitchen with a stove top, refrigerator, microwave, toaster, blender, coffee maker, plates, pans, and other
eating utensils. The unit is very clean, very comfortable with soft ...
Registration Number: GE-067-786-3424-01, TA-067-786-3424-01
- 3 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Haleiwa
Paradise At Your Doorstep
Vacation Rental located at Log Cabins/Rock Piles/Pipeline
Welcome to the newly listed Ke Waena Beach Cottage situated on the world famous North Shore, Oahu. Settle into your tastefully restored intimate ...
Registration Number: TA-121-502-9248-01, GE-121-502-9248-01
- 3 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Haleiwa
Spacious direct beachfront home with 4 bedrooms and 4 full bathrooms. Home was recently renovated and redesigned for maximum comfort and relaxation. The beach is stunning with flat ocean in the summer, and lots of surfing action and large ...
Registration Number: GE-019-517-2352-01, TA-019-517-2352-01
- 4 bedroom
- 4 bathroom
- Sleeps 11
Hawaii> Haleiwa
The studio has a TV with cable, internet connection, air conditioner, a full kitchen with an oven and stove, a refrigerator, microwave, toaster, blender, coffee maker, plates, pans, and other eating utensils. The studio is very clean, very ...
Registration Number: GE-067-786-3424-01, 159003081000, TA-067-786-3424-01
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Haleiwa
