select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) 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, 15
Property is currently for sale. The property manager will contact you to make arrangements to schedule showings at a convenient time.
Hale Kai offers space and comfort in a chic oceanfront setting. The pristine, private ocean front house has ...
- 5 bedroom
- 4 bathroom
- Sleeps 12
HI> Honolulu
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 every room and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
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 bathrooms and laundry facilities. It has a spacious kitchen and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Hale Kai O Kihei 101
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 every room and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
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 with ...
- 4 bedroom
- 5 bathroom
- Sleeps 10
HI> 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
HI> Lahaina
Welcome to Hale Kai Nalu (“home of the ocean wave”) - a perfect place to call home while you enjoy all that the magic that the Big Island has to offer. Here, everyday stresses will melt away and our Aloha-pace of life will become yours. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Keaau
Hale Kai is a beautiful, private, gated historic Hawaiian estate located on nearly 3 acres of oceanfront property (500 foot frontage) right on the calm waters of historic Keauhou Bay on the Big Island of Hawaii.
This residence, designed by ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
HI> Kailua
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 condo is located ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
HI> 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 provide you with ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
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 every room, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
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 kitchen.
Apartment ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
HI> 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 snorkel, swim, watch turtles and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> 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 unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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 every room and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
