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`, a.`ai_description`,
(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 LENGTH(href) > 0 AND sleeps>=1 order by rating desc LIMIT 8950, 50
Welcome to Ho'okipa Hale by Gather, your ideal retreat in Kailua Kona for a relaxing Hawaiian vacation. This charming and spacious home is nestled on a large lot surrounded by lush tropical fruit trees, offering stunning ocean views and a serene environment perfect for unwinding.
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua Kona
Welcome to Nalani Palms, one of Princeville's premier vacation homes, offering a perfect blend of peace, privacy, and luxury for your dream getaway. This expansive five-bedroom, four-bath residence is designed to accommodate families or corporate groups seeking comfort and style.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Princeville
Welcome to Ha'upu Hale, a stunning, brand-new two-bedroom townhome nestled within the exclusive Pili Mai resort in beautiful Poipu, Kaua'i. This luxurious property offers over 1,200 square feet of thoughtfully designed living space, featuring plantation-style architecture, bamboo floors, and elegant Koa cabinets.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> koloa
Escape to paradise at Napili Ridge! This beautifully decorated 1-bedroom condo offers a perfect retreat for couples or small families. The bedroom features a comfortable queen-size bed, while the living room includes a cozy queen-size sofa sleeper, providing ample space to unwind after a day of exploring.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the tranquil beauty of Maui from Kaanapali Shores 849, a beautifully furnished 1-bedroom, 1-bath condo on the 8th floor. Designed with an open-concept layout, this Hawaiian-inspired retreat comfortably hosts up to 4 guests. The bedroom boasts a luxurious king-size bed with premium linens and a flat-screen TV for relaxing evenings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Palms at Wailea 1201, a beautifully furnished condo offering the perfect blend of comfort and luxury in a prime location. This 2-bedroom, 2-bath unit is ideal for families or couples seeking a relaxing Maui escape. The spacious living area features floor-to-ceiling sliding glass doors that open to a private lanai, creating seamless indoor/outdoor living space.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Experience the beauty of Kauai at Niulani Lanikai, a charming beachfront property in Kapa�a designed to accommodate up to 10 guests across three unique on-site homes. The main house exemplifies traditional Hawaiian architecture with Balinese-inspired lofted ceilings, creating a breezy, open atmosphere.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kapaa
Haneo Unit 104 offers a cozy and upscale ocean view experience in Hana, Maui. This ground-floor 1-bedroom, 1-bath condo features tropical decor, a king-size bed in the bedroom, and a queen hide-a-bed in the living room for added comfort. Ceramic tile floors, a tiled tub/shower, and granite kitchen countertops provide a modern touch.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hana
This beautifully appointed two-bedroom interior ground floor suite at Honua Kai offers a perfect blend of comfort and convenience. Featuring its own spacious lawn area and upgraded travertine flooring, this suite provides a tranquil retreat away from the trade winds, while still offering easy access to the resort's amenities.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This charming studio condo at Kaanapali Shores offers breathtaking views of Molokai and partial ocean vistas, perfect for romantic couples or small families seeking a relaxing getaway. Inside, you'll find central air conditioning, a fully equipped open kitchen featuring a large counter, a new range, dishwasher, microwave, and a 42-inch flat-screen TV with DVD player.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautifully upgraded oceanfront condo located in the desirable Kihei Surfside Resort on Maui. Situated on the fourth floor in the sought-after "2 stack," this unit offers one of the closest locations to the ocean among Kihei and Wailea condos.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Maui getaway in this beautifully upgraded 3-bedroom luxury villa at Honua Kai Resort & Spa. Located just 30 feet from the Pacific Ocean, Villa #204 offers breathtaking ocean and beach views from its second-floor location, providing a serene and picturesque setting for your vacation.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
This inviting two-bedroom condo features spectacular ocean and garden views, providing a relaxing and comfortable setting for your vacation. The master bedroom and downstairs bedroom each have a king bed, ensuring ample space for all guests. The spacious kitchen boasts plenty of cabinet space, perfect for preparing meals during your stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
This beautifully appointed, air-conditioned condo offers an exceptional stay in Hilo, perfectly situated near the town center while providing a tranquil Polynesian-inspired setting.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hilo
Experience a tranquil getaway in our beautifully maintained ocean view condominium, thoughtfully furnished for your comfort. The condo's bamboo floors create a bright, airy ambiance, enhancing the open, clean feel of the space. Located on the top floor at the end of the building, it offers spectacular views of both the ocean and mountains from the lanai, perfect for unwinding with breathtaking scenery.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Aloha! Welcome to our charming condo in the heart of Waimea, offering a perfect blend of comfort and convenience. Located within walking distance to the town’s best restaurants, cozy coffee shops, and the lively Saturday farmers market, this property provides an authentic Hawaiian experience.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimea
Discover the beauty of Napili Point in this beautifully remodeled 1-bedroom, 1-bath condo nestled on the renowned Napili Bay. With breathtaking ocean vistas, this unit offers a perfect blend of comfort and natural splendor. Relax on your covered lanai and watch the sunset over the coastline and twin islands, or in winter, catch a glimpse of breaching whales.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your ultimate Hawaiian retreat, where the natural beauty of Kauai unfolds in every breathtaking detail. This stunning Princeville condo offers a 180° panoramic ocean view of the Blue Pacific and Bali Hai, the island's most iconic vista.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to Hale Kai #101, a beautiful oceanfront condo in Maui that offers breathtaking views and direct access to the beach. This two-bedroom unit comfortably sleeps up to six guests, featuring a king bed, two twin beds, and a sleeper sofa. Fully remodeled and furnished in 2014, the condo combines modern comfort with island charm.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Pacific Shores B312, a charming Maui condo owned by Dean and Susan, who spend part of each year here. Located in Kihei, this property offers the perfect blend of comfort and convenience. The area boasts incredible beaches, a variety of restaurants, shops, and vibrant nightlife, making it one of the most desirable spots on the island.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover this spacious 2-bedroom garden view condo located in the charming town of Kapaa, Kauai. Perfectly situated within walking distance to local restaurants, boutique shops, and grocery stores, this condo offers convenience and comfort. Wake up early to catch breathtaking sunrises over the Pacific Ocean or spot migrating whales during the season.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapaa
Surrounded by vibrant tropical foliage, this inviting home provides a true island escape. Relax on the lanai with outdoor dining and a soak in the private hot tub after a day of adventure. Located less than 5 minutes from the nearest golf course and pristine beaches, the house comfortably accommodates eight guests across four spacious bedrooms, with an additional air mattress for two more guests.
4 bedroom
2 bathroom
Sleeps 10
Hawaii> Princeville
Discover Mahina Surf 207, a spacious and bright condo ideal for a family getaway on Maui. This inviting unit features stunning pool and ocean views from the furnished balcony, perfect for relaxing or dining alfresco. Inside, you'll find a fully equipped kitchen to brew Kona coffee in the morning or prepare full-course meals for loved ones.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the beauty of Kauai at Hanalei Colony Resort E1, a modern ocean view apartment located on the North Shore's only true beachfront resort. This charming downstairs corner condo offers stunning ocean vistas from large picture windows and a spacious, shady lanai perfect for relaxing while listening to the surf.
2 bedroom
2 bathroom
Sleeps 3
Hawaii> Hanalei
Step into this stunning ocean view condo at Kapalua Bay Villas 15G4, offering a perfect Maui retreat. The newly remodeled space features a full kitchen, private washer and dryer, and central air conditioning for your comfort. Relax on the private lanai with breathtaking views of the ocean and a glimpse of Molokai, or unwind by the resort’s pristine pool.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Located in the heart of South Kihei, Maui, this beautifully remodeled condo offers spectacular ocean and garden views. Recent upgrades in 2024 include new furnishings, an expanded kitchen with black stainless-steel appliances such as a refrigerator, microwave, electric cooktop with convection oven, and dishwasher, making it a chef's delight.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Aloha~Welcome to your little slice of paradise! We are proud new owners of Maui Vista Unit 2419, a beautifully renovated two-bedroom, two-bathroom loft condo located in the heart of Kihei. This long-time favorite vacation spot has been popular among travelers (please note, reviews and star ratings reset with new ownership).
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience breathtaking views of the Pacific Ocean, Diamond Head, and lush Kapiolani Park from this 29th-floor 1-bedroom condo at Waikiki Sunset. Completely renovated in 2021, the unit offers modern comfort with central AC (offline Jan 8–Feb 18, 2025) and free covered parking. Registration Number: 260280110312, 2013, TA-129-286-7584-01, GE-129-286-7584-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this beautifully remodeled ground floor condo at Kiahuna Plantation, ideally situated on the sunny south shore of Kauai. Located just steps from the great lawn and the sandy swimmable beach, this corner unit offers stunning ocean views and a tranquil tropical setting.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Escape to this enchanting Kauai cottage nestled amidst lush tropical foliage, offering a peaceful retreat just minutes from stunning north shore beaches and vibrant local attractions. This multi-level, charming home features a loft-style master suite with a private bathroom upstairs, and a second open bedroom with its own bathroom on a separate level, providing privacy and comfort for guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the perfect Kauai getaway at Poipu Shores 103A, a fully air-conditioned, professionally decorated oceanfront condominium featuring two bedrooms and two bathrooms. Recent upgrades include engineered-wood flooring, new appliances, a split-king bed, and a solid teak outdoor dining set on the lanai, making it an ideal retreat for relaxation and entertainment.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa/Poipu
Welcome to your ideal "home away from home" in the charming town of Waimea on the Big Island. This inviting 2-bedroom, 1-bath residence boasts abundant natural light, stylish tile and bamboo flooring, and a cozy JOTUL gas fireplace perfect for cool evenings.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimea
Experience the charm of Baby Beach Hale, a spacious 4,000-square-foot two-story vacation home located just 50 feet from the calm sands of Baby Beach in Poipu Kauai. Designed to accommodate large families or groups, this well-appointed property features a functional layout with a large covered lanai on the second floor overlooking the beach, perfect for relaxing and enjoying island breezes.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Koloa
Experience the perfect Hawaiian getaway at Turtle Paradise, a spacious Polynesian-style condo situated on the unspoiled southeastern coast of Hawaii's Big Island. This true one-bedroom, two-level condo offers a unique blend of comfort and natural beauty.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Welcome to your perfect Maui escape at Koa Resort, where comfort meets convenience. This spacious, ground-floor two-bedroom condo has been beautifully renovated to offer a modern, relaxing retreat just a short walk from South Maui's stunning beaches.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Kihei
Escape to this stunning 2-level condo located within the exclusive Kiahuna Resort on Kauai's sunny south shore. This spacious unit offers a full open floor plan, upgraded kitchen and bathrooms, and newer furnishings, providing all the comforts needed for a perfect vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Poipu Beach
Immerse yourself in the beauty of Hawaii from this top-floor, southwest-facing condo at Puako Beach Condominiums. Wake up to breathtaking ocean views and enjoy fresh Hawaiian breezes that keep the unit consistently cool.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kamuela
This beautifully maintained almost beachfront residence offers cozy comfort and spectacular ocean vistas from every room. The property sits on a private quarter-acre lot, fully fenced for privacy, providing an ideal setting for relaxation and enjoyment.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Welcome to The Ko'olau Plantation Estate, your ideal Hawaiian hideaway nestled on five lush acres at the start of Oahu's North Shore. This exquisite property offers over 60,000 square feet of outdoor space, including a spacious front lawn perfect for gatherings and celebrations. Registration Number: GE-079-352-4736-01, TA-079-352-4736-01
9 bedroom
9 bathroom
Sleeps 14
Hawaii> Kaaawa
Experience the perfect Maui retreat in this beautifully appointed one-bedroom, two-bathroom villa on the ground floor. The expansive living area seamlessly extends to a large lanai through sliding doors, increasing the space from 1022 to an impressive 1500 square feet.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover Hale O' Waikoloa, a serene cedar-built retreat nestled in the peaceful community of Waikoloa Village on Hawaii's Big Island. This 2,000 sq ft home offers a perfect blend of natural beauty and modern comfort, making it an ideal base for exploring the island's stunning beaches, volcanic parks, and vibrant culture.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Waikoloa
This charming ocean and pool view unit is perfectly situated in the heart of Kihei, offering tranquil mornings with views of Haleakala and stunning sunsets from your private lanai. The location is ideal, just across from Kamaole II Beach—one of Maui’s top swimming and snorkeling spots—only 200 yards away.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Experience the ultimate Maui escape at Mahina Surf 112, a charming oceanfront condo nestled in a serene, noise-sensitive area. This well-appointed residence offers breathtaking ocean views from its private patio, perfect for enjoying breakfast or lunch while soaking in the tranquil surroundings.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the best of Poipu Beach in this spacious 2-level condo at Kiahuna Plantation, the only beachfront resort on the area. The open floor plan features vaulted ceilings and spectacular ocean views from the lanai, creating a perfect setting for your tropical getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Aloha! Discover our expansive nearly 180-degree west-facing oceanfront condo nestled along a charming two-lane road filled with coffee shops, restaurants, and art galleries—perfect for exploring the Big Island. We welcome all guests and invite you to enjoy our family-owned getaway as much as we do.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Step into your paradise at Kona Oasis #1, a charming two-bedroom, two-bath ocean view condo nestled at the end of a quiet cul-de-sac in Kailua-Kona. This ground-floor unit offers a spacious 214 sq ft lanai, perfect for soaking up Kona's renowned sunsets while enjoying your meals or relaxing with a drink.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Welcome to Valley Isle 309, a beautifully appointed 1-bedroom, 1-bath condo offering stunning ocean views from your private lanai. The unit features a spacious king-size bed with plush linens, ensuring restful sleep after a day of adventure. The bathroom includes a large walk-in shower and ample storage, while the living room offers a queen sofa bed for additional guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to our charming North Shore duplex, nestled amidst lush preservation and agricultural land, offering a serene Hawaiian retreat. The lower half of a thoughtfully maintained house, this unit features breathtaking forest views and easy access to some of the island’s best beaches, renowned for snorkeling, surfing, and sunbathing. Registration Number: 590110670000, TA-047-903-5666-01, GE-047-903-5666-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Haleiwa
Discover this charming second-floor one-bedroom condo in Kihei, offering breathtaking tropical views from your lanai overlooking meandering lagoons and lush gardens. The condo is fully air-conditioned for your comfort, providing a cozy retreat after a day of exploring.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lihue
Escape to the sunny south of Kauai at Prince Kuhio Resort, where this charming second-floor condo offers breathtaking ocean and park vistas from its lanai. Located just steps from two excellent snorkeling beaches, the property provides easy access to the crystal-clear waters and vibrant marine life that Kauai is famous for.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Poipu
