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 '%Kihei Bay Surf%') AND sleeps>=1 order by rating desc LIMIT 0, 20
**Special rate now to 12/15/24!! - Book now!**
Welcome to our second-floor studio unit in South Kihei, offering a glimpse of the Pacific Ocean and a host of amenities for a comfortable stay. Step into our home and be greeted by a large,...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Kihei Bay Surf Studio #218 is an upstairs studio with vaulted ceilings and a fully equipped kitchen. It has a queen bed, sofa sleeper and sleeps 3. There is a room unit air conditioner in the living room as well as a ceiling fan and a flat ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Kihei Bay Surf Studio #213 Is a beautifully furnished studio with a full kitchen and bath, as well as a King bed and queen sofa bed that can sleep 3. Kihei Bay Surf Studio #213 has cable T.V. and complimentary Wi-Fi. Air conditioning is window ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Kihei Bay Surf Studio #250 is a freshly renovated upstairs studio with vaulted ceilings. Some of the updates include new quartz countertops in the kitchen, bathroom, and hallway, creating a new kitchen sidebar, new stainless steel appliances, a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Kihei Bay Surf Studio #144 is a beautifully remodeled studio with a new split system A/C that can sleep up to 3 with a queen bed and a queen Tommy Bahama sofa bed. The unit is conveniently located on the lower level, and the lanai overlooks the ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Kihei Bay Surf Studio #123 studio condo across the street from the beach, with tiled floors, granite countertops, air conditioning, TV, dvd, and free wireless Internet. Coin-operated laundry room on-site.
Same day bookings and those arriving...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Kihei Bay Surf Studio #238 is a remodeled upstairs studio with vaulted ceilings, a fully equipped kitchen, mountain and tennis court views. It has a king bed and a double sofa bed and it can sleep 3 people. Kihei Bay Surf Studio #238 features a...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Kihei Bay Surf Studio #253 at the Kihei Bay Surf is an upstairs studio that can sleep 2. It has a fully equipped kitchen, a queen bed, and a separate full bathroom. Kihei Bay Surf Studio #253 also has Cable TV/DVD player, room unit air ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Kihei Bay Surf Studio #109 is a ground floor studio that has a fully equipped kitchen and bathroom. There is complimentary parking and WIFI. Air conditioning is room unit. Coin-operated laundry room on-site. Kihei Bay Surf Studio #109 sleeps ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Kihei Bay Surf Studio #119 is a beautifully remodeled studio that can sleep up to 3. This tiled unit features a fully equipped kitchen with new appliances and granite countertops. It is nicely furnished and has Cable TV and a DVD player. For ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Discover paradise at Kihei Bay Surf 227, hailed by guests as "a gem in a tropical garden paradise." Tucked away amidst lush landscaping and just a short stroll from Kalepolepo Beach Park, this second-floor studio condo offers a tranquil retreat ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Guests have called Kihei Bay Surf B212 “a gem in a tropical garden paradise,” and that pretty much sums it up. It is secluded, beautifully landscaped, and a short walk from Kalepolepo Beach Park. Your 2nd floor condo has a veranda that is ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Right across from the sands of Kalepolepo Beach and close to the National Marine Humpback Whale Sanctuary you’ll find Kihei Bay Surf A209. Perfect for a romantic Maui, Hawaii getaway, this island-inspired studio serves up rest and relaxation ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
