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 '%Kihei Bay Surf%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Kihei Bay Surf Studio #110 is a ground floor studio with a peek-a-boo ocean view, air conditioning, a fully-equipped kitchen (excluding dishwasher), a large flat-screen TV, DVD, telephone with free long distance calling to the US mainland and ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> Kihei
Kihei Bay Surf Studio #144 is a beautifully remodeled studio 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 pool area. This tiled unit ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> 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 as well as new faucets and a new kitchen sideboard, new stainless ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> 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 ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> 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, and a separate full bathroom. Kihei Bay Surf Studio #253 also has Cable TV/DVD player, room unit air conditioning in the ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Guests have called Kihei Bay Surf 227 “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 ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> 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 (meaning arrival is ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> 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...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> 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 ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> Kihei
You’ll wake up to a stunning view of the ocean when you vacation at Koa Lagoon 506. Guests regularly report seeing Humpback Whales, Green Sea Turtles, and Spinner Dolphins cavorting in the Pacific. Start the day with a cup of coffee on this ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Maui
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 3.
...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> Kihei
Kihei Bay Surf Studio #208 is a second floor studio that has a fully equipped kitchen and bathroom. Upgraded cable package includes extra channels, HD, DVR, Showtime, EPIX, and Music Choice. There is complimentary parking, free wifi, and free ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> 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 ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
