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
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
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
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
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
"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! 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
This recently remodeled, bright one bedroom condo has oversized Italian tile throughout and A/C in the living room, keeping the space airy and cool. Matching granite in kitchen and bathroom lend to a cohesive, clean and modern look. Living room ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
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
Maximum occupancy strictly enforced in accordance with local ordinances.
Welcome to Hale Kai 2 in Ko Olina! Immerse yourself in this coastal getaway, where colorful landscaping and shimmering lagoon pools await. Whether you're unwinding ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
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. Hale Kai Kea also has a spacious bonus room furnished with a sofa, two twin size beds, small dining area ...
- 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
LICENSED VACATION HOME CERTIFICATE:1990/NUC-2109 - LICENSE# 90/TVU-0169 - TAX MAP KEY: 1-6-1-004:067
This home will not be affected by the new Honolulu County ban on vacation homes. Make sure when booking your vacation home the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
