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, 30
Permit STPH 2015/0002 GET: 146669772802 TAT: 14666977280 Paia Ko is a quiet, lovely, 3 bedroom 2 and a half bath home with beautiful ocean views and central air conditioning that backs up to an old sugar cane field. With the benefit of a ...
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 31 Kaiholo place, nestled behind the famous Mama's Fish House(please make reservations before you arrive). This home has 3 bedrooms, 2 full baths, living room, dining room, dining area, fully equipped ...
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
SUPPORT MAUI THROUGH ALOHA TOURISM. Maui Winds II Paia - permit # STPHT2013/0019 is perfect for family and friends or a couple. This breezy and charming home invites rest and relaxation and offers exciting activities for top-notch athletes ...
2 bedroom
2 bathroom
Sleeps 6
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
PLEASE VISIT THE STATE / COUNTY WEBSITES FOR CURRENT COVID INFORMATION Located in central Paia, a short walk to beach, restaurants, bars, grocery, bank, boutiques. A thoughtfully remodeled c. 1934 plantation cottage, 832 SF, with modern...
2 bedroom
1 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). Minimum stay 7 nights. Want to stay fewer days? At certain times of the year, or for last minute bookings, we might waive the minimum nights ...
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 7
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 ...
0 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. Grom's has 3 bedrooms (king, queen and two twins) and 1 Bath with an ...
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 ...
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
Maui County Rental permit - STPH 2018/0003 (this is a legal, permitted Maui vacation rental) TA-066-523-9552-02 / GE-066-523-9552-02 TMK-260090240001 It's a Beach House! A place to relax, unwind, listen to the breeze through the ...
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Paia
Maui County Vacation Rental Permit: STPH2013/0010 GE-066-317-9264-02/TA-066-317-9264-01 TMK-260120340000 Experience a delightful part of “old Hawaii” with all modern conveniences by staying at the Menehune Cottage or House in ...
2 bedroom
2 bathroom
Sleeps 4
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. The main house ...
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
*On the Beach!* Luxury Private Villa 4 bdrm, 4 1/2 baths, Pool, Kite, SUP, Relax, close to Paia Once in the lifetime opportunity to enjoy this private beachfront property! Swimmable, Sandy Beach, On the beach “Inspiration” - ...
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Paia
FULLY PERMITTED BY MAUI COUNTY STPH 2013-0026 TA-073-017-7536-01, TA-134-951-1168-01 Ice-cold Mistubishi Split A/C units added 5/17. New entry door added 4/17. This listing is for the upstairs Bay View unit. The upstairs unit...
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 Hale Ola is an original island-style beach house, recently remodeled, with beautiful panoramic views of the West Maui mountains and Kanaha Beach Park. The ...
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Paia
Nestled on Baldwin Avenue, this two bedroom cottage provides convenient and comfortable air conditioned accommodations, comfy queen sized beds and a/c units in each bedroom, with the amenities of Historic Paia Town and Paia Bay just a 5 ...
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
TAT 199-207-5264-01,GE199-207-5264-01 Lawful Transient Vacation Rental Use 10/30/2009 NEW UPDATE!!- newly remodeled Kitchen with all new appliances including: Double Oven, Wine Fridge, Breakfast bar overlooking the ocean- ahhhh!! ...
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 TA-048-493-9776-01 / GE-048-493-9776-01 TMK-260040080000 WALK TO EVERYTHING IN PAIA TOWN! The setting is just stunning! Enter the back gate, and the first view looking through the glass ...
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 ...
1 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 ...
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Paia
Maui County Vacation Rental Permit: STPH2013/0010 GE-066-317-9264-02/TA-066-317-9264-01 TMK-260120340000 The Menehune property is located in Kuau Point, a residential sea-side outcropping near Paia, on Maui's well-loved North Shore. ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Paia