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,
a.`title_rewritten`, a.`custom_url`,
(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 = CASE
WHEN postalCode IN (96701,96706,96707,96712,6717,96730,96731,96734,96744,96782,96786,96789,96792,96826,96795,96797,96813,96814,96815,96816,96817,96818,96819,96821,96822,96825)
THEN
CASE
WHEN (registration_number like '%TA-%' or registration_number like '%TA %') THEN 1
ELSE 0
END
ELSE 1
END
and postalCode!=''and round(price)>0 and bedrooms>=0 AND sleeps>=1 order by rating desc LIMIT 0, 50
Punahele Penthouse Condo will fast become your favorite place to stay in Kailua-Kona! Punahele Penthouse is on the 7th (top) floor of the Kona Ali'i Condo Complex which is located in the heart of Kailua-Kona. You're within walking distance to many attractions, restaurants/bars and shops!
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
only a 3-minute walk to the Coconut Marketplace, or the Lava Lava Beach Club which offers drinks, dining, and live entertainment right on the sand. All the latest tech and amenities, including an LG OLED Smart TV and the latest Apple TV with paid premium streaming subscriptions (HBO Max, Netflix, Paramount Plus, Peacock, and more.
1 bedroom
4 bathroom
Sleeps 4
Hawaii> Kapaa
gourmet kitchen, ocean views, huge bedrooms. "perfect for a family reunion" "cool at night was just right" "close to the best beach in Hawaii" "your own private estate" "amazing sunset views" We are not a corporation.
6 bedroom
6 bathroom
Sleeps 18
Hawaii> Kailua-Kona
We invite you to stay in our peaceful home on the North Shore of Maui, within walking distance to a local coffee shop, small grocery and near the Road to Hana and Haleakala. Only available to book with the owner Amy - Beware of scammers advertising our home on other platforms such as CL.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Haiku
Dream your most desired vacation. Then come to magical Maui, one of the world’s most elite vacation destinations. How better to experience our island than by renting from an owner who has combined her own personal getaway with the conveniences of home and the leisurely delights of a vacation in the sun.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Maalaea
(Be aware that the beaches are subject to tidal erosion). Beach chairs, towels and snorkeling gear are provided if you wish to swim with the turtles feeding at the waters edge. Please don't touch them as we would like them to return each day.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Discover your family’s ideal getaway in the heart of Princeville at Hanalei, Kauai! Our spacious vacation rental offers a tranquil oasis surrounded by lush landscapes and tranquil gardens. • Family-Friendly Amenities: Enjoy beach chairs, boogie boards, and snorkeling gear for fun-filled days by the ocean.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Gorgeous, completely upgraded oceanfront/oceanview 2 bedroom, 2 bath. This condo is exquisite and has been meticulously renovated for the discriminating traveler. Huge, unobstructed ocean and island views from the living room, lanai and master bedroom.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Love to snorkel and want to see over 100 species of tropical fish & turtles? ! Our modern beachfront unit offers gorgeous ocean/sunset views and the easiest access to the incredible snorkeling at Kahaluu Beach.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Kauhale Makai (Village by the Sea) truly embodies paradise. The elegantly designed bedrooms and bathrooms ensure a comfortable stay. Located on the fourth floor, the condo provides stunning ocean views from the primary bedroom, kitchen, living room, and lanai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
ALOHA. Come vacation in our beautiful, newly remodeled 2BR/2BA beachfront home. Enjoy the breathtaking views of Kaho’olawe Island, Molokini Crater, the West Maui Mountains and of course the pristine Pacific Ocean and its shoreline. Sugar Beach is the longest sandy beach in Maui and is just steps away.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
" Come enjoy island life while staying in our newly built, 600 sq. ft. upstairs suite. Beautifully furnished with a modern meets the beach flare that will have you feeling relaxed and pampered. View the Ko'olau mountains from the bedroom and the main living area.
Registration Number: 420200410000, TA-071-652-3520-01, GE-071-652-3520-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Make your trip to Maui more memorable with a stay at our 5 star condo at Luana Kai. Luana Kai is a beautiful resort with immaculate grounds in a park like setting, ocean front in sunny Kihei with no busy roads to cross to get to the ocean.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
This is an end unit on the first floor at the back of the resort complex. You can enjoy the lush gardens and tropical breezes from the spacious lanai. The newly renovated condo has a master bedroom that includes a king size bed. The main living space contains a wall bed with a queen size Tuft and Needle mattress.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
The Artists House, Permit # STLA 2017/0002 is a home away from home with the comfortable furnishings and great amenities. The fabulous Lana'i sunsets can be enjoyed outside on the deck where there is glass dining room table with 6 chairs and bbq to enjoy.
3 bedroom
3 bathroom
Sleeps 4
Hawaii> Lanai
We have a dedicated housekeeping team which details the condo before every arrival. After the detailing is complete, we sanitize the condo and the lanai before your arrival. Honolulu Airport Club Lounge: We are excited to announce that our guests have the privilege to access the Ko' Olina Club Lounge at Honolulu Airport when arriving or when departing.
Registration Number: TA-134-204-4160-01, GE-134-204-4160-01, 1910570090191
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Emmalani court is a small, quiet complex in Princeville, near the Princeville Resort. After visiting the 4 main islands in Hawaii, we decided on the North shore for it's lush setting and laid back vibe. There is truly no other place like it in the world.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Sunset in Paradise is your private paradise with gorgeous ocean views straight from a postcard. Relax on the lanai listening to the crashing waves with a cocktail while you enjoy another sunset, or sip your morning coffee while you watch the cruise ship come in.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to Unit 1604 at the Fairways at Mauna Lani! This is the largest 1646 SF 2 bedroom floor plan where both bedrooms are located upstairs providing a peaceful sleeping area separate from the living area downstairs. They each have their own full en suite bathrooms so it is perfect for two couples!
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimea
We are located on the west side of the Big Island of Hawaii, directly above Kailua-Kona, on the side of Mt Hualalai, in Holualoa, with an awesome ocean and coastline view! Our custom built studio cottage is about 500 sq. feet with a roomy wide lanai, and special bistro high seating to enjoy the fabulous ocean view.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Holualoa
Hale Lanui is located across the street from the ocean and has sweeping unobstructed views of the ocean and sunsets. This two story 2700 SF home is located on a residential street and has a swimming pool (lots of beach towels) and Jacuzzi for total enjoyment.
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kihei
Our comfortable, fourth-floor, south shore condo is right ON the ocean so you will SEE --and HEAR-- the sound of the surf as soon as you open your front door. The condo, with stylish, contemporary furniture, boasts a comfortable king-size bed. Relax "at home" and enjoy the ocean while sitting inside or out on your lanai.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> poipu
This breathtaking 3 Bedroom plus bunkroom, 2 bath single level home is located just minutes from Kailua, Kona and some of the most beautiful beaches in Hawaii. Everything in this house is new! Fresh full remodel just completed. This includes a brand new gourmet kitchen, stocked with everything you will need to prepare great meals, should you choose to do so.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Welcome to Unit 1602 at the Fairways at Mauna Lani! This luxurious gated community is located between the Fairmont Orchid and Mauna Lani Bay Hotel. This is a bright and open unit in a park-like setting. Feels like your own backyard. Easy access to the pool.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waimea
Our Honu Villa in the Villages at Mauna Lani is a private one-level Hawaiian Kahili home filled with everything you need for a Hawaiian vacation including your own pool and spa, and nearby beach! We are members of the Mauna Lani Resort Golf Courses and have substantial discounts for our guests for both the north and south courses, (up to 30% off pp/per round) and tee time bookings 60 days in advance.
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Hosted by VacaMaui We chose Milowai in Maalaea due to it's fantastic convenient and direct oceanfront location. The unit is on the ground floor directly on the ocean and is literally steps to the water.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuke
Our unit is spacious with high ceilings, ceiling fans, fabulous sunlight, and gorgeous ocean views. This elevated third story unit affords a wonderful lanai with a view overlooking manicured golf courses, tropical flowers, natural lava formations, and lush palm trees all against a beautiful ocean blue backdrop.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Hale Mala Pua (Garden Home) is the perfect place for family and friends to gather or for a romantic getaway on the stunning Hamakua Coast. This spacious plantation house (2,200 square feet) is located just north of Hilo in the lush and tropical Gardens at Botanical World Adventures in Hakalau.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Hakalau
Welcome to Hale Mahina A-302! Our favorite thing about this condo is the VIEW! Unobstructed views of the islands of Molokai and Lanai, with spectacular sunsets year-round. We are located on the Pailolo channel, a popular pathway for humpback whales on their migration.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
In Hawaii's lush tropics, discover your dream home at this stunning 2-bedroom retreat. Nestled just an 11-minute walk from the picturesque Maalaea Harbor, you'll be perfectly positioned to explore the stunning West Maui Mountains and the pristine Wailea Beach, just a 30-minute drive away.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Welcome to the Poipu Beach House. IG @poipubeachhouse. A beautiful 1920s-era plantation home steps from Poipu Beach. This home is a historical gem that takes you back to old Hawaii with original wood floors, lava rock footings, and 100+ year old dry-stacked lava rock boundary wall.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
It has been fully renovated with beautiful furnishings inside and out. It has lots of windows that lead out to a spacious patio. It has central air if the cool Hawaiian breeze is not enough. It has multiple barbecues just steps away for a relaxing dinner after a day of fun.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> waikoloa village
Kauai Haven is nestled within a classic Hawaiian style backdrop, resting on the edge of a bluff overlooking the spectacular Pacific Ocean. An award-winning designer, who is part of the Coastal Living Network of designers, has designed this exquisite condo that offers comfortable elegance along with breathtaking ocean views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Your Island Paradise Awaits You! Feel right at home in our spacious 1700 square foot, 3 bedroom, 2 1/2 bath Villa with an attached garage. This beautifully decorated Villa is just steps away from the resort style pool featuring a lava rock waterfall, Jacuzzi spa, and 4 BBQ's for grilling your favorite local island fish.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Simply put, this property sits on one of the most gorgeous ocean-front locations in East Hawaii. Unlike other ocean-front homes, you will not need to walk to the end of a yard to appreciate the Pacific waves. Here, you will feel as if you are on a ship.
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Hilo
. This top floor, 2 bedroom and 2 bath condo is nestled on the Ocean Bluff with both Tropical Garden and Ocean Views. This Condo is a SECRET GEM offering High End COMFORT in Paradise. Some custom interior details include: Hawaiian Inspired Furniture, New Beds and Elegant Bedding, Travertine Flooring, Flat Screen TV in Living Room and both Bedrooms, Bose Bluetooth Speaker.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
5-bath home featuring beautiful ocean views and central air conditioning. Prime Location Enjoy the convenience of a ten-minute walk to downtown Paia and its beautiful beaches. Within just a two-minute stroll, you can reach Island Fresh Cafe and Maui Yoga studio.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Paia
Puako Pau Hana is a great place to escape from all your cares! Puako Pau Hana has a great room design, which flows the dining & living room, then beyond to the expansive covered lanai. The split floor plan features two spacious bedrooms, with a king bed in the primary suite and another king bed in bedroom 2.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waimea
Paradise inside and out! The elegant B314 is an elevator ride up to the third floor of the Maui Sunset, where you'll enjoy breathtaking ocean views right outside your window. The warm Hawaiian shore nestles directly against the Maui Sunset greenbelt, with beautiful landscaping, and the largest pool in Kihei.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Our beautiful One Bedroom, 2 Bathroom Corner Unit is extremely rare! Located on the 5th floor of the Aloha Tower featuring breathtaking panoramic ocean views from the Living Room, Open Kitchen and the Bedroom. Resort amenities include Castaways Restaurant, 2 tropical pools, complete with umbrellas and Jacuzzi, a kid’s pool and BBQ grills.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Video and virtual tour from 2020. m=bcVD3s5F2LV We want to welcome you to our Kihei Garden Oasis, a tropical garden paradise in sunny Kihei, Maui. Just pack your swimsuit and leave the rest to us. Staying in a house with relaxing outdoor spaces is truly one of the best ways to share memories with family and friends.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Kihei
Indulge in an extraordinary couples' getaway at our top floor ocean and beachfront studio condo in Kapa'a. Just 150 steps from our condo's front door, the beach awaits! Designed to cater to the discerning needs of nature lovers, our condo provides a cozy home base after a day of exploring the wonders of Kauai.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
If you come to Maui for the warm breezes that seem to kiss your skin, then this unit -#203 - is just waiting for you. The trade winds that love our area keep you cool and comfy. Not that you need it that much but there is AIR CONDITIONING - and it sure is nice to have when the trade winds take their own vacation!
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Halii kai is an Ocean front Resort in the beautiful Waikoloa Beach Resort. What sets 16B apart from the rest? Location, amenities and service. I live on Island and can assist with any issues or questions. Halii Kai Unit 16B is right across from the clubhouse.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa
Welcome to Hale Leilani (Heavenly Home). We love design and creating a feeling of being on a tropical island with natural materials, beautiful art, and an airy, open environment. Our serene Kauai retreat has an unbeatable location. It's serene and quiet yet central.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
HIGHLIGHTS: • Over 3100 square feet of space • Quiet residential neighborhood on the Makai Golf Course at Princeville • Beach and snorkel gear • Close to grocery store and restaurants • Game Room with ping pong, games, bikes • Lots of indoor and outdoor areas to gather or alone time • Air conditioning (mini splits) installed in master bedroom and upper living area (living room/dining room), 4 bedrooms *THE SPACE* BEDROOMS: Four large bedrooms with ac (1 king, 3 queens) with ample space to store your belongings.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Princeville
Indulge in an extraordinary couples' getaway at our top-floor ocean view studio condo in Kapa'a. Just 160 steps from our condo's front door, the beach awaits! Designed to cater to the discerning needs of nature lovers, our condo provides a cozy home base after a day of exploring the wonders of Kauai.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Unit 162. Update: Fall of 2022 we installed a Grohe hand-held shower head so that parents could more easily wash their children. In the summer of 2021 we added new artwork. In 2019 we installed a new 55” Samsung LED LCD Smart TV! It’s beautiful and has “Dynamic Crystal color”!
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to this wonderful 3 bedroom / 2 bathroom duplex home only a 3 min walk from Magic Sands Beach. Just remodeled and professionally decorated, this charming vacation getaway has all of the comforts you desire in your home away from home, including air conditioning!
4 bedroom
2 bathroom
Sleeps 7
Hawaii> Kailua-Kona
We purchased this lovely ground floor garden view condo in April, 2013. This condominium has a large master bedroom with A/C. The master bedroom has an an en-suite master bath with a walk in shower. Our home also has a half bath for your convenience.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Koloa