I am creating a spatial database using PostGIS.

I am storing the attributes and location of some species records that can be represented as points. The records are recorded from either:

a) static survey position (where previously I have just represented the species point as the static position) or b) a transect walked around the site and a position is marked when a record is recorded.

The database stores the static positions in a a separate table, so for a) above I don't actually have to store a point against each of these records as I can do a join on the species table and the static location table. If I did store the location, if the static location changed, I would have to also make sure I moved the location of all of the species records associated with that point.

The transect results are stored with spatial data as they could be recorded anywhere around the walked route and there is no other way to know where they were recorded.

I would all of this data in the same table as it seems silly to have to maintain two tables for records about the same species (would have to make changes to the table structures in two tables).

I can see three ways to deal with this:

  1. Store the species data in two separate tables, one spatial and one not.
  2. I am guessing you can't have NULL spatial values in a geometry column?
  3. Give all the records spatial data, and move the points from the species table when the static table locations change? Can you have a relationship / procedure in the database that automatically does this for you?

Which of these approaches is the most sensible? Or have I missed something. Any thoughts would be much appreciated.

  • To me the question is unclear. Could you please give an example of the two different situations you are describing. I don't know what transect is. Jan 7, 2011 at 11:16
  • A transect is the route that a surveyor will walk around a site. When they record a sighting they will record it as a point on a map.
    – James S
    Jan 7, 2011 at 12:13
  • You might take a look at the geocoding database. When you run a geocode in esri it builds a table that has placed and unplaced points. ESRI does all the creation so I am not sure about the structure but your design made me think of the similarities. HTH
    – Brad Nesom
    Jan 7, 2011 at 14:26

3 Answers 3


"Point data that doesn't always have spatial data" -- actually, if it's point data it is spatial data.

If I understand you correctly, you've got to a static survey point and record one or more species observed and a species can be observed at more than one static survey point. This is a many-to-many relationship. You need three tables for it.

And the transect observations would be the same? If the only difference is one is persistent and another is a one-time event they can be stored in the same table. You can use a field to flag a survey location as static or not. You might even infer it from time of observation (transects only have one, statics have multiple).

Here's one suggestion...

Think of it as Locations, Observations and Species. Locations have geometry (points), Species do not (unless you're talking about plants that don't move?). You observe species at locations, static or not. So, Species are related to Locations by Observations.

Store the data in these tables (not actual SQL):

  • Survey Locations (pkey id, geometry, static or transect?, other attributes...)
  • Species (pkey id, other attributes...)
  • Observations (fkey to survey locations, fkey to species, datetime, other attributes...)

In answer to your numbered questions:

  1. No, no, no. Do not duplicate data in an RDBMS. You'll have a maintenance nightmare.
  2. NULLs are legal in a geometry column. NULL means unknown but you won't have observations with unknown locations, will you? And if you haven't observed a species, there's no observations in the Observations table.
  3. More maintenance headaches, work on modeling and relating your data better.
  • In terms of statics, we cannot tell if it is the same actual animal at different static points, so I think it is a one to many relationship. In reference to transects, transects can have more than one point. The transect is the route that the surveyor walks (stored in a separate table as a polyline), I then store the point around the transect route that the surveyor finds a species.
    – James S
    Jan 10, 2011 at 10:51
  • Would it be sensible to then have some other tables off of the species table that contain more detailed information about each species record? For example, I do not store the same information about a bird as I do an amphibian or bat for instance. Or should I try and build all of the possible information into one species table with lots of rows?
    – James S
    Jan 10, 2011 at 10:52
  • 1
    Re: your first comment -- The point where you gather the data is the important thing and can be considered a survey location. Store separately in the Survey Location table from the lines representing transects.
    – Sean
    Jan 10, 2011 at 19:50
  • 1
    Re: your second comment -- Yes. You'll want to look at inheritance postgresql.org/docs/current/static/ddl-inherit.html. You might have a species table with all of the common fields and separate table for amphibians that inherits from the species table.
    – Sean
    Jan 10, 2011 at 19:58
  • Thanks for the advice Sean. I think I will go with this kind of solution. Just need to work out if QGIS, Geoserver/OpenLayers and MapInfo will handle inherited tables.
    – James S
    Jan 11, 2011 at 11:38

2) I am guessing you cant have NULL spatial values in a geometry column?

PostGIS allows NULL values in geometry columns.


I don't think that there is necessarily a correct answer. The correct solution is one that meets your data admin, maintenance, and analysis requirements.

To stereotype or generalize:

  1. A GIS person might think from a geometry-centric perspective, storing a table where each record represents a feature with additional related attribute data in another table. Think shapefile with a one-to-many relationship to a .dbf file.

  2. From a database perspective, if a geometry is just another attribute of an entity, you store all of the records and populate the geometry column for records that have features/geoms. In a case where you want to store the geometries in two different spatial reference systems, this philosophy supports the strategy of having two geometry columns on a record, one for each SRS. Because, after all, it is just an attribute.

In most of the cases that I have encountered, I have stored each feature geometry once and joined to related tables to get the appropriate related records and columns. With MapServer or the QGIS RT SQL Layer plugin, you can visualize your data based on a query tying your geometries to other tables.

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.