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:


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 a base make sense or do I need to consider using an ESA DIAS/Google Earth Engine/AWS in order to be able to use more efficient storage solutions?
  2. If Oracle/PostGIS indeed does make sense, what is your opinion on my planned table structure?

All comments are welcome.

  • 1
    I do not have much experience with GIS stuff, but from what I have heard PostGIS is far superior to Oracle's GIS features. – a_horse_with_no_name Jul 10 '19 at 8:58
  • @a_horse_with_no_name and that's very true (biased, but still true ,) ). this was 'migrated' from GIS SE, though, since the core question is about general DB design and normalization; the actual GIS features play a minor part in the overall request for relational performance of a large database. – ThingumaBob Jul 11 '19 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.