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 '%Casa De Emdeko%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Summary:
Newly renovated comfortable condo on ground level with garden view. Access to two pools, man made beach and bbq dining area fronting the ocean and sunsets. 920 sq ft of space, this unit has a separate entry to bathroom for guest. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Casa De Emdeko is one of the most popular waterfront Condo complexes in Kona; visit just once and, like most of our guests, you'll keep coming back. There is just something magical about Casa de Emdeko - there is a welcoming social ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Aloha and welcome to the most special Hawaii Vacation Condo in Kona on the Big Island, Casa De Emdeko 129.
This Oceanfront Vacation Destination is located on famous Alii Drive just two miles from the village shopping and Kailua Pier. The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Summary:
Newly renovated comfortable condo on ground level with garden view. Access to two pools, man made beach and bbq dining area fronting the ocean and sunsets. This unit has a separate entry to bathroom for guest. Large walk in shower,...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Convenient garden level 1BD/1BA unit sleeps 2-4. This condo has been completely remodeled in 2011 with tile and granite. Clean, comfortable, quiet and located just steps from both the saltwater and freshwater pools, beach-front barbeque and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
This immaculately remodeled Deluxe 1 bedroom at the Casa De Emdeko is truly a Breathtaking Property.
Located in the heart of Kailua-Kona, The Casa De Emdeko complex is a spectacular development that offers owners and guests the perfect blend ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
CASA DE EMDEKO, UNIT#230: DELUXE, REMODELED, 1/1!
Deluxe unit at Casa De Emdeko: VERY spacious, complete renovated unit, at this Ocean Front complex. Tastefully appointed 920 sq ft . apartment. Ocean views from spacious covered Lanai, view ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Our ocean view condominium is kept updated and comfortably furnished to offer you a relaxing, quiet, and enjoyable vacation. You'll love the bamboo floors, which lends to the clean, open feeling of our wonderful condo. This top floor end unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Your dream tropical vacation begins at Casa De Emdeko 336, a totally remodeled, fully-furnished oceanfront condominium. Within walking distance of Kailua-Kona Village, this ideal unit features central air conditioning, two bedrooms, two ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Casa#A105 is an excellent, affordable Ground floor property that you will certainly love. Unit#A105 has great access in the complex, only 4 doors in from the general parking area. Close to the Pool too! This unit is located on the "shadier" side...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Newly renovated condo close to downtown Kailua-Kona. Two pools, one right on the ocean (saltwater pool) and one freshwater pool. BBQ area overlooking the ocean. Beach area where you can sit right next to the ocean and enjoy beautiful Kona ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona,
Casa#323 is a Beautifully Remodeled Top Floor 1 bedroom, 1 bath condominium at the Oceanfront Casa De Emdeko Condominium Development.
This special property is close to the elevator on the 3rd floor for ease of access.
Located in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
vrKCV - Casa de Emdeko 306, Professionally Managed by Kona Coast Vacations
Welcome to your tropical paradise at Casa de Emdeko!
This completely remodeled one-bedroom, one-bath vacation condo is located on the third (top) floor, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Step into your slice of paradise at Casa de Emdeko #134, a sanctuary where the ocean’s melody serenades you into serenity, positioned mere steps from a secluded beach and two inviting pools. This oceanfront complex, nestled within ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Absolute oceanfront, Casa de Emdeko # 138 2 bedroom/2 bath condo. Enjoy year-round sunsets from the lanai. Dolphins play in the surf and whales are in view when in season. Inside you will find a remodeled granite kitchen with easy pass-thru to ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Casa De Emdeko 235 is a one-bedroom, one-bathroom non-smoking condominium within walking distance of downtown Kailua-Kona. This non-smoking oceanfront property features complimentary Wifi and is conveniently situated on Ali'i Drive, with miles ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
The Owners of Casa#237 have completed an incredible remodel. Top of the Line Kitchen & Bathroom with an amazing walk in shower. They have also ADDED A 2ND HALF BATHROOM! Wow! Very rare and special amenity, nearly unheard of at the Casa De ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Casa#324 is a Beautifully Remodeled Top Floor 1 bedroom, 1 bath condominium at the Oceanfront Casa De Emdeko Condominium Development.
This special property is directly across from the elevator on the 3rd floor for ease of access.
Located ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Need a perfect home base for your Big Island Vacation?
Perfectly located just two miles south of the Kailua-Kona pier, Casa de Emdeko is situated in close proximity to Ali'i Drive’s downtown area and nightlife - yet far enough away that...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
At Casa De Emdeko you can have it all!
Enjoy your morning coffee from the lanai while enjoying the partial ocean views! You'll also have a direct lush garden view to the soothing fountain. Relax and bask in the breathtaking ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
