1

PostgreSQL 13, PostGIS 3, QGIS 3.22

In a table, I had a geometry column geom with a SRID 2154.
Now I have to store geometries with different SRIDs in geom. So I did :

alter table activite.prescription
alter column geom type geometry(MULTIPOLYGON, 0),

to be able to store different SRIDs, replacing 2154 with 0.

SELECT Find_SRID('activite', 'prescription', 'geom')

returns 0 as expected.

Now I try to upload a shapefile with a SRID 5490 using QGIS and the plugin export to PostgreSQL. (The shapefile has the correct .prj file)

If the shp is imported correctly, even the geometry, its SRID is "converted" in 2154. It is not a reprojection but just a ununderstandable change. No trigger is activated.

SELECT DISTINCT ST_SRID(geom) FROM activite.prescription

returns just one SRID 2154!

When I load the layer in QGIS choosing 5490 to display the feature, the layer is indeed empty.

When I run a query like select st_setsrid(geom)... applied to the feature imported, it returns 2154 so to speak the SRID initially existing.

Why can't I keep the different SRIDs?

EDIT : Even if I use select select st_setsrid(geom, 5490)... on the imported feature to change its SRID, it doesn't change anything : the SRID stays 2154. Weird or there is an explanation I ignore (I'd prefer!)? And I have no error message.

3
  • 2
    Most GIS tools won't allow a source with multiple SRIDs, so it seems as if you are trying to force your way past protection against failure.
    – Vince
    May 13, 2022 at 13:22
  • 1
    Mixing SRIDs in a single column is a bad idea... it will make spatial operation difficult or unreliable - if even possible. You can instead use 2 columns, one for each SRID.
    – JGH
    May 13, 2022 at 13:41
  • I have to manage 7 SRIDS. What is weird to me is that if we can mix several SRIDs in a single column, SRIDs are lost after the imports. So what is the point ?
    – Leehan
    May 13, 2022 at 16:24

1 Answer 1

1

st_setsrid will return a new geometry with the updated projection. It is not an in-place update

UPDATE myTable
SET geom = st_setSRID(geom,4326);
2
  • that is not what I understand from the doc. It is used for a feature.
    – Leehan
    May 13, 2022 at 13:47
  • I agree the doc could be clearer. It subtly indicates that the function returns a geometry, and it does not mention in-place update so it shouldn't be assumed.
    – JGH
    May 13, 2022 at 14:04

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.