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 14000, 50
This charming condo at Kihei Akahi offers a perfect blend of privacy and tropical ambiance. Nestled amidst lush gardens, the unit features its own lanai where you can sip coffee or unwind with a glass of wine while enjoying the tranquil surroundings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this charming ground floor condo located centrally and less than a 2-minute walk to the beach. Fully remodeled with new furniture and appliances, it offers a cozy, modern space for your vacation. The condo features two ROKU TVs, beach equipment, books, games, and puzzles for entertainment.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover this tranquil studio apartment in Hawaii, ideal for exploring the Big Island. The unit offers a queen memory foam bed, modern waterproof flooring, and a fully equipped kitchenette. Enjoy amenities like Wi-Fi, a private bathroom, and a private entry door, ensuring comfort and privacy during your stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Experience the vibrant allure of Waikiki from this charming studio perched on the 23rd floor of the Hawaiian Monarch. This well-appointed unit offers breathtaking mountain and canal views, creating a perfect backdrop for your Hawaiian getaway. The studio comfortably sleeps two with a queen-sized bed and features modern amenities including air conditioning, a smart TV, WiFi, and cable service, ensuring a comfortable stay. Registration Number: TA-028-153-2928-01, 1260140320420, GE-028-153-2928-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to your ideal holiday at the Kuilima Golf Retreat, a beautifully maintained getaway just a short stroll from the stunning beaches of Turtle Bay. This inviting unit comfortably hosts up to four guests, featuring a spacious bedroom with a queen bed, a cozy sofa bed in the living room, and a modern bathroom. Registration Number: 1570010270154, GE-124-574-9248-02, TA-124-574-9248-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
This air-conditioned 818 sqft suite offers a warm welcome after a day of hiking and ziplining. Families and friends will enjoy the privacy, space, and comforts of a complete kitchen, furnished lanai, and spacious living area. The suite features ceiling fans, flat-screen TVs, DVD player, WiFi, and a washer/dryer.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> KIHEI
Experience a relaxing getaway in this beautifully renovated studio at Luana Waikiki, perfectly situated at the start of the famous Waikiki strip. The bright, hotel-style unit offers a private lanai with stunning city views, providing a serene spot to unwind after exploring the island.Registration Number: 1260060020084, GE-117-919-5392-01, TA-117-919-5392-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience stunning views of Diamond Head and Waikiki Beach from this cozy Hawaiian Monarch studio located on the 32nd floor. The unit offers all the essentials for a comfortable stay, including air conditioning, a flat-screen smart TV, Wi-Fi, and cable. Registration Number: 260140320069, TA-110-319-4112-01, GE-110-319-4112-02
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover this beautifully renovated and spacious three-bedroom retreat nestled in the heart of Princeville, Kauai's scenic North Shore. Perfectly positioned just minutes from Hanalei Bay, this private oasis offers a blend of luxury and comfort for families and friends alike.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Princeville
Experience the perfect blend of modern comfort and Hawaiian charm in this beautifully remodeled surf-chic studio at the Hawaiian Monarch. Located on the 12th floor, this unit offers stunning Ala Wai Canal and city views, making it an ideal base for exploring Waikiki and Honolulu. Registration Number: 260140320247, TA-114-763-5200-01, GE-114-763-5200-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
5-bath condo located on Kauai's stunning North Shore in Princeville. This airy and light-filled space offers a perfect retreat with breathtaking views of mountains and waterfalls from your private covered lanai. The condo's prime location places you just minutes from some of Kauai's most beautiful beaches, including Hanalei Bay, Queen's Bath, and Hideaway Beach, as well as scenic attractions like Turtle Cave and Secret Beach.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to Wailea Ekolu 1404, a beautifully appointed one-bedroom, two-bathroom vacation condo that offers an idyllic escape in the heart of Maui's popular Wailea area. This elevated second-floor residence boasts breathtaking ocean views and a peaceful ambiance, making it an ideal retreat for relaxation and adventure.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to your dream Hawaiian escape at Marriott's Ko Olina Beach Club, a luxurious ocean view 2-bedroom, 2-bath timeshare unit nestled within a premier resort in Oahu. This spacious villa comfortably accommodates up to six guests with two king beds, a queen sofa bed, and all the amenities you need for a relaxing and memorable vacation.Registration Number: GE-196-150-0672-04, 910570140000, TA-070-692-2496-03
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Escape to Kauai's premier vacation destination at Whalers Cove Resort, ranked as the top traveler choice on TripAdvisor. Nestled in the heart of Poipu, this secluded tropical retreat offers guests a perfect blend of comfort, luxury, and natural beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
5-bath townhome nestled within the exclusive Mauna Lani Resort. The property is tastefully furnished with elegant Tommy Bahama decor, creating a warm and inviting atmosphere.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
- 11th floor condo with balcony
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Discover the Ridge Villas, perched high above Kapalua Bay with breathtaking gold ocean views and overlooking the fairway of Kapalua Bay Course. This spacious 1-bedroom villa, ranging from 1200 to 1500 sqft, comfortably accommodates up to four guests, including children and infants.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the perfect Maui retreat at Papakea Resort with this charming 1-bedroom ground floor corner condo. Located just 5 miles north of Lahaina, this oceanfront property offers stunning ocean views, modern amenities, and easy accessibility. The bright, spacious unit comfortably sleeps four, featuring a king-sized bed, a sleeper sofa, and a fully equipped kitchen ideal for preparing delicious meals.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Ka'anapali Royal – Your Island Escape in the Heart of West Maui
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Lahaina
Experience the ultimate Maui getaway in this spacious lofted condo located just steps from the stunning Kamaole Beach I. This beautifully furnished unit offers breathtaking ocean views and unforgettable sunsets from a large, shaded lanai equipped with an electric awning.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Nanea Kai Villa by Gather is a luxurious four-bedroom, three-and-a-half-bath beachfront estate nestled along the peaceful shores of Hawaii Kai, Hawaii. This exquisite property sits on a spacious private lot with a meticulously manicured lawn leading directly to a sandy beach, offering a tranquil escape just a short drive from Waikiki's vibrant attractions. Registration Number: TA-026-671-3600-02, GE-026-671-3600-02, 680270240000
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Honolulu
Escape to paradise with this stunning two-bedroom villa located on the pristine sands of Kaanapali Beach. Opened in spring 2017, this resort offers 16 acres of lush grounds, spectacular scenery, and first-class amenities designed to provide ultimate relaxation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Wailea Elua 2007, a stunning 1-bedroom, 2-bath vacation retreat with gorgeous ocean views, a spacious private lanai, and upscale island furnishings throughout. Perfectly positioned within the prestigious Wailea Elua Village, this beautifully appointed condo blends comfort, style, and unbeatable location for the ultimate Maui getaway.
2 bedroom
2 bathroom
Sleeps 2
Hawaii> Kihei
Discover paradise in our charming 1-bedroom, 1-bathroom apartment nestled in the heart of Kihei, Hawaii. This fully equipped unit offers breathtaking ocean views from your living space, creating a tranquil and scenic retreat. Located just steps from pristine beaches, you can easily indulge in sunbathing, swimming, and water sports.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this spacious one-bedroom condo at Royal Kuhio in the heart of Waikiki, offering stunning partial ocean and mountain views from the 23rd floor. With 436 sq ft of living space plus an 80 sq ft lanai, this unit comfortably sleeps four with two full-size beds and a queen sofa bed, making it ideal for families, couples, or friends seeking a relaxing Hawaiian retreat.Registration Number: TA-105-232-1792-01, 1260200580212, GE-105-232-1792-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Puu Poa 306, a charming oceanfront condo offering breathtaking 180-degree views of the Pacific Ocean. This inviting two-bedroom, two-bathroom unit blends modern comforts with the relaxed charm of a family vacation retreat. The spacious living area features a fully stocked game room with a full-size pool table and a collection of hundreds of DVDs, ideal for unwinding after a day of adventure.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover this charming 2-bedroom unit in Waikiki, offering 1,000 sq ft of comfortable living space designed for relaxation and convenience. The condo features two spacious bedrooms, each with full-sized beds and ample storage, making it perfect for families or groups of up to 8 guests. Registration Number: TA-040-446-5152-01, GE-040-446-5152-01, 260100070007
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Hawaiian Botanical Themed waterfall room is a top floor queen bedroom with bench, has ensuite bathroom featuring tiled walk-in shower with regular head, rain shower head and handheld, double sinks & granite counter tops. The room also has a mini frig, coffee, microwave.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hilo
Enjoy a relaxing stay in this spacious 2nd floor Hawaii beach condo offering a private balcony and modern amenities. The condo features a comfortable king bed and a queen sofa bed, making it ideal for couples or small families. The 1200 sqft layout includes an updated kitchen with granite countertops, complete with a fridge, stove/oven, microwave, dishwasher, cookware, and tableware, perfect for preparing meals during your stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to paradise at the Maui Ocean Club located along the famous Kaanapali Beachwalk, just minutes from Whaler's Village shops and restaurants. This resort offers a vibrant, family-friendly atmosphere with luxurious amenities including multiple pools, a superpool with waterfalls, waterslides, and a pirate ship play area for kids.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this inviting Maui beachfront resort offering a variety of pools, including slides, alongside relaxing hot tubs and direct access to pristine beaches. The spacious two-bedroom suite features air conditioning, a fully equipped kitchen, a comfortable living area, and a balcony perfect for enjoying the tropical views.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Maluhia Beach House By Gather, your private oceanfront oasis on Oahu's North Shore. 5-bathroom home is nestled at the end of a quiet road, offering direct beach access and breathtaking views of the Pacific. The property combines coastal elegance with modern comfort, making it an ideal retreat for surfers, snorkelers, sunbathers, or anyone seeking tranquility amidst natural beauty.Registration Number: TA-020-234-2400-01, 1610040960000, GE-020-234-2400-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Haleiwa
Experience the breathtaking beauty of this majestic ocean-view retreat, featuring Air Conditioning, two luxurious king beds and two bathrooms. Nestled on the edge of an enchanting ocean bluff, you'll be greeted each morning by stunning sunrises that paint the sky with vibrant hues, while evenings invite you to gaze at the stars twinkling in the clear night sky.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience a charming second-floor end unit in Poipu featuring a private lanai overlooking twin bridges and the ocean. This bright and airy condo is decorated with tropical accents, creating a relaxed island atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Discover this inviting 3rd floor condo in Hawaii, offering a cozy retreat with a private balcony and modern amenities. The space features tile and vinyl plank flooring throughout, creating a fresh and durable environment. The updated island-style kitchen is fully equipped with a refrigerator, stove/oven, microwave, cookware, and tableware, making meal preparation a breeze.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Discover the perfect retreat in the heart of Waikiki with this charming studio apartment. Located just a 10-minute walk from the iconic Waikiki Beach, this unit offers convenient access to the area's best restaurants, shops, and attractions. Situated on the 12th floor of Marine Surf Waikiki, the studio features a bright and airy design with neutral tones and modern amenities.Registration Number: TA-010-914-9184-01, GE-010-914-9184-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Escape to the breathtaking beauty of the Big Island and indulge in a luxurious stay at this stunning 3-bedroom, 2-bathroom vacation rental home nestled in the heart of Kailua-Kona. This tropical paradise offers an open, spacious interior filled with all the comforts of home, making it an ideal retreat for families and groups of friends seeking both luxury and convenience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Enjoy a comfortable stay in this 11th-floor condo offering stunning views from the balcony. The updated open kitchen is equipped with granite counters, a stove, oven, stainless steel refrigerator, microwave, coffeemaker, toaster, blender, tea kettle, and complimentary tea.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this charming first-floor end unit condo in Poipu, offering stunning ocean views and a tropical ambiance. The bright and airy space features vinyl plank flooring throughout, creating a warm and inviting atmosphere. The fully equipped kitchen boasts granite countertops, a backsplash, full-size refrigerator, stovetop/oven, dishwasher, microwave, coffee maker, blender, toaster, cookware, and tableware, making meal preparation effortless.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
The property is situated in the heart of North Shore, offering stunning ocean views and a prime location across from Shark's Cove. The home provides easy access to the bike path leading to Log Cabins, Pipeline, and Rocky Point, making it perfect for outdoor enthusiasts. Registration Number: 1590110250000, GE-065-042-9952-01, TA-065-042-9952-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Haleiwa
Welcome to Hale O Noelani, a beautifully renovated 300 sq. ft. one-bedroom Ohana suite located on Hawaii's east side. This charming unit features a comfortable Queen-sized bed, a private entrance, and its own fenced yard, offering a perfect blend of privacy and comfort.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Pahoa
Your Honolulu vacation starts at this spacious 2-bedroom resort suite located at Wyndham Waikiki Beach Walk. The master bedroom features a king-size bed and an en-suite bathroom with a shower and jetted tub. The second bedroom offers two double beds with a full bathroom conveniently across the hall. Registration Number: GE-064-469-9648-01, TA-064-469-9648-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
This well-appointed condo in the heart of Waikiki provides everything needed for a relaxing and convenient stay in Hawaii. Featuring a comfortable queen-sized bed, a sofa, and a queen air mattress, the unit is perfect for both leisure and work.Registration Number: TA-049-791-5392-01, GE-049-791-5392-01, 260210490108
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Aloha! Welcome to your perfect Kona retreat where beauty, comfort, and quality come together. From your lanai, enjoy breathtaking ocean vistas, soothing sounds of surf, and the chance to spot dolphins and whales during season.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Experience the perfect Maui getaway with our cozy hotel room overlooking Kahului Bay. Wake up to stunning ocean views and the soothing sound of waves, creating an ideal retreat for relaxation. Located just steps from Kanaha Beach Park and Queen Kaahumanu Center, our property offers convenient access to Maui’s top attractions, beaches, and shopping districts.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kahului
Experience the perfect Hawaiian getaway at Maui Kaanapali Villas #409, a beautifully updated one-bedroom condo offering serene garden views and convenient beach access. This ground-floor unit features fresh, modern furnishings including a queen-size chaise sleeper sofa, contemporary lighting, and a fully equipped stainless-steel kitchen perfect for preparing family meals.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This spacious ground-level villa offers three bedrooms and three full bathrooms, ideally located just two buildings from the pool. It features two king-sized beds, two twin beds, and four wall-mounted TVs with Sonos speakers throughout, providing a perfect retreat for families or groups.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa
Experience the largest and only 3-bedroom, 3-bathroom luxury condo at Royal Sea Cliff, offering direct oceanfront access and stunning Pacific views. The open-concept design features soaring 9-foot ceilings, seamlessly connecting to an outdoor wet bar where you can enjoy tropical breezes.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Immerse yourself in the serenity of Kapalua Bay Villas, nestled amidst lush gardens and pristine beaches. These fully-equipped 1-bedroom villas offer breathtaking ocean views from your private lanai, perfect for unwinding and enjoying Maui’s stunning sunsets.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this spacious 1-bedroom condo located on the 23rd floor of the Royal Kuhio in the heart of Waikiki. Boasting partial ocean and mountain views, this 436 sq ft condo includes two full-size beds and a queen sofa bed, comfortably accommodating up to four guests. Registration Number: TA-105-232-1792-01, 1260200580213, GE-105-232-1792-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
