[postgis-users] SRID and geometry columns

[Markus Schaber]

Hi, Josh,

Josh.Zeckser at jeppesen.com wrote:
> 
> I am planning on adding postGIS geometry columns to a database table.
> The documentation shows that I need to supply an SRID when creating the
> new Geometry column. Does this mean that all rows in the table must use
> the same SRID? In my situation, many rows use different projections and
> horizontal datums. Does this mean I would have to split those up so
> that, for example, all rows that are in NAD27 ellipsoid would be in one
> table, while others in WGS84 ellipsoid would be in another? It is
> confusing, since it appears that each entry in the geometry column also
> needs an SRID. If it is based on the row, not the table, then why supply
> an SRID when creating the column?

The OpenGIS standard requires that all values in a column have the same
SRID.

However, most of PostGIS works fine if you store geometrys with
different or even no defined SRID in the same column.

One of the most annoying problems migth be that index queries will not
work as expected, at least if your SRIDs have overlapping coordinate
areas (this is no problem if you e. G. have different Gauss-Krüger Zones
as those have distinct coordinate ranges).

The easiest workaround might be a second column that is automatically
maintained by a trigger and contains the slightly extended bbox of the
projection of the geometry into a unified SRID (e. G. WGS84), and then
make your index queries against this other column, refining your results
by ANDing with a more exact test against the real column.

Markus