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 = '96712' OR postalCode = '96712' OR region like '%|96712|%') 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
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 between Backyards beach and the bike path. Only ...
Registration Number: TA-087-037-5424-01, 58003
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Sunset Beach
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
