1

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?

2 Answers 2

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))
2
  • I used the program shp2pgsql to convert the shapefile to sql format, then I copied the geometry data, which I think is in WKB format, or just regular postgis geometry format, and I inserted manually into my table, like this: insert into table (geom,name) values ('01080000002CE900......', 'New York'); How can I reproject this geometry data?
    – Koz
    Aug 6, 2015 at 16:28
  • Hi @Koz, I added some more detail. You can specify the SRID in the shp2pgsql program, but for the record, I've added a way to define your SRID in you insert statement, and how to transform to your target SRID.
    – Alex Leith
    Aug 6, 2015 at 22:45
1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.