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, 20
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
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
This property is a legally established dwelling that is rented for periods of 30 days or more at any one time. Contact us for more details.
This remarkable property is located in the highly sought-after East Honolulu neighborhood and is ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Honolulu
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
Fully remodeled in September 2021, 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
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
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
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
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
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
