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 = 'haleiwa' OR postalCode = 'haleiwa' OR region like '%|haleiwa|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Welcome to Hale Pono! The North Shore of Oahu’s premier luxury getaway. Please message me with any questions about available bookings. This hidden, elegant beauty sits on pristine property within the exclusive, gated community...
Registration Number: TA-166-809-3952-01, 58003095, GE-166-809-3952-01
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Haleiwa
****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
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
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
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