1

I am quite new to Postgres/PostGIS and have an open question regarding 'mixing' tables with different SRIDs. I know this is generally not recommended, but in my case, I will already be required to apply a union to those tables to calculate statistics over other non-geometry columns.

The only 'ST' operation I would like to apply to this 'merged' table is to calculate the area of each geometry without reprojecting the data to a common EPSG, as this might alter the covered area by some small fraction. (At least for now, that is the only operation I am interested in).

I didn't think about it too much while creating my table (and it seemed to work as expected), but now I am wondering whether or not I lucked out because the SRIDs I've tested can all be losslessly transferred to a common one.

I cannot find any resources that explain what happens when the rows have different SRIDs for the various operations. Are they all implicitly converted to a common reference system (maybe simply by selecting the first one it sees?)

If yes, would be the correct approach for my use case be to calculate the area beforehand, add it as an additional column and then merge those tables?

Improve this question

1 Answer 1

Reset to default
2

Generally, PostGIS enforces SRID equality for

  • predicate tests (e.g ST_Intersects)
  • aggregates (e.g. ST_Collect)
  • pairwise measurements (e.g. ST_Distance)

and all operations that require two or more geometries in comparison.

Geometric property operations, however, are executed on a per-geometry basis, i.e. they consider the given GEOMETRY/GEOGRAPHY object in isolation.

In other words, if you refer to more than one column in a function call, they must have the same SRID - but ST_Area can safely be called on a mixed-SRID column¹.

¹ This is true for Euclidean property calculations, in contrast to geodetic calculations that require the given geometries to be datum referenced (geographic reference system like EPSG:4326) - most property functions in PostGIS offer a GEOGRAPHY signature which you would not be able to use without de-projection.

Improve this answer
3
  • Great! Before I accept your answer, do you happen to know where I can find a reference for it? :) I am wondering what resource I've missed when looking for the solution. Thanks!
    – Bullfrog3655
    Commented Feb 17, 2023 at 13:00
  • @Bullfrog3655 there's none that I am aware of. Some function docs used to have a note regarding this, but that's by far not the norm. Apart from experience and knowledge of the lower-level implementations, the plain necessity of the same spatial reference system to do comparative analysis or type aggregation has to be sufficient a reference it seems...
    – geozelot
    Commented Feb 17, 2023 at 14:48
  • Thank you for following up. I agree that it makes sense for operations that work on multiple geometries to be required to have the same reference system. Though I was still quite unsure about the single geometry case.
    – Bullfrog3655
    Commented Feb 20, 2023 at 7:25

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.