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 3750, 50
Welcome to L308 at Papakea Resort, a highly sought-after one-bedroom corner unit offering breathtaking views of the sparkling Pacific Ocean. Perched on the third floor, this condo provides unobstructed 180-degree vistas on two sides, thanks to its unique corner layout and wraparound lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this charming 2-bedroom, 1-bathroom vacation home nestled in Waimanalo. Ideal for outdoor enthusiasts, it offers quick access to pristine beaches like Waimanalo Beach, just a 5-minute walk away. The home features a comfortable living space perfect for relaxing after a day exploring Hawaii's natural beauty. Registration Number: 410050460000, TA-076-575-7440-01, GE-076-575-7440-01
3 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
Immerse yourself in the beauty of Maui at Wailea Ekahi 3C, a spacious one-bedroom condo nestled within the lush 34-acre Ekahi Village. This inviting retreat offers a perfect blend of comfort and island charm, making it an ideal destination for your next vacation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Wailea
The Love Shack Beach House combines the comfort of a private home with the convenience of being close to Waikiki's top attractions. Located within walking distance to Waikiki Beach, Diamond Head, the zoo, Kapiolani Park, and numerous surf spots, it offers an ideal base for exploring Oahu without the hassle of renting a car.Registration Number: GE-120-679-4752-01, TA-120-679-4752-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Hawi Hideaway, a charming 2-bedroom, 1-bath home nestled in the scenic town of Hawi on the Big Island. Perfect for up to 6 guests, this cozy retreat offers a peaceful escape just a short walk from main street, where you can explore local shops, galleries, and the Saturday farmers market.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Hawi
Welcome to this stunning oceanview condo nestled within the boutique Napili Shores Resort on West Maui. Wake up to refreshing ocean breezes and breathtaking views from the lanai, where you can watch turtles and enjoy spectacular sunsets over lava rocks.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Welcome to Hale Lani at KaMilo, a stunning luxury townhome ideal for large families or groups seeking a memorable Hawaiian holiday. This beautifully furnished four-bedroom, three and a half bath residence offers an elegant blend of comfort and style.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Puako
Welcome to this bright and inviting one-bedroom condo located in the heart of Waikiki at the Waikiki Banyan. This spacious unit is filled with natural light during the day, creating a warm and welcoming atmosphere. The condo features a partial ocean view from the lanai, making your stay even more memorable. Registration Number: GE-011-768-6272-01, 260250050530, TA-011-768-6272-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Kauai escape at our meticulously furnished Poipu Oceanview Penthouse, located just a short 300-yard stroll through lush tropical gardens to Brenneke and Poipu Beaches. This top-floor luxury residence offers breathtaking views of the Pacific Ocean from its spacious lanai, perfect for relaxing and enjoying stunning sunsets from late September through March.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Welcome to your dream Maui retreat! This tastefully renovated 2-bedroom, 2-bath condo offers stunning ocean views and an unbeatable location right across the famous Cove surf spot. As you step inside, you'll immediately feel the relaxed beach vibes and luxurious touches that make this space special.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the ultimate Hawaiian escape in this spacious and breezy condo at Waikiki Shore #303, located at 2161 Kalia Rd. Spanning 692 sqft, this luxurious unit offers stunning views of Waikiki Beach from a unique 50-foot double lanai, the only one of its kind in the complex. Registration Number: 260040120005, TA-170-135-9616-02, GE-170-135-9616-02
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the best of Maui in this luxurious 2-bedroom, 2-bathroom villa at Marriott's Maui Ocean Club, situated in the newly renovated Lahaina Tower. With nearly 1300 sq. ft. of interior space, this villa comfortably sleeps up to 8 guests, making it ideal for families or groups.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to Hale Ama, a beautiful oceanfront home nestled in the enchanting Puako region of Hawaii's Big Island. This charming residence offers direct access to one of the most pristine coastlines in the islands, where each day begins with the soothing sounds of the sea and the gentle sway of coconut trees.
4 bedroom
12 bathroom
Sleeps 8
Hawaii> Waimea
Aloha! Welcome to this unique 2nd-floor ocean view condo in Molokai, offering stunning sunset vistas. , the open floor plan features hardwood floors, granite countertops, and a loft with a double bed. The master bedroom has a comfortable queen bed, and the condo includes an in-unit washer and dryer for convenience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> maunaloa
5-bath single-level villa located on the popular Waiulu Street within the Four Seasons Hualalai Resort. Spanning 2,642 square feet with a 414-square-foot lanai, it offers stunning views of Punawai Pond, the 5th Hole of the Ke`olu Golf Course, and the ocean beyond.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Immerse yourself in the ultimate Maui retreat at our 'FIVE STAR' condo located in North Kaanapali on the West Maui coast. This premier, front-and-center oceanfront unit on the 3rd floor offers breathtaking views of the ocean and nearby islands, providing a perfect setting for a serene and luxurious escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to this charming and cozy condo situated in a small complex with a stunning pool surrounded by lush gardens and a BBQ area. Just a four-minute walk from Kamaoloe Beach, you'll have easy access to sun, sand, and surf. The vibrant Triangle area, known locally for its excellent restaurants and shops, is only a five-minute stroll away, making it convenient to explore the local scene.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Welcome to your dream Maui escape at Wailea Luxury Hilltop Resort Vista, a beautifully remodeled condo as of September 2023. This immaculate property offers breathtaking direct ocean views and a stylish, airy interior designed for comfort and elegance.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Hawaiian escape at our charming oceanfront condo in Lae Nani, Kauai. Nestled in the lush Coconut Coast, this second-floor end unit offers breathtaking panoramic views of Wailua Bay, where you can watch surfers, kiteboarders, dolphins, and whales from your private lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapa?a
Experience the beauty of Hawaii at Mauna Kea Sunsets, a luxurious condo located in the Mauna Kea Resort Kumulani complex. This upper-level, two-bedroom unit offers breathtaking views of the ocean, mountains, and the adjacent golf course, providing a perfect setting for relaxation and adventure.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kamuela
Escape to your personal paradise with this luxurious Hawaiian retreat located near the stunning Kohala Gold Coast beaches and resorts. This exquisite home blends comfort and convenience, offering a perfect sanctuary for relaxation and exploration.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Experience a luxurious stay in this deluxe oceanfront 2-bedroom lock-off villa at Westin Ka'anapali Ocean Resort. The unit features a supersized lanai perfect for enjoying breathtaking sunsets and happy hours, offering an exceptional view of the oceanfront setting.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Discover this beautifully renovated condo at Kiahuna Plantation, offering a rare new A/C system for ultimate comfort in sunny Poipu. Located in Building 41’s peaceful corner, this 2nd-floor end unit features soaring vaulted ceilings, additional windows, and lush garden views, creating an airy and tranquil retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Kainani #1 is a beautifully appointed 2-bedroom, 2-bathroom villa nestled in the heart of Poipu, offering a perfect blend of comfort, convenience, and tranquility.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to your ideal Maui getaway! Located just six miles from Lahaina, our pristine condo offers breathtaking ocean views and direct beach access, making it perfect for a relaxing vacation.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Lahaina
Experience the best of Maui at the Kauhale Breeze, a beautifully renovated home offering spectacular ocean and West Maui views. This spacious property comfortably hosts up to 4 adults and 4 minors, adhering to Maui county law. The large deck is perfect for relaxing and enjoying breathtaking sunrises and sunsets, while the outdoor shower and lounging bench add to the tropical vibe.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Hale Lula o' Nai'a (House of the Dolphins), an exquisite oceanfront retreat located on Alii Drive in Kailua Kona. Perfectly positioned just north of Kahalu'u Beach Park, this home offers easy access to premier surf spots, paddleboarding, snorkeling, and other water activities.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to our stunning Regency Villa in Poipu, Kauai. This recently acquired, luxurious 2-bedroom condo offers a perfect blend of comfort and style with beautiful garden views and a prime location. The boutique resort is one of the newest in Poipu, making it an ideal choice for families or friends seeking a relaxing getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Located in the heart of Kapa'a, this charming 2-bedroom condo offers a comfortable and relaxing retreat with modern amenities. The condo features a cozy queen bed and a spacious king bed, making it ideal for couples or small families. The space is thoughtfully furnished with essential amenities, including high-speed WiFi, air conditioning, and a washer/dryer, ensuring a convenient stay whether you're here for a quick getaway or an extended vacation.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Discover this light-filled, airy 611 sq ft condo at Kona Reef, perfectly positioned on the third floor with direct ocean views from the living room, kitchen, and spacious 128 sq ft lanai. The fully equipped unit offers a seamless indoor-outdoor experience, ideal for relaxing while watching surfers and enjoying the ocean breeze.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the perfect Maui getaway at our beautifully renovated condo in Sugar Beach Resort, North Kihei. Located just steps from the Pacific Ocean and a pristine 5-mile white sandy beach, this property offers breathtaking panoramic ocean views from your private lanai.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Experience the ultimate Hawaiian getaway in this stunning 1,500 sq ft beachfront penthouse at Beach Villas Resort, Ko Olina. As the only private condo available for rent here with panoramic ocean views from every room, this residence offers unparalleled luxury and comfort.Registration Number: 910570090228, TA-137-160-2944-01, GE-137-160-2944-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to your perfect Maui getaway in this spacious 1-bedroom condo at Honua Kai Resort and Spa, located in the newer Konea Tower. This 780 sq. ft. suite is ideal for couples or small families (sleeps 1-4), offering a blend of luxury, comfort, and stunning views.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina-Kaanapali
Immerse yourself in the luxurious lifestyle of Mauna Kea with this beautifully designed two-bedroom villa, offering breathtaking ocean views and exclusive resort privileges.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimea
Welcome to our spacious 2 bedroom, 2 bathroom premium ground floor villa located in the desirable Waikoloa Beach Resort area. Overlooking the fourth hole of the Robert Trent Jones II-designed Waikoloa Lakes Golf Course, this villa offers a perfect blend of comfort, privacy, and stunning views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Permit: STPH 2013/0024TA-091-083-1616-01 / GE-091-083-1616-01TMK-270031060000
3 bedroom
4 bathroom
Sleeps 4
Hawaii> Haiku
Experience the epitome of Maui luxury at Polo Beach Club, perfectly situated at the start of Makena and linked to Wailea’s oceanfront pathway. This beautifully renovated condo offers bright, open interiors with creative decor and modern comforts, making it an ideal retreat.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to Hanalei Bay Resort unit 4101/4102/4103, a stunning ground-floor condo offering panoramic views of Hanalei Bay and the majestic Bali Hai Mountains. This spacious 3-bedroom unit comfortably accommodates families or groups, featuring two bedrooms with plush King beds, a third with a cozy Queen bed, and two large bathrooms with refreshing showers.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Imagine waking up to stunning Hawaiian sunrises from your spacious 28th-floor condo in Waikiki, where wall-to-wall windows offer breathtaking views from multiple angles.Registration Number: 260140390143, GE-078-467-0720-01, TA-078-467-0720-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to this beautifully upgraded 5th-floor studio suite at The Whaler, ideally located in the heart of Kaanapali Beach. Spanning over 500 sq. , suite 563 provides a luxurious retreat with stunning views of the Pacific Ocean and neighboring Lanai island.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautiful 3-bedroom, 3-bathroom Hawaiian vacation home nestled on 3 serene acres dotted with mature fruit trees in Captain Cook. The spacious 2,800 sq ft property offers a perfect blend of indoor comfort and outdoor beauty, featuring a private ocean-view lanai ideal for morning coffees or breathtaking sunsets.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Captain Cook
Discover this centrally located Waikiki condo featuring 2 bedrooms, 2 full baths, and a variety of sleeping options including 2 queen beds, sofa beds, a bunk bed, a floor mattress, and a crib. Enjoy the convenience of a free assigned parking spot, with additional paid parking nearby, and avoid resort fees for a budget-friendly stay. Registration Number: TA-081-847-7568-01, GE-081-847-7568-01, 260230720019
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Welcome to your perfect Hawaiian getaway at White Sands Village, Unit 229, ideally located just steps from the famous Magic Sands Beach in Kailua-Kona. This spacious 2-bedroom, 2-bath condo offers a blend of comfort, convenience, and island charm, making it an ideal base for exploring the Big Island.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Discover this beautifully remodeled corner unit at Wyndham Hotel, offering breathtaking ocean sunsets visible from the living room and bedrooms. The spacious condo features three bedrooms: a main upstairs with a king bed and private bath, a second upstairs with two twin beds and private bath, and a downstairs bedroom with a king bed.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Escape to Paradise at Hale Ylang Ylang in Hanalei. This luxurious two-bedroom townhome offers stunning ocean and mountain views, just steps from the famous Hanalei Bay and its beautiful beaches.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Hanalei
Welcome to Auntie Kali's Cottage, your private Hawaiian oasis nestled on Maui's South Shore. This beautifully furnished 2-bedroom, 2-bathroom cottage combines authentic Hawaiiana style with modern luxury. As you arrive through double entry gates, you'll leave the world behind and step into your personal mini-resort featuring an outdoor tiki bar, swimming lagoon, and lush tropical landscaping with exotic plants, bananas, and papayas providing fresh flowers and fruit.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to our private, gated estate nestled on the scenic hillside of Hualalai mountain in Kailua-Kona. Recently renovated, our 4,000-square-foot home offers a blend of modern luxury and island charm, perfect for families and groups seeking a relaxing Hawaiian getaway.
6 bedroom
4 bathroom
Sleeps 16
Hawaii> Kailua-Kona
Our stunning Kailua home has been featured in Hawaii Home Magazine and awarded best in class by the Hawaii Builders Association. Located just a 2-minute walk from Kailua Beach Park, the property offers easy access to local attractions like Kalapawai Market and Buzz's Steak House. Registration Number: GE-030-384-5376-01, TA-030-384-5376-01
4 bedroom
4 bathroom
Sleeps 7
Hawaii> Kailua
Welcome to Hokulani 836 at the prestigious Honua Kai Resort, your perfect Maui getaway! This highly upgraded corner unit on the 8th floor offers panoramic ocean views from Moloka'i to the West Maui Mountains. The expansive private lanai is ideal for entertaining, relaxing, or enjoying breathtaking sunsets with tropical drinks in hand.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kaanapali
Experience the ultimate Maui getaway in our beautiful oceanfront studio at Hana Kai Maui Resort, located just 100 feet from the water's edge. This charming unit features warm tropical decor with bamboo walls and rattan furniture, creating a cozy and authentic Hawaiian ambiance.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
