5

We have a PostGIS database with vector data with CRS British National Grid EPSG:27700. It is accessed remotely by users with QGIS.

I now need to set up a similar vector layer for Northern Ireland, which has CRS Irish Transverse Mercator EPSG: 2157

What things do I need to consider when I am deciding where to put a vector layer with a different CRS in a PostGIS database?

I have had a look at this post relating to raster data, but its very detailed and specific to that particular question How should I manage PostGIS Raster data with different projections?

  • 4
    Separate tables is probably enough, if you are explicity declaring the SRID. Having separate dbs or schemas seems like overkill, when you can easily convert from one projection to the other. Personally, I think schemas are best for separating things that are functionally different -- like postgis functions or data for different users/clients. The SRID for Ireland is 29902 (I think, for what it is worth). – John Powell Apr 4 '17 at 11:05
  • There are a lot of variables that go into the different table/schema/database/server/host decision, including the volume of data, backup complexity, data security requirements, use patterns, and personal inclination. – Vince Apr 4 '17 at 11:15
  • 1
    Thanks, I would be very interested to hear what the considerations are. I had thought that it would be a fairly straightforward answer. – Martin Hügi Apr 4 '17 at 12:09
  • 1
    Seconding what John said, absolutely no need for different databases and/or schema just because you have tables with different SRIDs. Just make sure the correct SRID is defined for each table - SELECT UpdateGeometrySRID('schema', 'table', 'geom', 27700); – Matt Apr 4 '17 at 12:12
  • 2
    That's a shame. I just wrote a long answer, but the question has been closed. – John Powell Apr 4 '17 at 12:29
3

There are no hard and fast rules for how to handle datasets with different projections, but here are some general comments on the separate database/schema question.

Separate databases

Separate databases are generally used for things that are very functionally different, where you are not likely to want to mix tables from one to the other in a query, for example, client data and static geographic data, would be good candidates to go into different databases. You don't want invoices in the same db as Brazilian trees. This is relevant because there is no built-in mechanism to do joins between tables in different DBs -- you need to use foreign data wrappers (or the older, dblink). Also, note that if you join two tables in separate DBs using foreign data wrappers, currently indexes will only be used for built-in Postgres types -- which means no spatial joins (as the spatial objects provided by Postgis are not a native type).

Different schemas

Separate schemas within the same database are used for a number of reasons.

  1. To separate things that are functionally different. You might want to put 3rd-party extension functions, such as PostGIS in their own schema (this can be useful with backups, see below). This means you are not backing up static data and functions along with dynamic data.
  2. To separate data for different users/clients. You can grant access for a user/role to specific schemas, so this can be used for security reason. You can also have a table with the same name but in a differently named schema. When you use pg_dump to backup databases or move them to new servers, there is a switch (-n) to only include certain schemas. So, if you have static and dynamic data in separate schemas, or different client's data in different schemas, it makes it easy to run partial backups or just move some data to a new machine. This way you can also avoid backing up all the system functions, which will default to the public schema.

There is no reason to need to put British and Irish data, for example, in separate schemas or databases. As the SRID should always be explicitly set, there is really nothing to be gained from separating them. You should always set the SRID explicitly on table/column creation, eg,

CREATE TABLE some_table (geom geometry (GEOMETRY_TYPE, SRID));

or failing that use UpdateGeometrySRID to change the SRID. Both of these will update the meta data geometry_columns and prevent queries with functions that accidentally use different SRIDs by giving the well-known error:

ERROR: Operation on mixed SRID geometries

And, of course, if you wanted to do a query combining British and Irish grids on tables in the same database, you can always use ST_Transform on one of the geometries, but without the pain of having to use foreign data wrappers or dblink.

So to summarize, data you are likely to want to combine in queries should go in the same database. Separate 3rd party (extensions) from your own data. Use schemas according to backup strategies and possibly, to separate tables with the same name, but from different clients.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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