I have a requirement to store and manage archaeological geophysics data that is collected as a rectangular array of samples -- a raster image.
- Each raster will usually 20x20 or 30x30 floating-point samples, typically sampled at 1m intervals.
- A survey will consist of one or more of these images in a given location.
- It is possible that two different surveys may take place in different countries, or areas that use different projections, but each survey will use one and only one projection.
- They're never likely to be viewed together, each survey will usually sit by itself.
- The data will only be accessed by a custom front-end, so there will be no users getting direct control of it through
- Each sample needs to be stored as it was collected, so I can't reproject it into a common CRS such as Web Mercator because one sample could end up covering more or less area than in the original projection, and analysis will need to be performed on the data.
How should I best store the data in a PostGIS Raster database? The options I have come up with are:
- Ignore SRID constraints and store all the data in one table, writing my front-end code to deal with manipulating the data in a consistent manner.
- Store all the data in one table, and rewrite the SRID constraint as a compound of SRID and survey ID.
- Through table inheritance, create a new table for each new SRID.
- Through table inheritance, create a new table for each survey.
1 and 2 break some of the nice automated parts of PostGIS, but will be otherwise hidden in front-end code. But queries will probably take slightly longer.
3 and 4 could end up with an explosion of tables that would make it harder to manage FK constraints and so on.
Practically, the number of rasters per survey is anywhere from 1 to 100 or more, and the number of surveys is likely to run into the hundreds. But the number of distinct projections is likely to remain very low, which favours 3.