I have large related database table and I want to ask several spatial questions by calling on the table within ArcGIS Pro.
How do I go about it?
If I am to use SQL statements if that is the option, how will it look like to ask several questions within ArcGIS pro?
Please I need suggestions to the best possible ways to ask spatial questions within arcgis pro.
In ArcGIS Pro, spatial questions are answered using geoprocessing tools like Spatial Join, Intersect, and Summary Statistics, not with SQL statements. A pure SQL approach to spatial queries would be the domain of other GIS systems, say, PostGIS or SQL Server Spatial, that include extensions to the SQL language that include spatial operators.
Though keep an eye on ArcGIS Pro for geoprocessing leveraging spatial databases:
New options to run certain geoprocessing tools like Buffer, Spatial Join, Select, or Intersect as queries in databases that support these operations, which will result in improved performance.
Not slated for the next release which is 2.4, so hopefully by the end of the year.
Thanks for your response. But in performing queries, one can use selection by attribute which gives the option to input an SQL statement. But with this, I think there will be a lot of SQL statement depending on the question one need to query at a point in time, which i feel it will be too much. I am just thinking of a tool that perform such queries by just input of parameters from the database.
Hello @Curtis Price. I am using model builder but confuse as tools required to run in order to get my output for a question like : "What is the total sum of a single tree specie at a specific location in a particular year". I will build a back-end like GUI through the model through which the user can interact with to select the specie, location, and the year which will give output of the numbers. I hope you get what I am trying to say. Thanks. I need the tools to run the model of that type.
You are not clear if the 'database ' is spatially enabled. If so, then you can run sql queries that include spatial operators.
But if it is a non-spatial database, then you will need to use a relate to the table that joins to a featureclass and you can then run Arctools for the spatial operations. Actually it is best if you just replicate the tables (or a subset) in the geodatabase for speed and repetition, because you will need to run it many times until you get the process correct.
You might use ModelBuilder to create some workflows because inevitably there will be many steps. Or you can script the steps in python.
Python is your friend here, because there are some efficiencies possible to hold tables in memory and index them using dictionaries before running the tools. Since the tools do not optimise the query (like sql) you will need to be smart to get efficient workflows. Indexes are important, tolerances, search limits, selection sets and the right tools for the process all take experience that will be more than a week to collect. The tools often do not work as efficiently as sql queries so do them in the database first if possible.
Spreadsheets and even shapefiles should only be regarded as data sources. Immediately translate them into a file geodatabase and permanently join them to the spatial datasets.
There are huge benefits in having all the data in a flat denormalised table for analysis. You may have to normalise data for efficient maintenance, but for analysis it has to be flat. You can then index the geometry field efficiently for spatial queries and the attributes can all be indexed as well. Index everything since there is no harm on a extracted database since you are not using it in a transactional database. Don't worry about repeated columns and other relational db caveats.
You can still do subsets equivalent to where clauses and build on the because a GIS has persistence available similar to a view, called a layer, that does not need to copy the subset.
You don't say how large your database is, but if you have spreadsheets (65,000 rows max?) and shapefiles (2GB max) then I would consider that you datasets are "small". Most single operations should complete in a few minutes so a complete workflow should be able to be run in a reasonable time. When testing out the workflow, get subsets to make it run even faster
It has already been translated in file geodatabase. And also my spreadsheets is over 74,000 column and shape-files is 82 column. Anyways I have been able to join and relate them.
Right now I need to develop my own geoprocessing tools either with python or model-builder to carry out spatial query of the related table for instance " what is the population of a particular bird specie in a particular location in a certain year and month.
I will appreciate if I get a sample of python script or model-builder for such question, as I will still ask as many spatial questions but getting what the code will look like will give me a startup. Thanks.