Spatial Database Design (PostGIS) - Point data that doesn't always have spatial data

Question

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.

Answer 1

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

Answer 2

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

PostGIS allows NULL values in geometry columns.

Answer 3

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.