UGRC - Python Shorts: Loading an Open SGID Layer into pandas
There are a myriad of ways you can access data from the Open SGID. However, these all require some form of desktop client application. What if you want programmatic access to our spatial data? Because
Published·Dec 22, 2022Last modified·Jan 28, 2026Read time·3 minThere are amyriad of waysyou can access data from the Open SGID. However, these all require some form of desktop client application. What if you want programmatic access to our spatial data? Because the Open SGID uses the open-source PostGIS extension to Postgres, you can use any libraries that can load Postgres tables and process the geometry data (or just ignore that if all you want is the tabular data). In the python world, pandas DataFrames are a common and powerful structure for evaluating data. Our developer team has put together this code snippet for loading an SGID layer into either ageopandas geodataframe, opens in a new tabor an Esrispatially-enabled dataframe, opens in a new tab. Now that you’ve got the SGID layer loaded into a dataframe, you can integrate it into the rest of your application. Our colleague Erik Neemann has written a pretty in-depthblog poston harnessing the power of geopandas for GUI-less GIS analysis. RequirementsThis code requires three additional libraries besides pandas (or four if you want a spatially-enabled dataframe). These are all pip-installable fromPyPi, opens in a new tab, so they can be used in the vast majority of python environments. psycopg2: Postgres database accesssqlalchemy: Generalized access to SQL databasesgeopandas: Open source geographic dataframesarcgis(optional): ArcGIS API for Python with access to Esri’s spatially-enabled dataframesThe Codeimportgeopandasasgpdimportpandasaspdimportpsycopg2fromsqlalchemyimportcreate_enginedefload_open_sgid_table(table_name, as_esri=False):"""Load an Open SGID layer into either a geodataframe or a spatially-enabled dataframe. Args:table_name (str): The name of the Open SGID table. ex: boundaries. county_boundariesas_esri (bool): Return a spatially-enabled dataframe instead. Defaults to false. Raises:RuntimeError: If the arcgis library is not installed. """#: Create the Open SGID connection and load the data as a geodataframeengine=create_engine('postgresql+psycopg2://agrc:agrc@opensgid. ugrc. utah. gov:5432/opensgid')withengine. connect()asconnection:gdf=gpd. GeoDataFrame. from_postgis(f'select * from{table_name}', connection,geom_col='shape')#: By default, return the geodataframeifnotas_esri:returngdf#: Otherwise, convert it to a spatially-enabled dataframetry:fromarcgisimportGeoAccessor, GeoSeriesAccessorexceptImportErroraserror:raiseRuntimeError('Can\'t load arcgis library')fromerrorsdf=pd. DataFrame. spatial. from_geodataframe(gdf,column_name='shape')#: Rename the shape column to match other spatially-enabled dataframes created through the ArcGIS API for Pythonsdf. rename(columns={'shape':'SHAPE'},inplace=True)sdf. spatial. set_geometry('SHAPE')returnsdfYou can then call this function with the table name for a layer:state_boundary_df=load_open_sgid_table('boundaries. state_boundary',as_esri=True)print(state_boundary_df)Which gives usxid state SHAPE0 1 Mask {"rings": [[[5686876. 59,8859297. 030000001],[. . . 1 2 Utah {"rings": [[[295008. 5599999996,4651995. 720000. . . You’ve now got a spatial dataframe that you can explore and manipulate and integrate to your heart’s content.