select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `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,
(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 and round(price)>0 and bedrooms>=0 AND (`headline` like '%loft%') AND sleeps>=1 order by rating desc LIMIT 0, 50
vrKCV - Country Club Villas 315, Professionally Managed by Kona Coast Vacations
*CONSTRUCTION NOTICE - Complex began stair railing steel painting project for the month of August, 2024. This consists of sanding away old paint, which can be...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Toast epic sunsets on the lanai of this modern 1,200sf suite with Pacific view. Tropical style rolls out with a spiral staircase to loft and master bedrooms, modern kitchen flowing into the open living area, laundry, ceiling fans, AC, WiFi, flat...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KIHEI
ALOHA!
***As of September 16th, 2024, we have installed two new split-unit AC (air conditioners)—one in the living room and one in the loft. Both units are of the highest quality and were professionally installed by one of the most ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kahuku
One of the nicest penthouse units at Papakea Resort, A404. From your lanai and the master bedroom, watch the whales play and the honu (turtles) swim. The KING bed in master bedroom allows you to sleep to the sounds of the ocean just 25 feet ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
Welcome to our Kauai Home!! Our Townhouse has been completely renovated with an all new kitchen complete with new stainless steel appliances, new cabinetry & granite counter tops. New Tile has been installed thru-out entry, kitchen, dining area...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
When the developer built this 18 unit condominium in 1973, he saved this unit for his family. One look will tell you why. It is the largest unit in the complex and is wonderfully spacious and open. Decorated in seaside colors, our unit ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Lahaina
Come enjoy a month or more at a spacious ocean view condo that has the perfect location in Kailua-Kona. There is a 30-night minimum stay required at Alii Cove. You may stay fewer nights, but we must block off 30 nights for each ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
You will not find a more luxurious penthouse ocean front unit at the Papakea Resort, than L405. From your lanai and the master bedroom, you can watch the whales play in the ocean seasonally and see the honu (turtles) swim all year round. This ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Lahaina
This stunning condo is located in the oceanfront building of the Kona Bali Kai, although not an oceanfront condo. There isn't an ocean view, but your proximity to the ocean is VERY close!! Just a walk through the under building parking garage,...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
We can rent the property for less then 30 days but will block availability for 30-One of three Newly renovated HAWAIIAN / Italian bungalow
5 minutes from downtown, 13 minutes from Waikiki, 10 minutes from the famous Ala Moana Shopping ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Stay close to Waikīkī Beach, minutes to Fort DeRussy Beach Park. Our 2-bedroom designer loft is in a secure building with a free offsite parking pass. Highlights include a full kitchen, unique wet room, beach gear, kids accessories and onsite ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
vrKCV - Fairway Villas Waikoloa F31, Professionally Managed by Kona Coast Vacations
Fairway Villas Waikoloa F31 – A Relaxing Retreat in Waikoloa Beach Resort
Welcome to Fairway Villas Waikoloa F31, where the Aloha spirit greets ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Welcome to Keauhou Kona Surf & Racquet Club #7-303!
This Gorgeous top floor unit truly is special, a lot of custom features one can enjoy. This spacious premium unit has an impressive 1,384 interior square feet, featuring 3 bedrooms & 2...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Golf Villas at Mauna Lani H22, Professionally Managed by Kona Coast Vacations
Welcome to your tranquil retreat at Mauna Lani's Golf Villas, perfectly situated along the ocean-facing side of the prestigious South championship golf ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
TMK: 750180080055
TA-014-093-3632-01
Alii Cove's Best Ocean Views! This unit is very quiet and has exceptional ocean views from the main lanai and upper lanai. The townhome comes with anything you could possibly need for an extended ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Two Bedrooms, 1 King bed, 1 queen bed, 1 queen murphy bed, and 1 queen sofa sleeper, 2 bathrooms, nicely upgraded, washer and dryer in unit, lanai, golf course view, great complex amenities.
If Sunny Poipu is your vacation choice, 6G at ...
- 4 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Welcome to Hale Blue Hawaii at Puamana 17D! Ideal for a family beach vacation, this spacious, two-story villa offers top-notch features throughout as well as plenty of on-site resort amenities located just steps from your door. Start your days ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Keauhou Punahele B301
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Boasting top-floor views of the ocean and golf course, this dreamy, loft...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Hanalei Bay Resort 8233 four
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Hanalei Bay Resort 8233/4 is located in the Plumeria Building ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
vrKCV - Country Club Villas 341, Professionally Managed by Kona Coast Vacations
Welcome to Country Club Villas #341 – Your Dream Vacation Awaits!
Aloha! Step into paradise and experience breathtaking views from Country Club ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Kailua's Lofty Bungalow is a premier private luxury bungalow getaway - Modern design mingles with traditional elegance in this newly built bungalow. Beautifully furnished with comfort and ease. Open floor plan that features floor to ceiling ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua
vrKCV - Country Club Villas 332. Professionally Managed by Kona Coast Vacations
Welcome to Country Club Villas 332, our Hale Moku (Island House)
Resting on the 18th fairway of Kona Country Club, this updated and newly furnished ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Country Club Villas 305, Professionally Managed by Kona Coast Vacations
Come enjoy a slice of paradise in this colorful Kona getaway with spectacular ocean and golf course views from the lanai, living room, kitchen, and primary ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Return to Maui! 🩵
Aloha! Welcome to Papakea and this beautiful OCEAN FRONT - 2 bedrooms, 2 bath + loft. The property is located within minutes of both Whaler’s Village and Black Rock and resides within a 13-acre resort like property. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Unwind in this serene, stylish oasis with a picturesque view of the golf course. Delight in the breathtaking scenery of the North Shore and charming Turtle Bay while luxuriating in the delightful, balmy tropical Hawaiian atmosphere.
The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
Experience this spacious, clean & comfortable, newly remodeled unique 2-story townhouse with the conveniences of a home which is fully air-conditioned w/ new quiet Mitsubishi split system a/c. This is especially important for the hot and humid ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Welcome to your Hawaiian retreat—a newly renovated 1,300-square-foot townhouse offering the perfect blend of comfort and island living. This split level unit, backing onto serene green space, provides an exclusive setting for your island ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Welcome to our private tropical oasis! This newly updated 1,478 sq. ft. end-unit offers the perfect retreat. Surrounded by lush tropical plants, you’ll enjoy privacy and tranquility, with no neighbors above or below you. The home features ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Princeville
Bring the whole family or group of friends to this great place with lots of room for everyone.
Spacious, ground floor, end unit fronts the 1st hole of the Fazio Course. Beautiful sunset views from lanai. 3 Pools. BBQ Grills. Fun Sports ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Affordable 2-Bedroom, 2-Bath + Loft at Kuilima Estates (next to The Ritz-Carlton O'ahu, Turtle Bay)
Enjoy more space without the luxury price tag! This comfortable and spacious 2-bedroom, 2-bath loft is the largest floor plan in Kuilima ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Welcome to The Drifter!! Part of the Kuilima Estates East community, the unit was designed with the goal of offering a “Relaxed Luxury” experience. Meaning, to provide guests with an elevated design aesthetic with a feel-good comfortability....
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kahuku
Aloha and welcome to our spacious and beautiful North Shore Turtle Bay Resort 1 Bedroom condo that includes an additional loft room + 1 bathroom. This gated condo is a short walk to the 5 Star Ritz Carlton Turtle Bay and all of its amenities, ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kahuku
Book With Confidence, This Nicely Updated 2 Bedroom Condo That Features A loft With An Additional Queen Sized Bed. This Condo Is Managed By PMI Maui, A Local Real Estate Brokerage That Specializes In Vacation Rentals. We Are Located Within A ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Country Club Villas 303, This TOP FLOOR UNIT comfortably sleeps 6 with each bedroom having its own full bath. 2-bedroom PLUS additional Queen bed in Loft. Three full bathrooms, Fully Air-Conditioned with Spectacular Sunset, Ocean and Golf ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
By following strict cleaning and disinfecting guidelines, we are doing our best to have our guests stay safe while on vacation in our condo! This beautiful, fully air conditioned, 1620 sq' Poipu Kai Regency Villas condo is very spacious with two...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Koloa
2 master bedrooms, 2 1/2 baths with a loft. 2nd floor unit, sleeps 6 comfortably. Fully stocked kitchen, full sized washer/dryer, assigned parking, color TVs with cable, VCRs and wifi, two lanai decks.Swimming pool with barbecue on property. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Aloha!!
Why stay in a hotel when you can stay in a beautiful, comfortable and spacious golf front condominium at the Fairway Villas at Waikoloa Beach Resort?
Our condominium home sleeps 6. It has 2 bedrooms, 2 full baths, plus a ...
- 4 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Puako
Treehouse Experience !!
Complete Bathroom Remodel completed in June, 2024.
Two Mitsubishi Mini Split Air conditioning units were added, living room (24,000 BTU) and Loft Bedroom (15,000 BTU).
Enjoy your Hawaii vacation ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kahuku
Come enjoy our Kings' Club golf membership. Our condo is ready for your vacation in paradise! Bring the family or a few couples and enjoy the resort life in the heart of the Waikoloa Beach Resort. Located on the fourth hole of the Waikoloa ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Waikoloa
Please read NOTICE towards end of this description affecting November 18 - December 1, 2024.
Kanaloa at Kona is an idyllic tropical oasis located on a point of land and surrounded by He'eia Bay, the Pacific Ocean and the Kona Country Club...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Sunrise Hale at the Cliffs
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Enjoy captivating ocean, mountain, and waterfall views from this ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Aloha and welcome to Polynesian Shores 219! Our remodeled 3-bed, and 3-bath condo is ready to host your Hawaiian adventures. Take in the ocean and Molokai views from the wrap-around balcony, or take a walk and mingle with the resident sea ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
vrKCV - Fairway Villas Waikoloa G33, Professionally Managed by Kona Coast Vacations
Welcome to Fairway Villas Waikoloa G33! Immerse yourself in the spirit of aloha in this beautifully appointed two-bedroom, two-bathroom loft condo with a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Welcome to the Listing Page of Keauhou Kona Surf & Racquet Club,#9-304!
This spectacular top floor unit truly is a gem in the development. This spacious premium unit has an impressive 1,170 interior square feet, featuring 3 bedrooms & 2 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Country Club Villas 334, Professionally Managed by Kona Coast Vacations
Country Club Villas #334 enjoys panoramic views of the Pacific Ocean with spectacular sunsets.
Treat yourself to the ultimate Hawaiian holiday in our ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
“Puhele” (Unit 206) is a premium oceanview studio located on the upper floor of the Mauka building. There is a king bed in the loft and a queen hide-a-bed on the main floor. Sliding glass doors open onto a spacious lanai with an amazing view...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Hana
Stay in lovely Hilo town and make this comfortable and clean House your Home away from Home. You will love the central location and quiet neighborhood as a restful interlude to your Big Island Adventure. Our newly renovated home is only a short ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Hilo
5-303 at the Surf & Racquet Club is a jaw dropping, absolutely gorgeous top floor condo located in the BEST building in the complex. Building 5! The most Private of them all! Come stay at this beautifully remodeled 2-story oceanfront unit and ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Placing a premium on privacy, this air-conditioned 1,102sf condo features a loft that’s perfect for families. Enjoy Haleakala views from the lanai while savoring meals prepped in the full kitchen. Other features include WiFi, ceiling fans and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KIHEI
vrKCV - Fairway Villas Waikoloa N33, Professionally Managed by Kona Coast Vacations
Welcome to Your Penthouse Paradise at Fairway Villas Waikoloa N33
This top-floor, two-bedroom loft penthouse offers sweeping views of the Waikoloa ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
