I have given your question a thought and ultimately came to the conclusion that I would store each survey into its own database.
NOTE: by database I mean a database created inside a single postgres database cluster as per the postgres terminology given here, not an entirely separate postgres process with its own users,template1,etc.
While this might sound overkill it, in fact, offers several advantages:
manageability: each survey has only one raster table with its srid which allows you to adhere as much as possible to the postgis standards of managing
data (ie: no messing with the raster_columns table or FKs or constraints. All postgis functions still work as expected)
simplicity: as long as you adopt and enforce a coherent naming strategy like: call each db as srvy_name
and then reuse the same name (ie surveydata) for all the raster tables and columns. If you're so keen (I know I would ;-)) you could also add a metadata table to each database describing
what kind of data is stored into that database, when it was last updated and so on.
Scripting/querying a database structure with such coherent naming would be easy (and pleasant).
it works as per you requirements, as long as each survey uses its own srid
scalability: it scales because you could move databases (by allocating them on different tablespaces) onto different spindles (or disks, pools, lun, depending on the storage vendor) so that I/O can be parallelized.
It would be much more difficult to move out tables from the same database to different disks
security: you can grant different permissions to different surveys by exploiting database security (as an additional layer above application)
tested: there have been reports of postgres handling thousands of databases on a single instance, see this for a reference
[this has to be tested, I know it works for geometries, don't know for rasters] you can still query (and transform) all rasters at once by creating views like the following:
create or replace view v_all_surveys_as_wgs84 as
select ST_Transform(raster, 4326) as raster_wgs84 from srvy_number1.rasterdata
union all
select ST_Transform(raster, 4326) as raster_wgs84 from srvy_number2.rasterdata
[...]
One possible argument against is that this setup is complex, but I would argue back that it is instead very simple to replicate once the first database has been established and then it can be completely managed in scripting if proper naming policy is put in place.
From postgis docs
, ST_Buffer calculations are performed in the units of the SRID. In this case, you are using a buffer of 2500 degrees, so yeah... that covers any ST_Contains. Which also speaks to your question of importance of setting an SRID. It is important, and in your case you probably need a projected coordinate system such as UTM.
As to efficiency, and whether or not you even need to segment your circles (the last argument in ST_Buffer), that depends on how the GEOS library performs those calculations. I don't have any insight on that, and even if I did, you probably will just want to test segments vs. circles on a heap of points and measure the results.
In response to the question below, use ST_Transform after ST_SetSRID and before ST_Buffer to work in proper units. This will require you to also set and transform SRID of the comparison point. Here's an example that returns one point contained, and one point not:
SELECT
ST_Contains(
ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(39.6346, 2.6308), 4326), 32632), 2500),
ST_Transform(ST_SetSRID(ST_MakePoint(39.6300, 2.6300), 4326), 32632)
) point1_is_in_buffer,
ST_Contains(
ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(39.6346, 2.6308), 4326), 32632), 2500),
ST_Transform(ST_SetSRID(ST_MakePoint(39.0000, 2.0000), 4326), 32632)
) point2_is_in_buffer;
Note that if you choose to store your Geometry in the projected coordinate system, you will be able to take advantage of spatial indexes using Gist. This can potentially reduce queries by orders of magnitude, especially on big cross joins.
Best Answer
I am assuming that by shp2postgis you actually mean shp2pgsql. If so, then the -a option will append your data into an existing table rather than replacing the data.