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 15200, 50
Aloha! This spacious 1-bedroom timeshare condo is perfect for up to 4 guests seeking a relaxing Kauai getaway during specific weeks: May 17-24 or August 31-September 7. Located on the first or second floor of a charming 3-story building, the condo features a comfortable living area of 917 sq.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
The Honolulu Landmark Condo-Hotel offers an ideal blend of comfort, style, and Hawaiian charm, nestled conveniently between Waikiki Beach, the Hawaii Convention Center, and Ala Moana Beach Park.Registration Number: 766456789012, GE-654-456-7811-01, TA-321-546-8190-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this inviting 2nd floor condo in Hawaii, offering a spacious 1200 sq ft layout with a private balcony. The open kitchen features granite counters, stainless steel appliances including a fridge, stove/oven, microwave, and dishwasher, along with cookware and tableware for your convenience.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Indulge in the luxurious comfort of the two-bedroom bungalow at The Lodge at Kukui'ula on Kauai, offering privileged access to top-tier resort amenities. Situated along Kauai's southern coast, this exclusive enclave features an 18-hole Tom Weiskopf-designed golf course, a sprawling spa and wellness complex, a 10-acre organic farm, and inviting saline pools.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Discover this spacious 1-bedroom condo offering partial ocean views in Hawaii. The condo features a full kitchen with refrigerator, stove/oven, microwave, dishwasher, cookware, and tableware, making it ideal for preparing your favorite meals. Choose between a king bed or two queen beds, and enjoy the comfort of a queen sofa sleeper in the living room.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this beautifully remodeled luxury condo in Waikiki, offering breathtaking panoramic ocean views from a high floor. Located just a 5-minute walk to the iconic Waikiki Beach, this fully furnished unit combines comfort and convenience for an unforgettable Hawaiian escape.Registration Number: GE-007-807-7952-01, TA-007-807-7952-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikiki
Welcome to this charming 1-bedroom, 2-bathroom air-conditioned suite perfectly situated across from the popular Kamaole II Beach Park. This homey retreat features tasteful etched wood furniture and a private lanai, creating a cozy yet elegant atmosphere for your Maui vacation.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Papakea C209, your serene home away from home on Maui! This charming 1-bedroom, 1-bathroom garden-view condo comfortably sleeps up to 4 guests, featuring a plush king-sized bed in the bedroom and a queen sleeper sofa in the living area—perfect for couples, small families, or friends traveling together.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
5-bath condo offering stunning ocean and golf course views. This ground-floor residence spans 1,509 square feet and provides a seamless blend of luxury, comfort, and natural beauty. Inside, you'll find a fully equipped modern kitchen with granite countertops, ideal for preparing delicious meals.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kamuela
Come for the beaches, stay for the adventure! This listing features three unique hotel rooms within a family-friendly property in Waikiki, offering comfort and convenience for your getaway.Registration Number: GE-107-900-1088-01, TA-107-900-1088-01
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Kiahuna Plantation Unit #13 is a beautifully situated, top-floor corner condo located in Building 3 of the renowned Kiahuna Plantation Resort on Kauai’s sunny south shore.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Escape to the serene shores of Waikiki at The Tradewinds Plaza Suite 606, a welcoming 1-bedroom, 1-bathroom vacation retreat offering 521 square feet of comfortable living space. The unit features a spacious living room equipped with a pull-out couch, Smart TV with cable, a 5-piece dining set, a dedicated workstation, and air conditioning, ensuring a relaxing and productive stay. Registration Number: GE-084-761-0880-01, TA-084-761-0880-01, 260210490170
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Aloha! Welcome to your perfect Maui retreat, just steps from sparkling shores and offering direct beach access. This charming one-bedroom, one-bath apartment is your private oasis on the island. Wake up to the soothing sounds of waves and enjoy the tropical breeze from your window, with breathtaking views of the azure Pacific Ocean.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Wailuku
Discover this charming 8th-floor condo in Hawaii, offering a spacious lanai perfect for relaxing and enjoying island views. The living area features ceramic tile flooring, a cozy bedroom with plush carpeting, and modern comforts. The updated open kitchen boasts granite countertops and stainless steel appliances, including a fridge, stove, microwave, and dishwasher, making meal prep convenient and enjoyable.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Hawaiian getaway at Poipu Shores Ohana, where two beautifully appointed ground-floor condos, 101A and 102A, offer spectacular ocean views and easy access to the beach. Nestled along Kauai’s scenic southern coast, these condos combine modern comforts with natural beauty, making them perfect for families or groups seeking a luxurious yet relaxed vacation.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Koloa
Discover this charming ground-floor condo in Poipu, offering direct access to an oceanfront lawn and stunning views. The condo features a spacious 880 sqft layout with a comfortable bedroom equipped with a king bed, and a versatile queen sofa bed in the living area, perfect for families or groups.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the perfect getaway in this spacious 758 sq ft Waikiki Beachfront hotel room, featuring a king bed, sofa bed, and exclusive club lounge access. Wake up to stunning ocean views and enjoy the luxury of daily breakfast, housekeeping, and all necessary linens and toiletries. Registration Number: TA-113-154-4576-01, 260040100000, GE-113-154-4576-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the epitome of Hawaiian luxury at Westin Kaanapali Ocean Resort Villas, perched on 26 lush acres with stunning views of the Pacific Ocean and neighboring islands Lana'i and Moloka'i. These beautifully appointed villas include studio, one-bedroom, and two-bedroom options, all featuring fully furnished kitchens, signature Heavenly Beds, luxurious Heavenly Baths with whirlpool tubs and showers, in-room washer and dryer, and high-end amenities like Bose stereo systems and flat-screen TVs.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Maui escape in this stylish corner suite at Honua Kai Resort. Located on the 8th floor, Konea 820 offers breathtaking views of the West Maui Mountains and glimpses of the Pacific Ocean from its expansive balcony. The spacious interior comfortably hosts up to six guests with two king-sized beds and a queen sleeper sofa.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the comfort of the CW Royal Garden at Waikiki in this well-appointed King Balcony Studio. Spanning approximately 311 to 381 square feet, this unit offers a cozy retreat with a private balcony to enjoy the island breeze. The studio features a plush king bed, a partial kitchen for convenient meal prep, and a modern bathroom with a hairdryer and in-room safe. Registration Number: GE-068-663-8592-01, 260160390000, TA-068-663-8592-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the beauty of Keauhou at this charming garden retreat, perfectly situated near the harbor and shopping center. The property features breathtaking ocean vistas, spacious lanais, and all the comforts needed for a relaxing getaway.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Get ready to relax in this 1-bedroom condo in Maunaloa. The bedroom features a cozy king bed, perfect for a good night's sleep. This enchanting property offers a washing machine, iron, hair dryer, and WiFi for your convenience. You're sure to make fantastic memories with a stay at our place.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Maunaloa
Experience the comfort and convenience of your own space at Wailea Grand Champions Villas. Located in the heart of Wailea, this spacious condo offers an open floor plan with a living area that seamlessly connects to a covered lanai. Step outside to enjoy outdoor dining and entertain amidst lush tropical surroundings, with stunning ocean views and breathtaking sunsets.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea-Makena
Discover this beautifully remodeled luxury condominium offering panoramic ocean views in the heart of Waikiki. Located just a 5-minute walk to Waikiki Beach, this high-floor unit provides sweeping vistas and a comfortable, modern space for your stay.Registration Number: TA-014-569-5744-01, GE-014-569-5744-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Welcome to this beautifully remodeled one-bedroom, two-bathroom suite at Maui Bayan Resort, blending modern comforts with a cozy island vibe. The spacious living area opens onto a private lanai, offering stunning views of Maui's lush terrain — perfect for relaxing mornings or sunset evenings.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Come and enjoy a charming Hawaiian vacation at Kamapua'a Hale, an artsy and eclectic retreat nestled in the prestigious Princeville community. Surrounded by breathtaking mountain peaks of Hihimanu, Namolokama, and Mamalohoa, as well as sweeping ocean vistas, this home offers the perfect blend of luxury, space, and privacy for families or small groups.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Princeville
Immerse yourself in the breathtaking beauty of Mauna Lani Point H202, where 180-degree panoramic views of lush golf course fairways, dramatic black lava fields, and the expansive blue ocean greet you from every angle. Situated on the second floor, this elevated condo offers unobstructed vistas from your private lanai lounge, making it the perfect spot for morning coffees or sunset cocktails.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Escape to Puako Hale Hau Oli, a charming Hawaiian beach cottage nestled in the heart of Puako on the Big Island. This classic coastal retreat offers peek-a-boo ocean views and is just steps from stunning tide pools, swimming holes, and white sand coves, perfect for exploring Hawaii's natural beauty.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
the gorgeous river front property has multiple waterfalls, a secluded river beach and a lava tube/cave surrounded by lush jungle. Your group will have the whole property to yourself, with a house and a separate cabin so your group can have plenty of space to comfortably relax and unwind.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Ninole
Discover this inviting 2-bedroom home in Kailua-Kona, ideal for a relaxing getaway. The residence offers a comfortable king bed and a cozy queen bed, perfect for families or couples seeking a peaceful retreat.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover this spacious 1-bedroom condo nestled in the tranquil Lawai Beach neighborhood on Kauai's southern coast. The residence features a comfortable king-size bed in the bedroom, perfect for restful nights after days filled with adventure or relaxation.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience this beautifully remodeled corner unit in the heart of Waikiki, offering ample natural sunlight and modern comforts. The condo features a spacious bedroom with a memory foam queen sleeper sofa, a full kitchen with dishwasher, and a private washer and dryer for your convenience. Registration Number: GE-208-250-3168-01, TA-208-250-3168-01, 260170570048
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
vrKCV - Waikoloa Colony Villas 801, Professionally Managed by Kona Coast Vacations
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa
Experience a relaxing family-friendly stay at our modern beachfront hotel, featuring four separate units with private balconies and partial ocean views. Each 312 sq ft unit offers two double beds, linens, towels, a coffee maker, and all essentials, ensuring a hassle-free getaway. Registration Number: 260270350000, GE-025-315-0208-01, TA-025-315-0208-01
4 bedroom
4 bathroom
Sleeps 16
Hawaii> Honolulu
Experience the beauty of Maui at Marriott Ocean Club, situated on the stunning Ka'anapali Beach. This listing features two separate 2BR/2BA villas, perfect for families or groups seeking comfort and convenience. Each villa offers a master bedroom with a king-size bed, a second bedroom with either a king bed and sofa bed or two double beds, a spacious living area with a sofa bed, and a fully equipped kitchen including a range, microwave, refrigerator, dishwasher, coffee maker, blender, toaster, and dinnerware.
4 bedroom
4 bathroom
Sleeps 16
Hawaii> Lahaina
Your adventure begins with our unique 2-in-1 experience on Big Island! Cruise in the island’s only outfitted FJ Cruiser, equipped with 4WD, a lift kit, and 33-inch wheels, perfect for exploring diverse terrains. After booking, you'll receive a handcrafted guidebook highlighting top camping spots, hikes, local food, and excursions, making your trip seamless and memorable.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Kailua-Kona
Summary:
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Waimea
Island Escape is a beautiful 7-bedroom home ideal for large families or groups seeking comfort and convenience in Hawaii. Situated near famous attractions like Kualoa Ranch and the Polynesian Cultural Center, this property offers easy access to Oahu's stunning beaches and vibrant cultural scene.Registration Number: 615091610000, GE-071-386-1503-01, TA-057-319-2683-01
8 bedroom
5 bathroom
Sleeps 25
Hawaii> Hauula
Welcome to Hawaii! Your tropical getaway begins at Plumeria Hale, a charming beachfront 2-bedroom condo nestled in White Sands Village on the west coast of the Big Island. This bright, airy retreat offers partial ocean views and all the comforts you need for a memorable stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Escape to the Westin Kaanapali Ocean Resort Villas, a premier beachfront destination on Maui perched on 26 lush acres with breathtaking views of the Pacific Ocean and neighboring islands Lana'i and Moloka'i. Guests enjoy significant savings of 30-70% compared to direct bookings, with the comfort of units managed and maintained by the resort.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a comfortable and spacious stay in this 1730 sqft, 3-bedroom, 3-bath condo located on the first floor for easy access. The unit features a modern kitchen with granite countertops, a ceramic cooktop, oven, refrigerator, microwave, dishwasher, coffeemaker, toaster, rice cooker, blender, cookware, and tableware, making it ideal for preparing meals during your vacation.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa
Enjoy this first-floor end unit condo in Poipu with a spacious lanai offering beautiful views of the twin bridges. The bright and airy space exudes a contemporary tropical vibe, ideal for relaxing and unwinding. Tile flooring throughout enhances the cool, breezy atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Summary:
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua-Kona
Wake up to breathtaking mountain and waterfall views from multiple lanais at Makana Breeze, a luxurious two-level townhome in Princeville. This spacious 3-bedroom property offers a perfect blend of comfort and natural beauty, making it an ideal retreat for families, friends, or couples seeking a memorable Kauai experience.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Princeville
Step into this expansive nearly 5,000-square-foot luxury villa nestled in the gated enclave of Pauoa Beach. Designed with comfort and flexibility in mind, the residence offers five spacious suites, including four King Suites, one with a cozy sitting area that doubles as an extra sleeping space with a pullout sofa, and an oversized media room that can serve as a children's bedroom with a full bath.
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Waimea
Welcome to Kon Tiki 234, your beautifully renovated studio nestled in the vibrant heart of Waikiki. Located at 235 Liliuokalani Ave #234, this charming space offers the perfect blend of comfort and convenience for a 30-day minimum stay offered by Hawaii Dream Realty LLC.Registration Number: TA-179-740-0576-01, GE-179-740-0576-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover this comfortable 6th-floor condo at Horizon Bay, featuring a private balcony with ocean views, vinyl plank flooring throughout, and modern amenities. The spacious 1441 sqft layout includes a master bedroom with a king bed, a guest bedroom with two twin beds, and a sofa bed in the living room, accommodating up to 6 guests including children and infants.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui getaway in this premium oceanfront center studio, perfect for families or couples. The unit boasts a luxurious king-sized Westin Heavenly bed and a sofa sleeper in the living area, providing comfort and flexibility for guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this charming 2nd-floor condo situated on the west side of Hawaii, conveniently close to elevators and parking. The spacious 850-925 sqft unit offers a comfortable retreat with modern amenities and stunning views from the sundeck and covered lanai, which spans 154 sqft.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
