0

I am working on creating a spatial database of county agricultural data where each county has multiple crops (30+) and for every crop there are several production metrics (yield, harvested area, economic value, etc) which are the same for all crops. I also have this information over many years, going back to 1980.

What are your thoughts for a spatial database design to allow easy querying of a production metric for a particular crop by county?

Chris W
  • 15,720
  • 2
  • 29
  • 47
RyanM
  • 103
  • 7
  • 2
    how many input datasets are you going to be working with? And do you have any idea of the types of analysis you'll be doing? – DPSSpatial_BoycottingGISSE May 14 '15 at 21:37
  • 1
    What software will use/access this database? Does it have relational capabilities? My first thought is shapefile/feature class of counties and Related tables for each crop type per year. You could also just have a ton of attributes for one feature class on a per year basis(county|cropx-met1|cropx-met2|...|cropz-met1|cropz-met2|... Lots of ways to go with this. Related: http://gis.stackexchange.com/questions/115634/ – Chris W May 15 '15 at 00:06
  • Thanks @ChrisW. You've given me some good options. I was looking for a general answer, for a relational database. Would the best solution be separate tables for each crop and year combination? That would make for quite a few tables with data going back to 1980 and 30+ crops... thanks again, I'll consider it answered. – RyanM May 15 '15 at 20:50
  • @mapBaker depends on what you mean by datasets. I have one large csv file for all crops with production data for all the years I am interested in. I also have a shapefile with the county geometries. What I would like to do is create the basic structure of a relational spatial database so that a user could query crop production data by county easily. Future steps would include the ability to calculate change in crop production by county as well. Thanks! – RyanM May 15 '15 at 20:53
  • @RyanM what locational information is in the CSV, that is, is the data by X,Y for crop locations, or are they each given a County name? – DPSSpatial_BoycottingGISSE May 15 '15 at 21:07
  • I wonder if this question might do better on http://dba.stackexchange.com/ – Chris W May 15 '15 at 22:41
  • @mapPaker the crop production statistics in the CSV are at the county level – RyanM May 20 '15 at 17:27

1 Answers1

1

Given the nature of your data and how many attributes you have, you don't have any choice but to split it up into different tables. From the ArcGIS help:

Most size limits in a database depend on the DBMS edition and hardware limitations. One exception is the number of fields (columns) supported in a table or feature class; the maximum number is 500. Be aware, though, that depending on the data types, the maximum number of columns in a table could be less than 500. Most database vendors do not recommend creating tables with over 200 columns of any data type.

You don't specify which DBMS you plan to use, so the capabilities offered might vary between choices. You mention making it Relational, which narrows down the choices a bit (ie an Esri file geodatabase isn't going to work).

Picking a DBMS is one thing, and actually organizing data in that DBMS is another. My comment mentioned Related tables from a file geodatabase perspective - you click on a county, it shows you all the records associated with that county from another table linked by a Relate. I don't know enough about database design to say what would be best (and such questions are generally closed as too broad or opinion-based here). Table per crop, table per year... there's a lot of options, and as my comment mentioned what you plan to access the database with may play a role (ie troubles GIS software has with one-to-many or many-to-many table relationships). My initial thought would be table by year with all crops, knowing that automatically puts you at 200+ fields but would be one-to-one. I can also see table by crop with all years in a single table for each crop, but again depends on how you're accessing it because that would be one-to-many.

Then you get into performance tuning...

Chris W
  • 15,720
  • 2
  • 29
  • 47
  • Thank you @ChrisW. The data are downloaded in .csv files by year, so that may be the way that I begin organizing them – RyanM May 20 '15 at 17:29
  • @RyanM An earlier comment you made said you had one large csv, perhaps indicating you had combined them then? If so, then yes, making tables out of the by year data as originally acquired might be a better way to start than combining everything to a single massive file. – Chris W May 21 '15 at 00:09
  • yes unfortunately I needed to do some analysis before setting up the database structure I am interested in creating. However if I continue with creating the relational database (most likely postgis first) then I will take your suggestion and start with tables by year. Thanks! – RyanM May 22 '15 at 15:39