I'm creating a database that will be used by researchers in population genetics with different goals and sampling strategies. The database needs to accommodate locations as points, lines, or polygons and I'm puzzling over whether those should be kept in a single "locations" table, or in separate tables for "point_locations", "line_locations", and "polygon_locations". The single-table design is appealing because it would make it simple to enforce the required one-to-one relationship between samples and their locations with a foreign key constraint. I can imagine that there may be reasons to split locations out into separate tables for points, lines, and polygons, however. If I can keep all locations' spatial data in a single column, then which geography datatype should I use (I know geometry would be more flexible, but that's a topic for another post)? I'm thinking geography(polygon,4326).
The answer depends a bit on what you're going to be using the geometries for. The database is perfectly happy to manage/index/search a generic geometry table:
Yes, it looks odd to be declaring a "geography" as a "geometry", but what's a boy to do?
The trouble with generic tables is for client software that expects to only deal in homogeneous types, not mixed. The design question becomes: is it easier to store things separately in the database and have every piece of database logic "type aware", or is it easier to have mapping queries that require homogeneity to deal with their issues?
You can perhaps get some distance by putting views onto your generic table.
Best of luck!
A solution with 3 tables would be:
Create a 'generic relation' table for your locations. Like:
Create 3 tables, one for each type, here is the example of one called "lines":
Now when you want to get the geom for a given location, query the table with the location's GEOM_TYPE and GEOM_ID.
You could create one Parent table called 'location' that doesn't actually contain any data, then create three child tables related to that table, each with constraints on geom_type, and load all data into those tables. You could also create triggers on the parent table, so that any geom type loaded into it will be automatically sorted into the appropriate child tables.
Depending on the amount of data you have, this will yield better performance when querying, as well as better data organization.
This approach is outlined in detail in PostGIS in Action, chapter 3 (I believe).
I do not recommend put all stuff into a single table. But you can create at least um column for each geometry type a point_location, line_location, polygon_location. This will avoid using the geometry type that allows geometrycollections etc.
Using separate tables, or columns would allow for additional checks on each location type.
You going to need answer question like those below: