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, 20
Permit STPH 2015/0002 GET: 146669772802 TAT: 14666977280
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Paia
Gorgeous and elegant 3 Bedroom/3 Bath Beach Home, a few steps from famous Baby Beach is waiting for your arrival. Perfect for couples. This beautiful and renovated Beach Home is your ticket to a perfect vacation. Nestled in a quiet upscale ...
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
