select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 and (registration_number like 'TA-%') AND (city = 'haleiwa' OR postalCode = 'haleiwa' OR region like '%|haleiwa|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
30 day minimum *Inquire for monthly rate*
The Ohana House is located Laniakea/Turtle /Papailoa Beach (North Shore's most exclusive beach). Only 5 min walk or 2min by bike to the beach. Strategic located near Haleiwa Town (avoid North Shore ... Registration Number: TA-032-035-4304-01, GE-03
3 bedroom
2 bathroom
Sleeps 8
HI> Haleiwa
Beautiful Private Oceanfront 3 bedrooms 2 bath home with air condition in the bedrooms LARGE PRIVATE LOT ON THE OCEAN with a full kitchen and lots of space to move around and enjoy each other with out others around you like in a hotel.
You can ... Registration Number: TA-063-435-1616-01, 61009
3 bedroom
2 bathroom
Sleeps 6
HI> Haleiwa
*( covid cancellation policy )*
Luxury awaits you in this very private sandy beach and ocean front, adults only, guesthouse of The Hale Grandiflora. The second floor accommodation, The Hoku (meaning star in Hawaiian) is where modern classic ... Registration Number: TA-162-356-2240-01, 16235
1 bedroom
1 bathroom
Sleeps 2
HI> 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-14
3 bedroom
2 bathroom
Sleeps 8
HI> Haleiwa
Experience local-style living in the cool, quiet, and tranquil Pupukea, a perfect place to relax! This newly remodeled studio (brand new kitchen, appliances, flooring, and paint) can accommodate 2 guests. Enjoy a private entrance and free ... Registration Number: TA-118-346-3936-01, GE-11
0 bedroom
1 bathroom
Sleeps 2
HI> Haleiwa
Only accepting reservations 30 days or more. Wake up to the waves in this oceanfront beach house between World Famous Pipeline and Sunset Beach. The main house is 3 bedroom 2 baths. The open lanai is ideal hang-out spot for relaxing, watching ... Registration Number: TA-060-635-3920-01
3 bedroom
2 bathroom
Sleeps 10
HI> Haleiwa
The Pipeline House is a uniquely designed piece of paradise that sits on the beach, front and center at the world famous Bonzai Pipeline! Pipeline or Ehukai Beach which means, sea spray is located on the best stretch of beach throughout all of ... Registration Number: TA-110-355-6608-01, GE-11
3 bedroom
4 bathroom
Sleeps 6
HI> Haleiwa
Enjoy this new oceanfront home, completed in 2015. The home sits on a half acre of oceanfront land which is fully landscaped including gardens and fruit trees, The property boasts dramatic 180 degree ocean, island and coastline views. There is... Registration Number: TA-048-487-6288-03, 61003
4 bedroom
4 bathroom
Sleeps 8
HI> Haleiwa
Aloha!!
Thank you for considering Sunset Hale.
The "hale" is nestled in a beautiful country neighborhood and is a relaxing place to stay while you enjoy the North Shore. Situated privately, on the bottom 1/3 of our family's acre, with ... Registration Number: TA-010-526-5152-01, GE-01
0 bedroom
1 bathroom
Sleeps 2
HI> Haleiwa
Welcome to Hale Pono!
The North Shore of Oahu’s premier luxury getaway.
Please email me with any questions about available bookings.
This hidden, elegant beauty sits on pristine property within the exclusive, gated community of the Sunset ... Registration Number: TA-166-809-3952-01, GE-16
4 bedroom
5 bathroom
Sleeps 8
HI> Haleiwa
Location, Location, Location .... This (100ft frontage) gated sandy beachfront property is located along the famous North Shore 'Banzai Pipeline' and offers panoramic breathe taking views.
The home was recently fully remodeled and when combined... Registration Number: TA-001-938-6368-01, GE-00
1 bedroom
2 bathroom
Sleeps 5
HI> Haleiwa
Capturing beautiful breezes and scents of a 2-acre Plumeria (Frangipani) flower farm surrounded by tropical fruit & foliage, owned & operated by the late Raynel Foster and her husband Frank.
This upstairs, 3-bedroom 2-bath home, with its ... Registration Number: TA-104-468-6848-01, GE-10
3 bedroom
2 bathroom
Sleeps 6
HI> 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) we accept 30 nights stay or longer and try to accommodate your travel needs. For your ... Registration Number: TA-057-911-5008-02, 59004
1 bedroom
1 bathroom
Sleeps 2
HI> Haleiwa
ALOHA is what Sunsetyards is all about.
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 covered with ... Registration Number: TA-151-548-3136-01, GE-15
2 bedroom
1 bathroom
Sleeps 4
HI> Haleiwa
In compliance with City and County Of Honolulu Ordinance Section 19-18, our house is available to rent for a period of 30 days or more. Please contact the owner to find out about shorter stays. By law, the property cannot have other tenants ... Registration Number: TA-051-248-7424-01, 15900
3 bedroom
2 bathroom
Sleeps 6
HI> Haleiwa
