Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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).

share|improve this question
add comment

4 Answers 4

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:

CREATE TABLE g (
  id Serial Primary Key,
  geog Geography(geometry, 4326)
);

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.

CREATE VIEW g_point AS
SELECT * FROM g WHERE ST_GeometryType(g::geometry) = 'ST_Point';

Best of luck!

share|improve this answer
add comment

A solution with 3 tables would be:

Create a 'generic relation' table for your locations. Like:

ID     NAME    GEOM_TYPE    GEOM_ID  
1      foo     lines         44535 

Create 3 tables, one for each type, here is the example of one called "lines":

ID       THE_GEOM
44535    (a wkb geom)

Now when you want to get the geom for a given location, query the table with the location's GEOM_TYPE and GEOM_ID.

share|improve this answer
add comment

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).

share|improve this answer
    
I'm kicking myself for having left my copy of PostGIS in Action on a shelf 400 miles away. I won't have a chance to pick it up for several weeks and I need it now! –  pyrogerg Dec 20 '12 at 23:08
add comment

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:

  • Is the location an attribute of relation sample.
  • Do all the samples have the location know and defined?
  • Why locations should have different geometry representations?
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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