The pipe goes to Ocean Beach, if you look carefully between the parking lot and the concrete boardwalk about midway between Lincoln and Fulton you can see two metal plates at your feet (you can see them on google maps), these are hatches that open up to a small room below with two large Fybroc pumps inside it. The pumps are drawing water from underneath the sand, one intake goes to the west (towards the ocean) and pulls a mix of ocean water and a bit of freshwater intruding from SF's groundwater. The other intake goes east and is an effort to lower the groundwater in the immediate vicinity to keep it from intruding.
There were a few reasons using this water sucked.
1) Salinity varies but was always lower than NSW and needed "brining" or raising it with a salt mix.
2) Phosphate and ammonia were frequently problems (a water treatment plant is just south of Ocean Beach, near where the Great Highway meets Lake Merced).
3) Using this water requires running the pumps 24/7, and the pumps have a minimum setting, so a constant stream of ocean water has to be delivered to CAS 24/7 whether it gets used or not. The vast majority did not get used and had to be diverted to sewer, which costs money. This is horribly inefficient, obviously.
The first switch from ocean beach water was to IO and carbon filtered SF city water. That works fine but there is a cheaper way, making your own mix using quality ingredients. IIRC the cost went from 11 cents a gallon to 7 cents a gallon doing this, but it's been 4 years since I crunched the numbers. NaCl comes from Morton, minus anti-caking agents and such, MgCl and MgSO4 come from Skyline in LA, CaCl2 comes from Cal-Chlor in Michigan, KCl comes from Brenntag Pacific in Richmond, NaHCO3 comes from Arm & Hammer ( pallets of 50# sacks from a cattle feed store in Petaluma), B and Sr come from a chemical supply house, etc etc. There are lots of other trace elements too. Got any more questions, let me know.