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 6600, 50
Welcome to your perfect tropical retreat! Our luxurious villa offers breathtaking views of four pristine lagoons right outside your door, inviting you to relax or dive into refreshing waters. Inside, plush sofas create a cozy space for reading or sharing joyful moments with loved ones. Registration Number: GE-050-267-5968-01, 910560130121, TA-050-267-5968-01
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Kapolei
5-bath townhouse located in Waikoloa Colony Villas, situated just off the 9th hole of Waikoloa Beach Nine. This private unit offers a perfect blend of comfort and convenience, with a location set back from the main drive, adjacent to the golf course, and just a minute's walk to the pool and barbecue area.
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Waikoloa,
Experience the beauty of Kauai from this charming third-floor penthouse condo with breathtaking ocean views from the living room and large private lanai. Situated within the exclusive Kaha Lani Resort, known as "Heavenly Place," this Bali-style retreat offers a serene escape surrounded by lush tropical gardens, oceanfront serenity, and proximity to Lydgate Park and Wailua Golf Course.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailua
Experience the ultimate Kauai getaway at Hale Hapuna, a sprawling 5,000 sq ft luxury estate perfect for large families or groups of friends. This home is perched at the highest point in Poipu, atop the extinct crater rim, offering breathtaking panoramic views of the ocean, mountains, and coastline.
6 bedroom
7 bathroom
Sleeps 16
Hawaii> Koloa
Welcome to Kai Nui, a beautifully refreshed 2,800 sq. ft. home nestled within the Lanai Villas of Poipu Kai Resort. Perfectly situated for beach lovers and resort explorers, this stylish retreat offers a peaceful ambiance enhanced by the soothing sounds of distant surf.
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Koloa
This beautifully remodeled Deluxe 1-bedroom condo at Casa De Emdeko offers a perfect Hawaiian escape. Located in the heart of Kailua-Kona, this property combines tropical charm with modern luxury.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the ultimate Maui escape in the ocean view studio at Hana Kai-Maui Resort, perfectly situated just 100 feet from the water's edge. This ground-floor unit in the Mauka building offers a cozy queen-size bed, a spacious covered lanai with a small dining table and comfortable chairs, and breathtaking ocean vistas.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
Discover Hokulani 108, a beautifully upgraded ground-floor condo at Honua Kai Resort in Maui. This one-bedroom, one-bathroom unit offers a perfect blend of comfort and luxury, with porcelain tile flooring throughout and modern furnishings. The spacious living area features a queen-size pullout sofa, smart TV, and sliding bi-fold doors that open onto a large lanai, ideal for outdoor dining and relaxing in the Hawaiian breeze.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Experience the ultimate Hawaiian getaway in this beautiful third-floor villa at Waikoloa Beach Villas, located within the scenic Waikoloa Beach Resort on the Big Island of Hawaii. This spacious two-bedroom, two-bathroom condo comfortably accommodates four guests, featuring a luxurious king-sized bed in the master suite and a cozy queen in the guest room.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
" This luxurious vacation rental on the Island of Hawaii is just two miles from the beach and centrally located near Kailua-Kona town. Inside, the spacious and stylish interior offers stunning panoramic ocean views visible from nearly every corner of the living room, which is furnished with plush seating and a neutral palette accented with Celedon and blue tones that evoke a relaxing vacation vibe.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua Kona
Welcome to Honu House, your perfect Hawaiian retreat located in KaMilo at Mauna Lani Resort on the stunning Kohala Coast. This spacious Makani floor plan offers breathtaking panoramic views of the Kohala Mountains descending into the ocean, with Maui visible in the distance, creating a truly picturesque setting.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
3 miles from Kona town. This vibrant community features lush tropical grounds, a saltwater oceanfront pool and spa, a sunny lounge area, two workout rooms, and designated eating areas with gas BBQs. Tennis courts, ping pong tables, ample parking, and nightly gated security enhance your stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Experience the perfect Maui getaway at Maui Kamaole J118, a spacious and luxurious condo located in the heart of Kihei. This beautifully furnished retreat offers a blend of privacy and comfort, just minutes from the renowned Kamaole beaches. The condo features a lovely view from the spacious lanai, ideal for relaxing and soaking in Hawaiian scenery.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience unparalleled oceanfront living in this stunning penthouse at the Valley Isle Resort, Kahana. Residence #804, offered by Hawaii Life Real Esatate Services, boasts breathtaking, unobstructed views of the Pacific Ocean, Molokai, and Lanai islands from every room.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the charm of this beautifully appointed second-floor condo nestled on the 9th tee of Kona Country Club, offering stunning views of Keauhou Bay and the surrounding mountains. Floor-to-ceiling windows in every room flood the space with natural light and allow seamless views of ocean breezes and lush landscapes, creating an airy, inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Book with confidence this beautifully remodeled 2-bedroom, 2-bath condo located in the Grand Champions Villas of Wailea. Managed by PMI Maui, a local expert in vacation rentals, this spacious unit offers a perfect blend of comfort and convenience.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Wailea
Discover this tranquil 400-square-foot cottage nestled just minutes from the renowned Kailua Beach. The detached studio offers a restful retreat with a private entrance, parking, and access to a serene pool area. Inside, you'll find a full bath and a kitchenette equipped with a full-sized refrigerator, microwave, and toaster oven, ideal for extended stays of 30 days or more due to local zoning laws. Registration Number: 430190420000, GE-166-476-8000-01, TA-166-476-8000-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Welcome to Waiohuli Beach Hale D-123, a beautifully remodeled 1-bedroom, 1-bath condo situated on the ground floor right next to the pool in this oceanfront paradise. Contact us for seasonal discounts and special rates! This NON-SMOKING property offers a perfect blend of comfort and convenience, making it an ideal vacation spot in Maui.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Immerse yourself in the relaxed charm of this Kauaian-inspired mid-century modern home, designed to evoke feelings of coziness, nostalgia, and adventure. The property celebrates the craftsmanship of 60's Hawaii with handmade furniture, local art, and linens, creating a beautifully curated space that warmly welcomes guests.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Princeville
Escape to a tropical paradise in this charming 1-bedroom, 1-bathroom Kailua-Kona condo, ideally situated within walking distance to the heart of Kailua town. This fully furnished, top-floor corner unit offers a stunning birds-eye view of the ocean, coastline, and nearby mountains, providing the perfect backdrop for your Hawaii getaway.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Immerse yourself in the lush beauty of Kauai at Hale Ko'olau Treehouse, a unique vacation rental nestled among tropical foliage on the island's famed north shore. This charming, bright one-bedroom home offers a distinctive experience, perched among the treetops with stunning mountain views and all the comforts of home.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Haena
Experience the spacious comfort of this beautifully furnished top floor condo, designed to highlight its soaring vaulted ceilings and breathtaking views of Mauna Kea. Not all 2-bedroom units at the Fairways are alike—this "Resort" floor plan offers an extra 200 sq ft compared to the "Golf Villas," creating an expansive and inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Immerse yourself in the stunning beauty of Hawaii with this charming oceanfront condo at Sea Village in Kailua Kona. Located on the top floor, this unit offers spectacular 180-degree views of Kailua Bay, allowing you to wake up to the soothing sounds of waves, watch playful dolphins, and enjoy breathtaking sunsets from your private lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to Hale Makai Cottage #2, a beautiful and luxurious two-bedroom retreat nestled on Kauai's enchanting North Shore in the charming Haena area. This inviting cottage is ideal for families or groups of friends looking for a relaxing escape in paradise.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Kauai County
Escape to our beautifully remodeled 2 Bedroom 2 Bathroom Ali'i Kai 1 Condo, offering some of the best oceanfront views in Princeville. With nearly 1200 sq. feet of comfortable living space, this condo provides a perfect blend of luxury and natural beauty.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Princeville
Discover the perfect Maui getaway at Mana Kai Condo 607, situated directly on the pristine Keawakapu Beach. This spacious 2-bedroom, 2-bathroom unit offers spectacular ocean vistas from a large lanai, where you can often spot whales in winter and turtles year-round.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the ultimate Hawaiian retreat in this stunning private oceanfront home on Lyman's Bay, one of Kona's most beautiful and popular surf spots.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Escape to your personal paradise in this spacious ground-floor one-bedroom condo at Maui Kamaole. Featuring a private lanai with tranquil ocean views, this unit comfortably accommodates two guests with a cozy queen bed. The fully equipped kitchen allows for preparing delicious home-cooked meals or trying local island dishes.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
This beautifully maintained Maui vacation condo offers a legal and approved stay in a prime hotel zone, ensuring a safe and authentic Hawaiian experience. Located on the 5th floor of a well-kept resort, this two-bedroom, two-bathroom unit boasts unobstructed ocean views, modern amenities, and a warm aloha spirit that makes every guest feel at home.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Hale Hui Kai 309, a professionally cleaned, no-contact check-in/check-out condo located on the stunning Keawakapu Beach in Kihei. This charming beach bungalow-style complex offers a perfect blend of comfort, convenience, and natural beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Polynesian Shores 105, your ideal Maui retreat offering a cozy, ground-floor condo with stunning oceanfront views. Designed for comfort, this unit accommodates up to four guests with a plush king-sized bed, a queen sleeper sofa, and a convenient desk perfect for remote work or postcards.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Maui Ka'anapali Villas (MKV), a popular destination known for repeat guests returning year after year! This PRIME beachfront property is perfectly situated between the iconic "Black Rock" and Kahekili Beach Park, also known as Airport Beach, offering direct access to pristine white sands and crystal-clear waters.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the beauty of Kauai from this private, upgraded room at Kauai Beach Resort. Overlooking pools and the Pacific Ocean, this boutique hotel offers multiple amenities including large pools, jacuzzis, a water slide, and easy access to a sandy beach.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lihue
Discover the perfect Kauai getaway in this spacious 1300 ft2 townhouse located in the heart of Princeville Resort on Kauai's North Shore. This beautifully appointed home features two bedrooms, including an open loft-style master bedroom with a king-size bed and a downstairs bedroom with a queen-size bed, providing ample space for relaxation and comfort.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the ultimate Hawaiian retreat in this beautifully remodeled top-floor condo at Waikoloa Beach. Surrounded by tranquil koi ponds and water features at the grand entrance of the Bo Tree Tower, this spacious unit offers breathtaking tropical views of crashing ocean waves and lush mountains.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Escape to paradise at our luxurious oceanfront villa nestled in the serene Turtle Bay area of Oahu's North Shore. This exquisite 3-bedroom, 2-bathroom retreat offers a perfect blend of comfort and elegance, just steps from soft sands and gentle waves. Registration Number: TA-135-679-7952-01, 570010130028, GE-135-679-7952-01
4 bedroom
2 bathroom
Sleeps 9
Hawaii> Kahuku
Nestled along the stunning Maui coast where azure waves meet soft golden sands, the Westin Nanea Ocean Villas Kaanapali offers a premier 4-star experience with luxurious accommodations and authentic Hawaiian hospitality. T. Fleming Beach Park, making it an ideal base for exploring the island’s natural beauty.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Immerse yourself in the beauty of Princeville, Kauai, with this spacious and eco-friendly 22-acre luxury resort property. Located at the end of a quiet road on a scenic plateau, this top-floor condo offers breathtaking sunrise and sunset views from its lanais, overlooking lush tropical mountains, gardens, and the ocean.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Step into Kolea 13D and be greeted by expansive views of lush greenery and stylish, tropical decor. This bright and open condo invites you to relax in comfort, with a spacious great room featuring a cozy couch, a large smart TV with enhanced sound, and a dining area with seating for six.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa
Welcome to your Hawaiian paradise at Kona Ali'i! This recently upgraded 2-bedroom, 2-bath oceanfront condo offers a perfect blend of modern comfort and tropical charm. Located in the heart of Downtown Kailua-Kona along the renowned Ali'i Drive, this property provides direct access to pristine beaches and a variety of water activities, ensuring endless sun-soaked adventures.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Discover the perfect island escape at Lanikai #212, a spacious 1,416 sq. ft. condo nestled in the tranquil Lanikai Resort on Kauai's east coast. This private retreat offers partial ocean views from a peaceful lanai, ideal for enjoying your morning coffee while listening to the waves.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Experience the perfect Maui getaway in this upgraded ground-floor suite at Honua Kai, nestled within the highly desirable Konea Tower's interior courtyard. Covering 1315 square feet, this spacious two-bedroom residence offers seamless access to the pools, beach, and BBQ areas, making it ideal for families or groups seeking convenience and luxury.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to this charming 960 sq ft cottage located at the back of a lush 2-acre yard on the North Shore. Situated on the second floor above an open-air garage, this retreat offers a cozy, treehouse-like ambiance surrounded by trees that provide privacy and tranquility.Registration Number: 590050850000, TA-182-371-3280-01, GE-182-371-3280-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Haleiwa
Experience the comfort and elegance of this spacious 3-bedroom home located in Waikoloa Beach Resort. Situated on a large lot at Ainamalu, this property offers stunning views of Mauna Kea and Mauna Loa mountains, creating a picturesque backdrop for your stay.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa
Discover this spacious and recently updated 3-bedroom, 3-bath townhome nestled on the 10th tee of the renowned Francis H. I'i Brown South course at Mauna Lani. The property features two en-suite bedrooms upstairs and a third bedroom with an adjacent bath downstairs, all offering excellent ventilation and scenic views.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimea
Experience the tranquility of this beautiful oceanfront home located on Hawaii's scenic "Red Road," just a short walk from a black sand beach. The upstairs unit offers modern comforts in a natural tropical setting, with breathtaking views of the ocean that can be enjoyed from the private outdoor deck.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> P?hoa
Escape to this beautifully situated double A-frame residence in Puako, designed to maximize breathtaking views and privacy. The home comfortably accommodates six guests, making it perfect for family or friends seeking a serene seaside retreat.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Whether you seek an adventurous escape or a tranquil retreat to rejuvenate, this Kauai Beach Villas condo offers the perfect setting.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lihue
This luxurious 1-bedroom suite offers stunning partial ocean views from a large lanai and a spacious, modern interior furnished with upgraded decor. The southern exposure ensures plenty of morning sunshine and breathtaking Maui sunsets while keeping out northeast trade winds.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Pali Ke Kua 214 offers a luxurious and comfortable retreat on Kauai's north shore. This beautifully furnished condo features granite countertops, cherry wood cabinets, and redesigned bathrooms, creating a stylish and contemporary ambiance. Berber carpeting in the living room and bedrooms enhances the cozy, inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
