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
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
For more information please go to HonuPacificSurf.com
This beautiful beachfront property is located on the famous North Shore of Oahu. The house is a 3 story, 4 bedroom, 3.5 bathroom with open living space and a relaxing lounge area under...
Registration Number: 16101201, TA-108-239-6672-01, GE-108-239-6672-01
- 4 bedroom
- 4 bathroom
- Sleeps 12
Hawaii> Haleiwa
Inquire about available dates even if shown as blocked, as some dates are blocked for homeowners and they are flexible.
* Exciting News! Maluhia has upgraded with split AC throughout, enhancing comfort in every room of this Papailoa ...
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
Aloha, and welcome to paradise!
Please CALL ME before BOOKING. For accommodations please give me, Ashley, a call: (801)735-2878. I like to speak with all future guests before accepting bookings. I'm just six minutes from this beautiful ...
Registration Number: TA-061-502-2592-01, GE-151-625-8304-01
- 7 bedroom
- 8 bathroom
- Sleeps 14
Hawaii> Haleiwa
ALOHA is what Sunsetyards is all about. Partial ocean views.
This classic beach house is deep in surf history and is located on the tip of Sunset Pt. Windows and garden all around create beautiful ocean and garden light. Lanai is half ...
Registration Number: GE-151-548-3136-01, TA-151-548-3136-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
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
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
