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, 50
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
• Oceanfront
• Split A/C units
• King bed
• Remolded Oct 2021
• Beach access from condo complex
Fully remodeled in September 2021, this bright, beautiful one bedroom, one bathroom oceanfront property in Hale Kai O ...
- 2 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
• Oceanfront
• Split A/C units
• King bed
• Remolded June 2023
• Beach access from condo complex
Fully remodeled in June 2023, unit 218 is bright, beautiful one bedroom, one bathroom oceanfront property in Hale Kai O...
- 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...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Hale Kai - Spacious Enclosed Lanai - Sleeps 2
Welcome to Hale Kai, your “Home by the Sea” in the charming Kuilima Estates East community. This beautifully designed island-style retreat offers 875 square feet of living space, including...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kahuku
This single-level waterfront, home is thoughtfully curated & designed. New AC in every room. Featuring a full kitchen, living room, and lanai as well as 4 bedrooms and 3 bathrooms. The property is fully furnished and boasts captivating marina ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Honolulu
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
Your Private Paradise on the North Shore of Oahu
Welcome to Hale Kai Sunset Estate, the crown jewel of the North Shore of Oahu. Nestled behind a secure, gated entrance, this exquisite property offers unmatched privacy and luxury, perfect ...
- 4 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Haleiwa
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 ...
- 4 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
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
"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
Summary:
Welcome to Hale Kai in Ko Olina! If you enjoy panoramic ocean views and tiki-lined pathways to white sand beaches, Ko Olina Kai is the paradise for you. Maximum occupancy strictly enforced in accordance with local ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapolei
Summary:
Welcome to Hale Kai 2 in Ko Olina! Immerse yourself in this coastal getaway, where colorful landscaping and shimmering lagoon pools await. Maximum occupancy strictly enforced in accordance with local ordinances.
The ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
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
Hale Kai O Kihei 106
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Hale Kai O Kihei 206
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
Hale Kai 115 offers magnificent ocean views and just steps to the pool! This ground floor 1 bedroom/1 bath is a perfect getaway for couples or a small family!
As you enter, you are immediately drawn to views of Molokai, Lanai', and the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Brought to you by Paradise in Hawaii Luxury Homes
Welcome to Hale Kailani, a brand new exquisite 4 bedroom, 4 1/2 bath home located in Hapuna Estates within the Mauna Kea Resort. Prepare to embark on a truly remarkable escape where ...
- 5 bedroom
- 5 bathroom
- Sleeps 12
Hawaii> Kamuela
Hale Kai Kalani (“House of the Heavenly Sea”) is a perfect Kauai cottage situated on one and a half acres of land just up from the picturesque Rock Quarry Beach (also known as Kahili Beach). Conveniently located near Kilauea Town, home of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kilauea
Hale Kai O Kihei #116 is a ground floor, ocean view unit. It has 2 bedroom and 2 bath (each with showers), fully equipped kitchen, wireless internet service, with a brand-new split system throughout the unit. With 1 California king bed and 1 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
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
Welcome to Hale Kaimana by Gather
Wake up to breathtaking views of Diamond Head and the Pacific Ocean at Hale Kaimana, a stunning oceanfront retreat located in the prestigious Colony Surf building on Honolulu’s Gold Coast. This ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Welcome to Hale Kaimana Breeze by Gather
Experience the ultimate beachfront retreat at Hale Kaimana Breeze by Gather, a unique combination of two distinct units, Hale Kaimana and Kaimana Breeze, located in the prestigious Colony Surf ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Honolulu
