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, 30
****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
In compliance with City and County Of Honolulu Ordinance Section 19-18 and Ordinance 22-7 (Bill 41) our house is available to rent for a period of 30 days or more. Please message the owner to find out about shorter stays. By law, the property ...
Registration Number: 159003078000, GE-051-248-7424-01, TA-051-248-7424-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
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
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
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
Beachfront property on Northshore Oahu. Beautiful home on the beach with close access to numerous activities. Gated property with lots of privacy, beachside lanai and memorable sunsets. House has 3 Bedrooms and 2 full Bathrooms with ac in all ...
Registration Number: 59020050, TA-036-160-3072-01, GE-036-160-3072-01
- 4 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Haleiwa
Oceanfront Beach House - Tropical Paradise!
Please call me to inquire about booking. Kelley (858) 947-8173. I like to speak will all future guests. I look forward to speaking with you soon. Mahalo!
Our Tropical Paradise is ...
Registration Number: GE-083-416-0640-01, TA-083-416-0640-01
- 7 bedroom
- 5 bathroom
- Sleeps 16
Hawaii> Haleiwa
PRIVATE BEACH HOUSE,, LARGE BEDROOM WITH CAL KING SIZE BED, WITH SOUNDS AND SIGHT OF OCEAN, TRAVITINE TILE FLOORS THRUOUT HOUSE AND BATH W/BATH TUB AND SHOWER. HOUSE DECOR is MIXED ASIAN AND BEACHY THEME ,DIRECT SATALITE TV IN LIVING AND ...
Registration Number: 161004065000, TA-154-073-7024-01, GE-154-073-7024-02
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> haleiwa
30 day minimum *Inquire for monthly rate*
The Hawaii Studio is located Laniakea/Turtle /Papailoa Beach (North Shore's most exclusive beach).Only 5min walk or 2min by bike to the beach. Strategic located near Haleiwa Town (avoid North Shore ...
Registration Number: TA-032-035-4304-01, GE-032-035-4304-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
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
Aloha ! because of new laws ...This will be a rental for 30 days or more state regulations.
This studio is for one guest only that will not have additional visitors to the property.
This lovely studio comes with a nice deck in...
Registration Number: GE-151-548-3136-01, 580031030000, TA-151-548-3136-01
- 0 bedroom
- 1 bathroom
- Sleeps 1
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
- LOCATION! Beautiful home Very conveniently located.
- Quiet Neighborhood, this is NOT a party home.
- 1 designated parking stall, street parking in often unavailable in this popular area
- LARGE open floor plan.
- private entrance ...
Registration Number: GE-013-962-6496-01, TA-013-962-6496-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
This newly remodeled Haleiwa vacation rental home is just steps from the sand at Ehukai Beach Park and world-famous Banzai Pipeline. Complete with ocean views and air conditioning in the living areas, it has a chic, modern beach house feel with ...
Registration Number: TA-143-012-9664-01, GE-143-012-9664-01, 590200160000
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Haleiwa
Aloha Please Note: max 2 adults, no children.
The Surf Ohana is a beautiful large one bedroom for max of 2 adult guests, one child. The double living room is open to dining room and vintage style kitchen. The large living room space w ...
Registration Number: GE-151-548-3136-01, 580031030000, TA-151-548-3136-01
- 1 bedroom
- 0 bathroom
- Sleeps 2
Hawaii> Haleiwa
Aloha! Relax and experience North Shore perfection from the comfort of this chic and modern loft. Located just across the street from world class beaches & the historic North Shore bike path that stretches the famous tropical coastline known as ...
Registration Number: GE-076-962-6112-01, TA-076-962-6112-01
- 1 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Haleiwa