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 '%Nani Kai Hale%') AND sleeps>=1 order by rating desc LIMIT 0, 15
With breathtaking ocean views from the 6th/top floor, this beautifully remodeled unit offers full beachfront access. The master bedroom features a California king bed with a en suite tiled walk-in shower. There is also a sofa sleeper in the ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
507 is a 1 bedroom, 2 bath condo- steps from the beach. See and hear the ocean waves from the living room, bedroom and private lanai. Enjoy sunsets and views of the beautiful West Maui mountains. The apartment has custom tile throughout and ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Nani Kai Hale 304 is a 1 bedroom, 2 bath condo with central air conditioning for your comfort. This well stocked, but uncluttered condo has everything you need for a relaxing vacation. Tile floors and neutral decor are perfect for the tropics. ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Nani Kai Hale 307 has an open floor plan that features a modern galley style kitchen and a spacious living room decorated with a tropical flair and with a queen sofa bed and LARGE flat panel screen tv. Split AC system installed in living room ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Nani Kai Hale #A209 is beachfront/oceanfront. Has an awesome ocean view from unit & oceanfront lanai. | Enjoy the ocean view from the amazing lanai. You can go for long walks on the sandy sugar beach. Enjoy magical sunsets, paddle ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
HI> KIHEI
Stunning luxury, ground floor condo directly on Sugar Beach, one of Maui's best beaches. The unit was completely remodeled floor to ceiling in 2017 and includes 2 bedrooms (1 king, 1 queen) each with en-suite bathrooms, a full luxury kitchen, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Nani Kai Hale #208 is a beachfront/ocean-view unit. Has an awesome ocean-view and pool view from lanai. North Kihei is an ideal location for social distancing!! Enjoy the ocean-view from the amazing lanai. You can go for long walks on the ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> KIHEI
WOW! Newly renovated and UPdated! This showplace is on the fifth floor and features panoramic views of the Pacific Ocean and the West Maui Mountains! NEW windows ensure a QUIET and serene setting. The designer kitchen is open and has top of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
A Great Location! Nani Kai Hale is on Sugar Beach- a long sandy stretch of beach which is perfect for a stroll, watching sunset or swimming in the beautiful blue Pacific Ocean. In the winter months, you can watch the humpback whales put on an...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Nani Kai Hale 405 is a 1 bedroom, 2 bath elegant yet casual condo that’s awaiting you. Enjoy your stay in this remodeled condo. Custom tile throughout, fully-stocked kitchen with blue pearl granite, unique desk area with internet, and flat ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Nani Kai Hale 601 is a 1 bedroom, 2 bath top floor condo with central A / C, light and bright tropical décor and a picture window facing the beautiful Pacific ocean. The kitchen is well equipped for preparing a quick snack or some delicious ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
HI> Kihei
We chose Nani Kai Hale due to it's fantastic beachfront location directly on Sugar Beach. Sugar Beach 3 is a beautiful white sand beach which is popular for swimming, snorkeling, and boogie boarding.
The unit was finished being completely ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
302 is a spacious 2 bedroom, 2 bath renovated condo, perfect for a family getaway. Central A / C and double-pane glass keeps the condo cool and quiet. One bedroom has a king bed another has twin beds. The living room sectional converts into two ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
The gorgeous view and amazing location will excite you at Nani Kai Hale 303 a third-floor condo featuring one bedroom, two bathrooms and sleeps four. Enjoy the beautiful ocean view from the amazing balcony! This amazing condo is the total ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Welcome to Nani Kai Hale 301, a wonderful 1 bedroom 2 full bath condo right on the Pacific Ocean! Enjoy views of both Haleakala and Molokini from the comfort of the air conditioned living room. Relax and dine in the fully remodeled kitchen and ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
