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
Welcome to our Beautiful Waikoloa Beach Condo-Penthouse with Discounted Gold Golf Membership & Fabulous Sunsets.
We have recently remodeled our bedrooms including the loft with new furniture, new flooring and flat screen TVs. It is a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
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. Feel the Aloha spirit in this 2-bedroom/2-bath+loft top floor condo.
Treat yourself to a unique Mid-Century Modern ...
- 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
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 the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Everything you'd ever want in a tropical getaway! With the ocean just feet from your private lanai, your family will be close to everything Kona has to offer when you stay at this centrally-located place. Enjoy the immaculate grounds of the Kona...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Discover the ultimate South Maui escape at Kamaole Sands 4-411, a beautifully renovated 2-bedroom, 2-bath condo with stunning ocean views. Located in the heart of Kihei, just steps from Kamaole Beach Park, this luxurious condo blends modern ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
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
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
**NOTICE** My unit has just had all the decor in the unit completely updated!
Here's what's new: New draperies, sofa bed, recliner, entertainment center, 65" TV, washer/dryer, refrigerator and dishwasher. Living Room lanai dining ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Your dream vacation awaits, beginning with this lovely 2-bedroom plus loft at Kona Makai!
Located on the top floor, enjoy spectacular ocean views and breathtaking sunsets from the wrap-around lanai which provides ample seating for your ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
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
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
vrKCV - Fairway Villas Waikoloa G33, Professionally Managed by Kona Coast Vacations
Welcome to Fairway Waikoloa G33! With a Hawaiiana theme throughout, this one of a kind residence is nestled in a garden-like environment in the highly ...
- 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 311, 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...
- 2 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
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 ...
- 3 bedroom
- 1 bathroom
- Sleeps 6
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
Penthouse condo with sweeping Golf Course and Lagoon Views. Upper story location catches refreshing tropical breezes.
This two-bedroom with loft ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Spend your next vacation in our luxury two-story villa in the Waikoloa Beach Villas. This unique floor plan with 1,585 square feet of living space provides plenty of room and privacy for your whole family.
The main level features a large...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Village
Enjoy views of the Pacific Ocean, tropical grounds and koi pond from this spacious Papakea condo! Sip local coffee and watch fiery sunsets from your private, comfortable lanai.
Papakea C405 is a three-bedroom loft, two-bathroom condo ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Hale On'tario
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
High on a cliff in Princeville, a prime neighborhood in the heart of Kauai’s ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Princeville
Aloha~
Welcome to your little slice of paradise! We are the new owners of a long-time favorite and previously 5 star reviewed vacation rental (VRBO requires that you start the unit's listing, reviews, and star history from scratch with ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This beautiful, fully air conditioned, 1620 sq' Poipu Kai Regency condo is very spacious with two stories and an open loft that serves as a fourth bedroom. On the main floor is the master bedroom with a Calif. KING size bed, a large master ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Poipu
"Alau” – Unit 202 – is an upper-floor, lofted, ocean-view studio located in Hana Kai’s Mauka Building. The unit accommodates up to 4 guests with 1 queen bed on the main floor and another queen bed in the loft. *Note* SLEEPING IN THE LOFT...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hana
