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 '%Hale Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Youtube Video Presentation: https://youtu.be/-c1tR8mMI1g
Hale Kai in Hawaiian means “ocean house” as there are spectacular views of the coastline and a white sand beach from every room. This home sleeps four with two bedrooms, two ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Fully remodeled first floor direct oceanfront condo, just steps from the pool, beach, and BBQ. The Hale Kai has a small quiet feel, that has guests returning year after year, "Hale Kai", your home by the sea. Experience amazing views of Molokai ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Hale Kainani 6
E Komo Mai
Set within the exclusive resort community of Kukui`ula, this luxury Poipu vacation rental offers the ultimate location for your group getaway. The home is brand-new, finished and furnished in comfortable elegance ...
- 4 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Koloa
Hale Kai O Kihei 216
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
This Hale Kai O’ Kihei condo offers a breathtaking ocean view from ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
This bright ground floor one bedroom unit was recently upgraded. Enjoy stunning ocean views from the lanai, with a pristine swimming pool and the ocean just steps away.
Located at Hale Kai O Kihei condo complex in the heart of Kihei, you are...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Superbly stylish in every way, this 4BR/4.5BA South Shore showplace offers all the luxuries you’d expect in a glamorous Kukui‘ula villa—tropical charm, abundant space, elegant furnishings, expansive ocean-and-mountain views—and many ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Koloa
Fully remodeled in June 2023, this bright, beautiful one bedroom, one bathroom oceanfront property in Hale Kai O Kihei welcomes you! This second floor condo is luxurious and comfortable and includes a brand new, top of the line split air ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Hale Kai O Kihei 312
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
This Hale Kai O’ Kihei condo offers a breathtaking ocean view from ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Summary:
Discover charm and comfort in our ground floor vacation rental, nestled by the pool and oceanfront. Immerse yourself in stunning sunsets just steps from the water. This cozy unit boasts a fully equipped kitchen, a luxurious king-size...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to Hale Kai, a stunning, private, gated historic Hawaiian estate located on nearly 3 acres of oceanfront property (500-foot frontage) directly on the calm waters of historic Keauhou Bay on the Big Island of Hawaii.
This main ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Kailua
Optional services offered upon request: Mid-stay housekeeping. Babysitting services provided by a First Aid and CPR certified family member. Please inquire for rates and availability.
Aloha and welcome to our ocean view beach villa! This...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapolei
ALOHA AND WELCOME TO HALE KAI # 101
Beautiful oceanfront two bedroom condo with king bed, two twin beds and sleeper sofa that can accommodate up to six people. The condo was fully remodeled and refurnished in 2014. The ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Lahaina
ALOHA AND WELCOME TO HALE KAI # 102
Beautiful one bedroom condo with king bed and sleeper sofa that can accommodate up to four people. Children and long term rentals are welcome. Located in Honokawai, which is between ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Experience the ultimate tropical vacation by staying at this Big Island Beach House, a luxurious oceanfront property located in the western condo community of Hale Kai O'Kona. This stunning vacation home is managed by Gather, the premier ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua Kona
"Hale Kai" is well appointed two bedroom perched on a bluff overlooking Papaloa Bay just steps down to the Beach. The panoramic view will mesmerize you and the sound of the ocean soothe your soul. You'll fall in love from the moment you walk ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Maximum occupancy strictly enforced in accordance with local ordinances.
Welcome to Hale Kai 2! Immerse yourself in this coastal getaway, where colorful landscaping and shimmering lagoon pools await. Whether you're unwinding in the steamy...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
Maximum occupancy strictly enforced in accordance with local ordinances.
Welcome to Hale Kai! If you enjoy panoramic ocean views and tiki-lined pathways to white sand beaches, Ko Olina Kai is the paradise for you. Once you're finally able...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapolei
ALOHA AND WELCOME TO HALE KAI # 120
Experience your perfect Maui vacation. Comfortably decorated with tropical Hawaiian furniture and artwork, our unit will provide you with all the comforts of home, including a full ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Lahaina
ALOHA AND WELCOME TO HALE KAI # 215
Located right on the ocean on the beautiful island of Maui, our condo can be your _home by the sea_. Comfortably decorated with tropical Hawaiian furniture and artwork, our unit will ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Hale Kai O Kihei 202
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
This Hale Kai O’ Kihei condo offers a breathtaking ocean view from ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Hale Kai O Kihei 211
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
This Hale Kai O’ Kihei condo offers a breathtaking ocean view from ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Come visit and see our gorgeous 2024 room renovations! Beds, furnishings, art, decor - everything is ALL NEW. The amazing ocean front view remains the same ;-)
Hale Kai Hawaii Suites offers four oceanfront rooms (booked individually) and...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hilo
ALOHA AND WELCOME TO HALE KAI # 118
NOTE FROM THE OWNER:
_When I remodeled this apartment, which has been in our family since the Hale Kai was built, I took special care to make it wheelchair and walker accessible....
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
ALOHA AND WELCOME TO HALE KAI # 108
Hale Kai # 108, our “home by the sea”, was decorated to reflect the charm of old Hawaii but with all of the comforts of home – flat panel TV, DVD player, free wi-fi, a fully ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Lahaina
We would like to welcome you to our #104 “home by the sea” in West Maui.
Step back in time and visit romantic old Hawaii during your stay at this unique condo. When you enter, our tropical and colorful décor will welcome you. This ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
ALOHA AND WELCOME TO HALE KAI # 117
ARLENE'S MAUI OCEANFRONT CONDO
Hale Kai #117 is just steps to the sand, ocean and pool. From this one level, ground floor, non-smoking, oceanfront condo, you can...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
ALOHA AND WELCOME TO HALE KAI # 103
Located right on the ocean on the beautiful island of Maui, our condo can be your _home by the sea_. Comfortably decorated with tropical Hawaiian furniture and artwork, our unit will ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
E Komo Mai, Welcome to Hale Kai Kea!
This island home features 3 large bedrooms, each with a king sized bed and ensuite full bathroom. The home is outfitted with tropical-inspired furniture, and ceiling fans throughout. The open layout of...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Hale Kai O Kihei 212
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
This Hale Kai O’ Kihei condo offers a breathtaking ocean view from ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Come visit and see our gorgeous 2024 room renovations! Beds, furnishings, art, decor - everything is ALL NEW. The amazing ocean front view remains the same ;-)
Hale Kai Hawaii Suites offers four oceanfront rooms (booked individually) and...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hilo
