SQL to Liberate Point Location Data from Proprietary Socrata "Location" Column (PostGIS)

sql.txt

Socrata datasets often include a column of type 'Location', which may include a WGS84 latitude and longitude in parenthesis, separated by a comma.  `(latitude,longitude)` This format does not lend itself well to mapping the point data, as most mapping software is expecting separate attribute columns for latitude and longitude.  The SQL below is meant for use in a PostGIS database such as CartoDB.  It parses the latitude and longitude from the a Socrata location column, and creates a point geometry in `the_geom` using PostGIS' `ST_geomfromtext()` function.  

With this function you can liberate point location data from Socrata's unconventional format, and put it to use in maps!

UPDATE tablename 
SET the_geom = 
ST_setsrid(
  ST_geomfromtext(
    concat(
      'POINT(',
      trim(split_part(location_1, ',', 2),')'),  --parse longitude
      ' ',
      trim(split_part(location_1, ',', 1),'('),  --parse latitude
      ')'
    )
  ),
  4326
)

I really prefer ST_MakePoint to text munging:

UPDATE tablename 
SET the_geom = 
ST_SetSRID(
  ST_MakePoint(
      trim(split_part(location_1, ',', 2),')'),  -- longitude
      trim(split_part(location_1, ',', 1),'(')   -- latitude
  ),
  4326
)

Or, use super-high-test text munging...

UPDATE tablename 
SET the_geom = 
ST_GeomFromText(regexp_replace(location, '\(([0-9\.\-]+), *([0-9\.\-]+)\)', 'POINT(\2 \1)'),4326)