Does the Postgis UpdateGeometrySRID function update the column structure as well as the data inside it?

Question

I had to manually insert a geometry from a shapefile which had SRID 0, and my column had SRID 4326, so I had to use the UpdateGeometrySRID function to update my column to SRID 0. I inserted the geometry and it worked fine, but now I need to transform everything back to SRID 4326, the column as well as the data inside it. Does UpdateGeometrySRID do that?

Additional question: how can I transform the geometry's SRID before inserting so that I don't have to modify my column?

Answer 1

How are you inserting the feature? Just with SQL?

If you're inserting geometry from text, do it with the SRID set explicitly like this:

ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4326);

Edit: you can do this with well known binary too: http://postgis.net/docs/ST_GeomFromWKB.html

ST_GeomFromWKB(bytea geom, integer srid)

And you can reproject that:

ST_Transform(ST_GeomFromWKB('geomstring', 9999),1111)

So your insert will look something like:

INSERT into "table" (
    column1, 
    column2, 
    geom) 
VALUES (
    'value1', 
    'value2', 
    ST_Transform(ST_GeomFromWKB('geomstring', 9999),1111))

Answer 2

UpdateGeometrySRID doesn't transform, it just does a

ST_SetSRID(geom,4326) and also changes the typmod/or constraint of the geometry column to 4326.

The might be all you need if your shapefile data IS in WGS 84 long lat units. If it was not, then you need to figure out the original projection and do ST_SetSRID and then transform.

It's much easier to do this if you have typmod and not both with UpdateGeomtryColumn.

So you would do:

ALTER TABLE mygis ALTER COLUMN geom TYPE geometry(POINT,4326) 
USING ST_Transform(ST_SetSRID(geom,original_srid),4326);

If you just need to change the SRID (because your data is in wgs84 long lat units) you can do the below which is equivalent to what UpdateGeometrySRID does in PostGIS 2.0+:

 ALTER TABLE mygis ALTER COLUMN geom TYPE geometry(POINT,4326) 
USING ST_SetSRID(geom,4326);