1

I want to generate a number of vegetation related indices (NDVI, NDTI, etc., min/max/avg/std (pixel values)) from earth observation data for a large number of polygons during the entire growing season, roughly between March and November each year. The figures are about as follows: I have about one million polygons. Every day I will get new EO data (Sentinel-1/Sentinel-2) for about 20% of them. For each of these polygons I generate 10-20 indices based on the EO data. This gives me appr. 2-4 million records, every day. That makes appr. 500 - 1000 million during just one growing season (I´ll need to store at least 5 seasons).

The infrastructure within which I have to operate is predetermined and will have to be something based on either Oracle(Locator) or PostGIS. Personally I´d prefer PostGIS since OpenSource allows for much more flexibility.

My initial idea is to create a PostGIS database, which is partitioned based on year value. I thought about creating one attribute table where I create a new row for each date and each interpreted property (polygon geometries+id are stored in a separate table). It would look something like this:

table_structure

Since I have to do different interpretations depending on the geographical zone where the polygon lies, I also thought about creating a separate table for each zone. This will however make querying more difficult.

My questions are hence:

  1. Does Oracle (Locator) or PostGIS as base for all this make sense at all or do I need to start asking for an account at an ESA DIAS/Google Earth Engine/AWS in order to be able to use cloud solutions?
  2. If this indeed makes sense, wht is your opinion on my planned table structure?
|improve this question
  • Voted to close as off topic (or too broad, or primarily opinion based?); you should definetely ask this over at Databse Administrator SE. I do believe, though, that PostgreSQL can be sufficient as your backend (I worked with these and larger sizes), in a clustered environment (on AWS maybe). Go through the DB normalization rules to see where you can set up efficient relations. Check hstore/arrays for your key/value tuples (or check if the categories can actually be the key for normalization?). Keep the tables narrow, and use multi partition hierarchies (e.g. year, then zone, then time range). – ThingumaBob Jul 9 '19 at 21:54
  • Thanks for the comments. Great to hear that the vast number of data should´nt kill my dream of using PostGIS as backend. I´ll definately have to do some thinking about the partitioning and structure in general (i.e. what other tables will come in and play a part in the game). Thank´s also for the off-topic comment, I posted the question on the DBA SE. – GISinHelsinki Jul 10 '19 at 14:13