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 (city = '96740' OR postalCode = '96740' OR region like '%|96740|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Punahele Penthouse Condo will fast become your favorite place to stay in Kailua-Kona! Punahele Penthouse is on the 7th (top) floor of the Kona Ali'i Condo Complex which is located in the heart of Kailua-Kona. You're within walking distance to ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Guests walk in and we see wow on their faces...gourmet kitchen, ocean views, huge bedrooms.
"perfect for a family reunion"
"cool at night was just right"
"close to the best beach in Hawaii"
"your own private estate"
"amazing...
- 6 bedroom
- 6 bathroom
- Sleeps 18
Hawaii> Kailua-Kona
Love to snorkel and want to see over 100 species of tropical fish & turtles? Then why not stay right across the street from Hawaii's best snorkeling beach?! Our modern beachfront unit offers gorgeous ocean/sunset views and the easiest access ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Enjoy breathtaking sunset views every night from this beautifully updated condo steps from Downtown Kailua-Kona village with AC in both the living room and bedroom.
Sunset in Paradise is your private paradise with gorgeous ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Welcome to our beautifully decorated ocean front condo in paradise - Kona Reef A-22.
Our condo is our home away from home, so we respectfully request a maximum of 2 adult guests and no children under 18.
Whether this is your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
This breathtaking 3 Bedroom plus bunkroom, 2 bath single level home is located just minutes from Kailua, Kona and some of the most beautiful beaches in Hawaii. Everything in this house is new! Fresh full remodel just completed. This includes a ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Our unit is spacious with high ceilings, ceiling fans, fabulous sunlight, and gorgeous ocean views. This elevated third story unit affords a wonderful lanai with a view overlooking manicured golf courses, tropical flowers, natural lava ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Welcome to this wonderful 3 bedroom / 2 bathroom duplex home only a 3 min walk from Magic Sands Beach. Just remodeled and professionally decorated, this charming vacation getaway has all of the comforts you desire in your home away from home, ...
- 4 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kailua-Kona
*** For more details and more pictures, go to our website at BIGWAVEHAWAII•COM***
The Big Wave Penthouse is arguably in the best location on the Big Island. Consider this:
• Facing west, you're guaranteed a front-row seat every...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Aloha and welcome to our condo at White Sands Village. Brand new to the rental market, this wonderfully appointed 2-bedroom, 2-bathroom top floor condo offers everything you’ll need to enjoy your stay in Kailua-Kona and the Big Island Of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Was recently completely remodeled.
Our oceanfront condo is 23ft. from the ocean and is located on the 2nd floor and offers spectacular sunsets.
Watch the surfers and the dolphins from your lanai.
A Short walk to town and over 20 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
On the Kona side, safe and away from the volcanic activity, the Cottage awaits you.
The perfect, peaceful getaway for those on a honeymoon or looking for a relaxing, romantic experience. This warm, welcoming, newly-built and finely ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Cozy, private rain forest retreat cottage situated above Kona in the Kaloko Mauka Honua'ula Forest Reserve. Located on a 3-acre property, this cottage is set away in a lush forest of Ohia trees and Hapu'u tree ferns. Enjoy the sweet aroma of ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Peaceful, well appointed room with private entrance ,down a flight of stairs, overlooking our back yard garden of fruit trees and red ginger blooms. Private bath with shower, queen bed, walk in closet, kitchenette, outdoor seating; Wake up and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
STVR-19-358846 - Relax and pamper yourself in luxury in this well-appointed condo. On the golf course with Spectacular Ocean views. This is NOT your average condo rental! This condo has had an extensive high-end renovation and interior ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Kona Bay Estates is a gated and patrolled oceanfront community. The Sunset Beach House offers 2700 square feet of luxurious space. It is just 500 meters from the sea. Opening walls of glass offer indoor-outdoor living with spectacular views of ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Kona Shores is a great location right on Alii Drive, a Hawaii Scenic Byway (Ala Loa Foot Trail). A lovely complex with an oceanfront pool & BBQ area for easy island living! Ground floor, end unit with easy access to Alii Drive and just one mile ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Kona Shores is a great location right on Alii Drive (Hawaii Scenic Byway). A lovely complex with oceanfront pool & BBQ area for easy island living! Ground floor with easy access to Alii Drive and just one mile to downtown Kona, shopping, ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Enjoy a perfect vacation in this private Hawaiian home.
Imagine arriving in Hawaii and pulling up to your own private home with ocean views from every bedroom! This spacious home is located in a beautiful upscale neighborhood and has a ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua Kona
Haliipua Villas are the newest generation of multi-family villas at Hualalai offering an array of designer finishes throughout the home. Special features include luxurious mahogany finishes, island-inspired ceiling treatments, enhanced ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
This spacious 3 bedroom, 3.5 bath single-level villa is centrally located on the Resort’s popular Waiulu Street overlooking beautiful Punawai Pond, the 5th Hole of the Member’s Ke`olu Golf Course and the ocean beyond. With 2642 square feet ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
One of the largest floor plans of any villa at Hualalai, this spacious and centrally located 3 bedroom, 3 bath villa is ideal for your Hawaii getaway. Extra bedding available with a "bonus" living room on the first level. The down-stairs suite ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Whale watch while listening to the breaking waves from the shoreline from the Primary bedroom's private lanai with unobstructed ocean views! This beautifully appointed three bedroom villa has over 2,500 square feet of living area. Each of the ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua Kona
The spacious floor plan includes a downstairs primary bedroom suite with king bed and luxury spa-like bath. Just off the master is a large living room with queen sized sofa pull out that opens to the downstairs lanai, leading to the lawn and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
This luxury condo is as close to the beautiful blue waters of the Pacific Ocean as you can get at Kanaloa at Kona. Unit 1804 is a top-floor (second floor) oceanfront condo with over 1700 square feet of living space with all the amenities of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Hale Akoa, Professionally Managed by Kona Coast Vacations
Welcome to Hale Akoa, a stunning 5-bedroom, 4.5-bath single-level home located in the exclusive, gated community of Kahakai Estates in Kailua-Kona on the Big Island. This ...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Kailua Kona
vrKCV - Mahi Iulani, Professionally Managed by Kona Coast Vacations
Welcome to Mahi Iulani – Your Private Tropical Retreat
Nestled on the gentle slopes of Kona, Hawaii, Mahi Iulani is a luxurious tropical villa where tranquility...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua Kona
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
vrKCV - Kona Isle B38, Professionally Managed by Kona Coast Vacations.
Welcome to your oceanfront retreat in scenic Kailua-Kona! This renovated one-bedroom condo offers stunning ocean views and is located right at the water's edge, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Spectacular Sweeping Views from this Four Bedroom Luxury Home with Separate Guest House is Paradise Perfected. Located on the #16 Fairway of the Hualalai Golf Course, this luxury home is recently redecorated by Willman Interiors, and boasts ...
- 4 bedroom
- 5 bathroom
- Sleeps 9
Hawaii> Kailua Kona
Hawaii STVR Registration Number: STVR-19-355146 ****************************** OUR CONDO HAS BEEN COMPLETELY RENOVATED !! NEW DUAL AIR CONDITIONING SYSTEMS - - KEEPS CONDO COOLER THAN MOST OTHER UNITS IN RESORT
PREMIUM OCEAN VIEWS ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Located in the Award Winning Resort Community of Hualalai Four Seasons.
Enjoy the sounds of the ocean serenade, the magic of sunsets, and whale watching during the winter months, from this recently updated garden level Hillside Villa. ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Enjoy Panoramic Ocean Views from your private lanais. Sunset views 365 days a year. Your only decision, whether to use the upstairs lanai with chaise lounge and cosy sitting area perfect for morning coffee or wine and cheese at sunset, or the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - GMH at Keauhou Estates 9, Professionally Managed by Kona Coast Vacations
Imagine a warm, welcoming place where families and friends can feel a sense of peace, and share in the wonderment of seeing the blue Pacific Ocean stretch ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kailua Kona
Welcome to Keauhou Lani, an exquisite private residence located in the desirable gated community of Keauhou Estates.
Step into the grand entry of Keauhou Lani's 4 bedroom, 3.5 bathrooms, 4,750 sq. ft. home plus, 1,700 sq. ft. covered ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
vrKCV - Nani Kahakai, Professionally Managed by Kona Coast Vacations
Welcome to your tropical retreat! This beautiful 3-bedroom, 2.5-bath home offers breathtaking ocean views and a private pool, all nestled within a gated community. ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Gorgeous, Contemporary, Newer Home Exemplifies Luxury and ease of Living in our Island Paradise. This 5-bedroom, 5 1/2 bath pod-style estate home is exceptionally appointed and designed to take full advantage of the temperate climate and island ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
Prime fairway front location - great for bird watching or watching golfers along the 11th fairway of the Kona Country Club Ocean Course. Peaceful and spacious with lovely Hawaiian decor, this condo is the perfect escape for two couples or a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Hawaiian Honu House, Professionally Managed by Kona Coast Vacations
Hawaiian Honu House
Welcome to Hawaiian Honu House, a stunning 3-bedroom, 2-bathroom home in the tranquil gated community of Kahakai Estates, just minutes from...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
vrKCV - Nakukui Elua, Professionally Managed by Kona Coast Vacations
Nakukui Elua- Peaceful and Private with Pool and Ocean Views!
Flowering hedges, rock walls and wooden fencing surround your island home. Located on 3/4 acre lot ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Aloha Place, Professionally Managed by Kona Coast Vacations
Aloha Place – Luxurious 3-Bedroom, 3-Bathroom Home with Spectacular Ocean Views
Experience the ultimate in luxury and tranquility at Aloha Place, a Bali-inspired...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Kamilo Place, Professionally Managed by Kona Coast Vacations
AC Included in this 4 Bed, 3.5 Bath home with Pool & Ocean Views.
This beautiful island home is single level, beautifully furnished, with ocean views and a ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua Kona
vrKCV - Puuwai Alii Place, Professionally Managed by Kona Coast Vacations
Pu'uwai Villa is on a landscaped half-acre on a private road with plenty of off-street parking.
Private, very quiet, one of only six homes on the private ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua Kona
vrKCV - Laulea Hale in Kahakai Estates, Professionally Managed by Kona Coast Vacations
Breathtaking views! Laule'a Hale has just been remodeled for the ultimate resort home experience.
You'll enjoy panoramic ocean views and ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua Kona
vrKCV - Royal Sea Cliff 109, Professionally Managed by Kona Coast Vacations
Welcome to Your Luxury Oceanfront Getaway!
This stunning 1,900-square-foot villa is an oceanfront paradise, perfect for those seeking relaxation and adventure....
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Akoa Place, Professionally Managed by Kona Coast Vacations
Akoa Place - A Kahakai Beauty with Unmatched Panoramic Views
Perched at the top of a luxury gated community, Akoa Place offers breathtaking panoramic views of ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Kanaloa at Kona #2203: Inside, you’ll find a bright living area with generous seating and a large flat screen television, perfect for entertaining your friends and family! The suite’s full size galley kitchen has Koa wood cabinetry, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> KAILUA-KONA
Exquisite 2-bedroom, 2.5 bath house located in a private gated community on Ali'i Drive! With only 16 homes in this community, you can expect peace and serenity staying here on your Hawaiian vacation.
Featuring an air-conditioned home ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Unbelievable views from almost every room in the house!
Located just minutes above Kailua-Kona town, this spacious two-story home is a beautiful island getaway for your whole family. Two master bedrooms with king size beds and private ...
- 4 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
Enjoy your vacation at this Ali'i Cove townhome. Decorated in an elegant island style, this 3 bed, 3 bath end unit is designed for comfort and ease. Located on Ali'i Drive, Ali'i Cove is conveniently near shops, restaurants, and activities in ...
- 4 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua - Kona
