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 (city = 'paia' OR postalCode = 'paia' OR region like '%|paia|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Permit STPH 2015/0002 GET: 146669772802 TAT: 14666977280
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Paia
Your North Shore Beach Retreat in Maui’s Premier Neighborhood:
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Paia
HI Tax ID-W01462890-02
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Paia
Maui North Shore Oceanfront Cottage 2 bedroom oceanfront cottage, sleeps 4, one rental group at a time. Relax in this charming bungalow or under the palm trees. Enjoy the surf or the nearby swimming beach or watch the turtles swimming. Minutes ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
2 rows off the sandy beach in a quiet north shore neighborhood.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Paia
Walk to everything in Paia Town. Paia Family Cottage is well equipped, with comfortable indoor, and sweet outdoor living spaces for everyone. There is AC in all bedrooms and living room. And a yard where kids can play, with views out over the ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Paia
$320/night (+ tax 17.962%)(Permit # CP2001/0018 & HT ID TA-003-079-5776-01 & TMK 2-6-009-008).
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
Nestled on Baldwin Avenue, our plantation style one bedroom cottage provides convenient and comfortable accommodations for 2 people, with the amenities of Historic Paia Town and Paia Bay just a few minutes walk away and all areas of the Island ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
4 bedroom, two bathroom, oceanfront cottage, sleeps 7, one rental group at a time. Relax in this charming 2 story house or under the palm trees. Enjoy the surf or nearby swimming beach. Minutes away from world famous Ho'okipa Beach Park and Paia...
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Paia
Walk to the beach and everything in Paia Town. Paia Family House is very spacious, well equipped, with a variety of comfortable indoor, and sweet outdoor living spaces for everyone! There's a yard where kids can play, and views out over the ...
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Paia
This Private air conditioned studio is located on a newly remodeled ocean view estate, within walking distance to some of Maui's best restaurants and shops in the historic plantation town of Pa'ia. The property offers stunning views of both the ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
Grom's Hideaway is a charming cottage nestled in the heart of Kuau Point, the last little neighborhood 1 mile outside Paia(a 5 min drive), as you head out on Hana Highway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Paia
This modern newly remodeled studio is hidden away in our tropical sanctuary we call Mangolani Inn. The studio has a king size bed, comfy love seat w/ driftwood coffee table & Mango wood dresser. Spa like custom tile bathroom and shower will ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
Maui County Rental permit - STPH 2018/0003 (this is a legal, permitted Maui vacation rental)
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Paia
Maui County Vacation Rental Permit: STPH2013/0010
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Paia
Discover paradise with a private pool, surrounded by natural oceanfront scenery. Revel in the charm of this tastefully decorated 4-bedroom, two-story Villa and romantic 2-bedroom Cottage overlooking Maui's North Shore.
6 bedroom
3 bathroom
Sleeps 12
Hawaii> Paia
Oceanfront property in the heart of Paia town, surrounded by Coconut Trees with Coastline and Ocean views. The fenced in property offers great privacy and is only a few minutes walk away from Paia's Boutiques and Restaurants.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Paia
Introducing the ultimate Maui adventure experience! Explore the magic of Maui in style and comfort with our premium 4x4 Jeep equipped with a rooftop tent, enabling you to seamlessly transition from one thrilling adventure to another while ...
1 bedroom
1 bathroom
Sleeps 1
Hawaii> Paia
Paia is convenient to the airport but a world away in culture and vibe. We have a 2-minute walk to a secluded beach, and a 1-minute walk to Mama's Fish House, one of the highest-rated restaurants on the island. A 1/4 mile drive to Ho'okipa ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Paia
FULLY PERMITTED BY MAUI COUNTY
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
TAT 199-207-5264-01,GE199-207-5264-01 Lawful Transient Vacation Rental Use 10/30/2009
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Paia
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
This unique and romantic two story, one bedroom house is nestled in a mango tree. The house is an A-frame structure with high ceilings and skylights making it bright and airy. It is located less than a mile from the bohemian town of Paia and ALL...
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Paia
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Paia
A beautiful, modern one bedroom condo located walking distance to the famous Mama's Beach on Maui’s majestic North Shore! Located in Paia, this unit is the perfect home away from home for exploring the delights of Maui's North Shore and ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
If you want to be in Paia, or minutes to Ho'okipa or Sprecklesville, you'll like this location. This is the relaxed, non-touristy Hawaiian experience. Plus, Paia is the north shore hub for all sports wind and wave. And, Paia is a walk-able ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Paia
Maui County Rental permit - STPH 2013/0008
4 bedroom
4 bathroom
Sleeps 6
Hawaii> Paia
Clean, newly built 1 bedroom/1 bath with super fast internet and A/C. Situated in a quiet neighborhood in Kuau, away from the hustle and bustle of Paia but still just a short 1 mile car journey from the town. Kuau Beach and Mama’s Beach are ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
Nalu House is the perfect home for a Maui vacation. This house, a beautiful designer remodeled home, combines old Hawaii's charm with all the modern amenities. Sit outside on a private covered lanai on relaxing lounge chairs to watch the ...
3 bedroom
3 bathroom
Sleeps 4
Hawaii> Paia
Maui County Vacation Rental Permit: STPH2013/0010
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Paia
Ku'au Plaza is a very small, friendly, quiet 30 unit condo complex; the only one of it's kind on Maui's north shore. Our fully remodeled, Beach Chic style condo is ideally nestled next to Mama's Beach & tide pools and the famous Mama's Fish ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
TA-059-556-6592-01 / GE-059-556-6592-01
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Paia
We have three newly renovated air-conditioned studios on our property. This add features our 'Large Studio' it has a king size bed and a full size sofa sleeper. Sleeps up to 3 people. (2 adults 1 child at least 8 years old) The studio has a ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
Transient Accommodation (TA) Registration Number: TA-155-824-1280-01. TMK:260120500004
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
Permitted Vacation Rental: BBPH\2015\0007
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
Permit # STWK 2016/0001
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Paia
Kunea Place rests on three-quarters of an acre of beautifully landscaped property, with a private pool and tennis court access. The home sleeps eight guests and has four bedrooms, four bathrooms, a living room, a large renovated and fully ...
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Paia
You can't beat the location: Kuau Makai Studio is just a few steps away from Mama's Beach in a quaint and quiet neighborhood where it is still a pleasure to walk about.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
This is an adorable TWO bedroom house with high vaulted ceilings and shiny wood floors. The house is a half block off Hana Highway, 0.9 miles from Paia town, and within a mile of all the North Shore Beaches.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
Right on the fabled road to Hana just one mile east of Paia Town, this little gem of a hideway offers a sampling of both sides of Maui, "mauka" (toward the mountain) and "makai" (toward the sea): From this home's front entryway, an unobstructed...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
My place is simple cozy and very well located. Only a 2 minutes walking distance to a private beach on the north shore of Maui, and walking distance to the world famous Mama's Fish House Restaurant. Hookipa Beach Park which features great ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
Your heavenly Hawaiian holiday awaits at this seaside sanctuary situated just 1 block from the diamond-dusted shoreline of the Maui coast. This 1-bedroom, 1-bath condo is well-appointed with upscale furnishings and a private outdoor lanai with ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
Your well-kept condo offers the perfect blend of comfort and convenience, ensuring a relaxing stay. The air conditioning provides respite from the tropical heat whenever you need it, but you'll likely find yourself drawn to the outdoors most of ...
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Paia
My one bedroom apartment is very well located on the beautiful and wild north shore of Maui. It is 2 minutes walking distance to Mama’s beach, right in front of the world famous Mama's Fish House Restaurant. Hookipa Beach Park, which features ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
*On the Beach!* Luxury Private Villa 4 bdrm, 4 1/2 baths, Pool, Kite, SUP, Relax, close to Paia
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Paia
The apartment is less than a 100 yards from one of the most beautiful semi private beaches in this area, and also the best restaurant on the island, Mama's Fish House.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
Permitted Vacation Rental: BBPH\2015\0007
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
TA-190-072-6272-01 / GE-190-072-6272-01
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Paia
At the Paia Inn, your safety and well-being is our top priority. Our dedicated staff is here to make your stay comfortable and memorable – with outstanding customer service, in-house amenities and dining, and great vibes. We have implemented ...
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Paia
Discover paradise in our 4-bedroom, two-story Villa overlooking Maui’s North Shore. Featuring a pool surrounded by breathtaking oceanfront scenery.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Paia
