7

What is the best way to look at and query data (e.g. group by) in a file geodatabase?

I'm thinking something similar to opening up a Personal Geodatabase in Access, creating queries, adding columns, etc.

The data I'm working with is too large and way too slow to query in Personal Geodatabases, but I still want to be able to view the data outside of ArcGIS and have the updates relayed back to GIS when I work with it there.

Am I better migrating the data to a proper database?

I'd rather not have to do this.

10

Personal geodatabase are great because they can be accessed outside of ArcGIS. But they are slow and, in practice, the performance starts degrading after they get larger than 400-500MB in size. If you can - do migrate to the file geodatabases.

There are many ways you can query your data using SQL-like syntax:

  1. Select By Attributes geoprocessing tool or a menu command will let you run your SQL where clause.

  2. You can use the Python window in ArcMap for running the Python code. For instance, arcpy.da.SearchCursor provides a rich interface into running your SQL queries while limiting the recordset. Look for the sql_clause argument in this help link. SQL prefix supports None, DISTINCT, and TOP. SQL postfix supports None, ORDER BY, and GROUP BY.

Some examples of the SQL queries:

  • Select distinct sub regions for all states table

The code:

import arcpy
states = r'C:\Program Files (x86)\ArcGIS\Desktop10.4\TemplateData\TemplateData.gdb\USA\states'
sql = ('DISTINCT', None)
with arcpy.da.SearchCursor(states,"SUB_REGION",sql_clause=sql) as cur:
    for row in cur:
        print row[0]

#E N Cen
#S Atl
#Mid Atl
#Mtn
#E S Cen
#N Eng
#W S Cen
#W N Cen
#Pacific
  • Group counties by state and fips code

The code:

import arcpy
counties = r'C:\Program Files (x86)\ArcGIS\Desktop10.4\TemplateData\TemplateData.gdb\USA\counties'
sql = (None, 'GROUP BY STATE_NAME, STATE_FIPS')
with arcpy.da.SearchCursor(counties,["STATE_NAME","STATE_FIPS"],sql_clause=sql) as cur:
    for row in cur:
        print row[0],row[1]
#Minnesota 27
#Washington 53
#Idaho 16
#Montana 30
#North Dakota 38
#Maine 23
#Wisconsin 55  
  1. pandas Python package provides rich interface for data analysis. This is pre-installed with ArcGIS 10.4 and can be installed easily for pre-10.4 versions of ArcGIS. There are great samples here that translate SQL queries into pandas syntax.

    • Create a data frame from a feature class

The code:

states = r'C:\Program Files (x86)\ArcGIS\Desktop10.4\TemplateData\TemplateData.gdb\USA\states'
fields = ['AREA','STATE_NAME','SUB_REGION']
df = pd.DataFrame.from_records([f for f in arcpy.da.SearchCursor(states,fields,"SUB_REGION = 'Pacific'")])
df.columns = fields
print df

    AREA  STATE_NAME SUB_REGION
0   6381      Hawaii    Pacific
1  67290  Washington    Pacific
2  97074      Oregon    Pacific
3 157776  California    Pacific
4 576594      Alaska    Pacific

Running SQL ORDER BY:

df.sort_values('AREA',ascending=False)
         AREA  STATE_NAME SUB_REGION
4  576594.104      Alaska    Pacific
3  157776.310  California    Pacific
2   97073.594      Oregon    Pacific
1   67290.061  Washington    Pacific
0    6380.614      Hawaii    Pacific

Running SQL TOP:

df.nlargest(3,'AREA')
         AREA  STATE_NAME SUB_REGION
4  576594.104      Alaska    Pacific
3  157776.310  California    Pacific
2   97073.594      Oregon    Pacific

At a last resort, you might consider to keep a copy of your data in other free DBMS (such as SQL Server Express, PostGIS, SQLite) to be able to take advantage of SQL querying. You can execute SQL queries from arcpy to DBMS using any decent Python package such as pymssql, SQLAlchemy, or psycopg2. Python also has sqlite3 module for working with SQLite databases (you can copy your data into the file-based database and run some queries there).

  • Fantastic, this is exactly the kind of explanation I was looking for... Makes things much clearer. I also had no idea you could use Pandas with ArcGIS, this is great news. Cheers – Dùn Caan Sep 20 '16 at 14:03
  • 1
    No problem! I've added more samples for pandas and also Python packages for SQL querying. I know it can be frustrating not to be able to run your GROUP BY or similar directly in the Select By Attributes... – Alex Tereshenkov Sep 20 '16 at 14:12
2

I would recommend staying with .gdb since it sounds like only ArcGIS desktop users will be querying the data. Here are some general concepts to help enhance querying:

  • To query data, first recommendation use arcpy da cursors (will only look at certain fields while querying, this should make querying tables with many fields more efficient) with if conditional logic for evaluating for values you are looking for. You may also use select layer by attribute arcpy method
  • Apply attribute and spatial indexes to improve querying
  • Use filter expression on cursors to query subsets within subsets of data
  • Thanks, but could you give me a rough example of how you would go about creating a simple query - would this be through arcpy within the ArcGIS environment? – Dùn Caan Sep 20 '16 at 13:28
  • 1
    I've updated my answer, there are many examples of querying data on this forum and others (search for arcpy query data, or select layer by attribute). Once you have done some research if you are stuck on a particular part you may ask another question and post your code noting where you are stuck. – artwork21 Sep 20 '16 at 13:35
  • Ok thanks, so basically there is no way to just quickly open up a table and create a group by query (such as in Access) - I need to script with arcpy? As far as I know Select by Attribute only allows adding a Where clause, not Group By etc. – Dùn Caan Sep 20 '16 at 13:46
  • 1
    Yes, if you are wanting to query outside of ArcGIS app. There are other open source python libraries you may use to query .gdb data, but since you have arcpy I would go with that. – artwork21 Sep 20 '16 at 13:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.