ArcGIS Feature Service to PostGIS: The QGIS Way
As a GIS newbie, I've been trying to use local open data for my own learning projects. I recently relocated to Tampa, Florida and was browsing through the City of Tampa open data portal and saw that they have a Public Art map. That looked like a cool dataset to work with but I couldn't find the data source anywhere in the portal. I reached out to the nice folks on the city's GIS team and they gave me an ArcGIS-hosted URL.
To get the public art features into PostGIS I decided to use the "ArcGIS Feature Service" option in QGIS to point to the ArcGIS API, then export the feature layer to PostGIS.
If you're a QGIS user and happen to be new to PostGIS, this is super convenient. I chose this option myself since I knew I wanted to use the public art data with other layers I'm creating in QGIS for a simple demo app.
If all you needed was to just get the data into PostGIS and you're comfortable with the command line, ogr2ogr should be sufficient for that purpose as well. (Skip all the way to the end for a quick example.)
Read on to see the steps I took in QGIS (version 3.16) and Postgres (version 13)/PostGIS 3.0 -- I'm storing my spatial data in a Crunchy Bridge instance, and I'm also hooking up pg_featureserv as the feature server for my app.
About ArcGIS Feature Services
Organizations on the ArcGIS platform can use a feature service to share their feature data with the public. The web service can have certain levels of access enabled. The API documentation is available if you want to see the details.
If we open the URL in the browser, we see the contents of as well as metadata for the web service: https://services1.arcgis.com/IbNXlmt2RVVRCZ6M/arcgis/rest/services/PublicArt_SM/FeatureServer
For example, navigate to "Public Art" under Layers and scroll to the end of the page, and you'll see what operations have been allowed for the service. We can even click on "Query" to open a GUI for querying the feature data. I personally don't find the interface to be easy to use, so I haven't spent a whole lot of time here.
Let's get going: add the Feature Service as a QGIS Layer
QGIS makes it very simple to add the ArcGIS service as a layer. In QGIS, you should see "ArcGIS Feature Service" in the Browser panel.
Right click that and we see the option to add a new connection. That opens up to the following dialog:
All we have to do is give the connection a name and add in the service URL. The web service itself is public (at the time of writing) and doesn't require authentication. Once the connection is set up, we can now drag in the "Public Art" layer to the map view in the QGIS GUI, or down to the "Layers" panel. Here's how the layer renders:
If we like, we can also preview the features data by opening the Attribute Table (right click on the layer in the Layers panel, or open "Layer" from the top menu bar):
Import the QGIS layer into PostGIS
Getting the layer into QGIS was a no-brainer. Importing the feature (vector) data into PostGIS involves a few steps, but is also pretty easy:
-
Set up a PostGIS connection
I'm saving the feature data in a tampa database in PostgreSQL, where I've enabled PostGIS by running
CREATE EXTENSION postgis. I'm not creating a new table for the layer at this time - we'll see in just a bit how QGIS takes care of that.In the Browser panel in QGIS, right click "PostGIS" and select "New Connection."
I'm using a Crunchy Bridge instance, so I grab the connection info from the Crunchy Bridge dashboard and add it in QGIS:
- Crunchy Bridge instances require an SSL connection.
- There's an option to just enter credentials under "Basic," but they get saved in plaintext with the project. Authentication configurations are more secure.
-
Export the layer to PostgreSQL
There's two ways I've found to get a QGIS layer to Postgres. One is the QGIS DB Manager plugin, and the other is through the "Export to PostgreSQL" option from the Processing Toolbox panel. I haven't figured out why, but the DB Manager didn't give me the option to create a spatial index, so I went with Export to PostgreSQL.
The feature service layer as well as the PostGIS connection I just added are available as export parameters.
I mentioned just now that I hadn't created a table in PostgreSQL to hold the feature data. Under "Table to import to" in the Export dialog, we can either select an existing table, or, if we leave the field blank or add a new table name, the processor will create a new table for us. I'm naming my table tampa_public_art.
Other parameters to set:
- Specify which layer attribute should be set as the primary key (I went with OBJECTID)
- Name the geometry column ("geom" is provided by default)
- Create a spatial indexAfter hitting "Run" we should see a message "Algorithm 'Export to PostgreSQL' finished" (this only takes a few seconds).
If we take a quick look in the database via psql:
tampa=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | spatial_ref_sys | table | postgres
public | tampa_public_art | table | art_user
(2 rows)
tampa=# \d tampa_public_art
Table "public.tampa_public_art"
Column | Type | Collation | Nullable | Default
--------------------+-----------------------------+-----------+----------+---------
objectid | bigint | | not null |
geom | geometry(Point,3857) | | |
sequence | character varying(10) | | |
artid | character varying(20) | | |
title | character varying(50) | | |
artist | character varying(50) | | |
location | character varying(120) | | |
ownedby | integer | | |
maintby | integer | | |
lastupdate | timestamp without time zone | | |
lasteditor | character varying(50) | | |
createddate | timestamp without time zone | | |
createduser | character varying(50) | | |
description | character varying(2100) | | |
primarycommunity | character varying(75) | | |
secondarycommunity | character varying(75) | | |
Indexes:
"tampa_public_art_pkey" PRIMARY KEY, btree (objectid)
"sidx_tampa_public_art_geom" gist (geom)
The geom column is set to a Point geometry type (SRID: 3857). A GiST index is created as well.
My table has 89 records, which matches what I saw with the layer in QGIS:
tampa=# SELECT COUNT(*) FROM tampa_public_art;
-[ RECORD 1 ]
count | 89
And here's what a record looks like with the geometry value:
tampa=# SELECT * FROM tampa_public_art LIMIT 1;
-[ RECORD 1 ]------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
objectid | 485
geom | 0101000020110F0000EA2B9D068A8161C10514898CB5BB4841
sequence | 1
artid | 01
title | Riverwall
artist | Bruce Marsh
location | Beneficial Bridge & Channelside, 587 Channelside Drive, Tampa, Florida, 33602
ownedby | 1
maintby | 1
lastupdate | 2019-09-05 08:34:07.168
lasteditor | PublicInfo_TampaGIS
createddate | 2019-09-05 08:34:07.168
createduser | PublicInfo_TampaGIS
description | Artist: Bruce Marsh - Located at the Southern Plaza, Riverwall provides an iconic entrance to the Riverwalk. The vision of theTampa Riverwalk is to create a vibrant and interactive waterfront experience for residents and visitors that reflects the spirit and uniqueness of Tampa. Riverwall is one of the many unique cultural components integrated into the Riverwalk. The driving idea of this piece was to create a quilt of images to celebrate and document the Hillsborough River.
primarycommunity | Channel District
secondarycommunity | Downtown
Serve PostGIS feature data using pg_featureserv
As a bonus, let's take a quick look at how we can view the PostGIS data in the browser.
I've downloaded pg_featureserv on my local machine. You can run it as a containerized service if you prefer.
All I need to do is set my DATABASE_URL environment value with my Crunchy Bridge connection info, then deploy the service from the command line:
export DATABASE_URL="postgres://fsrole:verysecurepassword@p.jip37jrksfb27klmzxr4mdkrdq.db.postgresbridge.com:5432/tampa"
./pg_featureserv.exe
Pg_featureserv automatically knows about spatial tables (i.e. tables that have a geometry column set to a geometry type as well as SRID), and we'll see this in the service's web UI. As long as the Postgres role I'm connecting with has the correct permissions, the tables will be listed under "Feature Collections."
Pg_featureserv even provides a map preview for each feature collection:
The ogr2ogr/programmatic route
ArcGIS is ubiquitous in the geospatial world, but shapefiles or file geodatabases aren't the only formats in which ArcGIS-hosted data can be imported into Postgres.
I mentioned ogr2ogr as another option. If you're given a feature service URL, you should be able to pass in that endpoint as a parameter and get GeoJSON back as a response, with something like this:
ogr2ogr \
-f PostgreSQL \
Pg:"dbname=tampa" \
GeoJSON:"https://services1.arcgis.com/IbNXlmt2RVVRCZ6M/ArcGIS/rest/services/PublicArt_SM/FeatureServer/0/query?where=OBJECTID%3DOBJECTID&objectIds=&returnGeometry=true&outFields=*&f=pgeojson" \
-nln tampa_public_art
More details about the driver used should be in the ESRIJSON / FeatureService driver section of the GDAL docs.
ogr2ogr is like the Swiss army knife of converting between geospatial formats, and you may prefer to use it in many other cases. With that said, you could probably call the endpoint from another programming language of your choice and parse the GeoJSON there. For my app, I'm planning on drawing a couple more layers with QGIS, so the QGIS route made sense for me. Pretty sure it took no more than ten mouse clicks!
Credit and thanks to my colleagues Steve Pousty, Martin Davis, and Paul Ramsey for their guidance, as well as the City of Tampa GIS team for the friendly and quick response to my request. If you found this post helpful, I recently did a writeup on loading spatial data into PostGIS. We also have plenty of other PostGIS content on the Crunchy blog! If you've also figured out a different ArcGIS to PostGIS workflow, I'd love to learn about it in the comments below.
