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 '%Kona Makai%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Stunning ocean views from the lanai, ocean front from the bedroom, the sounds of the surf on the rocks, and the spirit of Aloha welcomes you to our home in Hawaii. Kona Makai is a beautiful oceanfront complex with well maintained gardens, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Hawaii STVR License: TA-146-953-6768-01 & GE-146-953-6768-01
Crashing waves lull you to sleep in our OCEANVIEW Very spacious 670 sq. ft. 1 bedroom condo! Steps to the waterfront at the tropical OceanFront Kona Makai. Our light and bright 2nd...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Bask in the beauty of Kona Makai 6303, a remodeled, one-bedroom, one-bathroom oceanfront condominium. This stunning top-floor unit also features ocean views from the main spaces, free Wifi and a wonderful loft with two twin beds (trundle style)....
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Come and enjoy our little Piece of Paradise in one the most beautiful complexes in Kona. Located just one mile south of the town of Kailua-Kona on Alii Drive. The unit, 1204, is on the second floor of building 1, the best building in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
This 2 bedroom 2 bath condo has everything you need to relax in paradise! Enjoy ocean views and gorgeous Kona sunsets from your private lanai.
The well appointed kitchen is fully equipped to comfortably prepare meals/drinks and the spacious ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Welcome to Kona Makai 6201, a two-bedroom, two-bathroom oceanfront unit, where the wrap-around lanai offers some of the best views on the island. Located less than one mile from downtown Kailua-Kona, you'll have access to all of the fun and ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Enjoy the comfort and efficiency of Kona Makai 6301, a newly remodeled oceanfront condominium offering stunning coastline views and free Wifi. Located one mile from the heart of Kailua-Kona town, you'll have access to all the fun and excitement ...
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
The Kona Makai complex is a beautifully maintained & highly sought after Ocean Front Complex located on famous Ali'i Drive, just 1.3 miles from Kona town. This wonderful complex features include, beautifully manicured tropical grounds, Saltwater...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Lovely top floor unit with great ocean view from both lanais. Well equipped with free WiFi and air conditioning.
OUR RESPONSE TIME is less than one hour on inquiries!!
King bed in master bedroom and the Loft bedroom has a queen bed. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Kona Makai #4-306 is a excellent, very spacious condo for your Hawaii Vacation! Rarely are there 2 bedroom units with the loft being the Third Bonus Bedroom! Being the top floor corner in building 4, the location is truly wonderful! A great ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Kona Makai #1-301 is arguably the BEST condo in the entire complex! Being the top floor corner in building 1, the location is undeniably too perfect. Private & upgraded, this is direct oceanfront condo living at its finest. You can't get a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Kona Makai#2-201 is a fantastic option for your Hawaiian Vacation. The owners extensively updated the property with beautiful new furnishings, installed tile throughout the entire condo & have upgraded the property with everything any guest may ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Flanked by iconic Ali’i Drive and the rocky shores of the Pacific Ocean, the Big Island’s Kona Makai Condominiums provide the perfect backdrop to your Hawaiian vacation. As you make your way to Unit 2103, a recently upgraded one bedroom, one...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Kona Makai 3-205 is a beautifully appointed 2nd floor condo with ocean views! This condo is fully equipped with everything you may need for your Vacation! Your own private Wifi network available for use as well! Air Conditioning is in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAILUA KONA
Come Vacation in Paradise in an Ocean Front Complex
Tropical island decorator themes welcome you to Paradise at the front door of this unit. Check out the hand painted ocean mural on the living room wall! It sleeps 6 with 2 king sized ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Kona Makai#5-205 is a fantastic option for your Hawaiian Vacation. Gorgeous Luxury Vinyl Plank Flooring Installed throughout Living Areas/Bedroom. No Carpet!
This beautiful 2nd floor unit is located in a very accessible spot of ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Sunset Haven at Kona Makai is a charming one-bedroom, one-bathroom property with an updated kitchen and room for up to four guests. Enjoy ocean views from the lanai and breathtaking sunsets. The oceanfront amenities include a pool, BBQ area and ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Kona Makai#2-102 is an excellent option for your Hawaiian Vacation.
This spectacular ground floor unit is a true gem at the Kona Makai. Queen Sized Bed in Bedroom & Queen Sized Blowup mattress for 2 people if needed! The owners keep ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
This gorgeous, newly-remodeled condominium gives you an authentic flavor of Hawaii. You'll love waking up to panoramic views of the Pacific Ocean in this tropical vacation paradise.
Fresh and updated, this beautiful condominium features ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Absolutely stunning unit at Kona Makai! Unit#6-305 is a gorgeous fully remodeled top floor unit. It features a spacious 2 bedroom, 1 bathroom oceanfront space of 779 square feet & an 122 square foot lanai. This condo can sleep up to 6! ...
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kailua Kona
NEW REMODEL AS OF FEBRUARY 2024! Enjoy amazing new features of Kona Makai 3-203 to include:
All new flooring, Paint throughout, All new cabinets, All new granite countertops, New furnishings, appliances, and much more!! The Owners are the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Welcome to Kona Makai 3102 by Gather!
This gorgeous, newly-remodeled condominium gives you an authentic flavor of Hawaii. You'll love waking up to panoramic views of the Pacific Ocean in this tropical vacation paradise.
Fresh and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Newly remodeled in 2023! Pictures reflect the current updates!
Incredibly special & unique unit at Kona Makai, this top floor unit with a loft has SPLIT AIR CONDITIONING INSTALLED! Split AC in Loft Bedroom & Back Bedroom! Very rare ...
- 3 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Kailua Kona
Unit#2-303 is a excellent top floor unit with a loft, Optional AC in living room and bedrooms for an additional $25/night + tax! Enjoy gorgeous views of the ocean, waterfront pool, and tropical landscaping!
The Kona Makai is a beautiful, ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Kona Makai#1-103 is one of the best located ground floor units in the entire complex! Being 2 units away from the very west ocean end of building 1, the location is undeniably too perfect.
This NEWLY REMODELED unit has a gorgeous upgraded ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Unit 1-102 is located in the highly desired building 1 in the gorgeous oceanfront complex of Kona Makai.
With stunning ocean views from both the Lanai and Bedroom, this unit is literally steps away from the ocean.
This ground floor unit is...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
From the moment you arrive to this seaside condo you will feel the tranquility.
Impeccably maintained grounds in this complex invite you to spend time in the beautiful surroundings. The pool/hot tub, and work out room are steps outside your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Discover your perfect Hawaiian escape at Kona Makai 5103, a charming and tranquil one-bedroom condo nestled in a lush tropical setting.
This cozy retreat is designed for comfort and relaxation, offering a serene garden view and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
