PostGIS and ArcGIS Enterprise: From Annemasse to Morzine
For those who sadly missed our after-business workshop together with our new partner Synergis in June 2023, you now have the chance to catch up and learn more about how PostGIS and ArcGIS Enterprise fit and work together. I’ll not bore you with theoretical stuff but showcase how PostGIS and ArcGIS interact in a simple scenario – we’ll analyse the 14th Tour de France stage 2023 in a hybrid environment.
By hybrid I mean we’ll utilize PostGIS native geometry types instead of ESRI geometry types to analyse this challenging stage both in ArcGIS Pro and PostGIS.
14th Stage of the Tour de France 2023, © letour.fr (Image credit: ASO)
Let’s start and look at our basis for the ArcGIS Enterprise and PostGIS hybrid environment
Our technical environment consists of the following components:
- PostgreSQL 14.5 + PostGIS 3.2, Ubuntu 22.04.02
- ESRI ArcGIS Pro 3.1.2
ESRI certifies PostgreSQL and PostGIS versions and publishes recommended OS versions. I gathered this information from The PostgreSQL database requirements list at enterprise.arcgis.com and picked the latest certified versions for my initial setup.
- To completely replay this demonstration and write data from ArcGIS Pro to PostGIS, an enterprise geodatabase must be set up in PostGIS, which requires an enterprise license.
- In addition, this step involves first making our database cluster “ESRI ready” by deploying a shared library to our database server.
Fortunately, the task is straightforward and involves only the following two steps:
Download ArcGIS Pro ST_Geometry Libraries (PostgreSQL) from myesri.com
Deploy st_geometry.so to PostgreSQL’s library directory (PKGLIBDIR), in my case /usr/lib/postgresql/14/lib
Now we’re ready to setup our enterprise geodatabase within PostgreSQL by running the “Create Enterprise Geodatabase tool” from ArcGIS Pro. The following image illustrates this essential step:
Create enterprise geodatabase
For our hybrid environment, pick PostGIS as the spatial data type. This should not take too long, but let’s connect to our database cluster in the meantime to see what’s happening here.
A new database tourdefrance has been created, consisting of three database schemas, with extensions plpsql and PostGIS activated. In case you did not pick PostGIS as the spatial data type, this schema will contain a lot of functions to deal with their own spatial data types on top. Schema sde is required by the ESRI ecosystem to support enterprise features such as versioning. Schema editor was created manually in conjunction with a database role editor, to comply with ESRI’s best practices regarding ACL. Check out this webpage to learn more about ESRI’s recommendations in detail.
tourdefrance=# \dn
List of schemas
Name | Owner
--------+----------
editor | editor
public | postgres
sde | sde
(3 rows)
tourdefrance=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.2.0 | public | PostGIS geometry and geography spatial types and functions
(2 rows)
We’re now ready to look at our data sources and what we want to achieve.
First, we want to generate an altitude profile for the 14th stage of the Tour de France 2023 within ArcGIS Pro. The results are stored in an enterprise geodatabase within PostGIS.
Second, we want to select segments filtered by elevation of the generated profile within PostGIS and visualize the results in PostGIS.
Where do we start from? Our data sources…
The stage itself is served as gpx from this cycling stage page. This file already contains z values, so we can skip the task of grabbing elevation data from a digital elevation model separately. To store these gpx features as a feature class in PostGIS, we’ll utilise GPX to features within ArcGIS Pro (see image below).
Turn gpx file to feature class
Before generating our elevation profile, let’s quickly assess how this feature class has been set up and registered within PostGIS.
tourdefrance=# select * from geometry_columns where f_table_name ='stage_14_gpx'; -[ RECORD 1 ]-----+------------- f_table_catalog | tourdefrance f_table_schema | editor f_table_name | stage_14_gpx f_geometry_column | shape coord_dimension | 2 srid | 4326 type | GEOMETRY tourdefrance=# select st_geometrytype(shape) from editor.stage_14_gpx; -[ RECORD 1 ]---+-------------- st_geometrytype | ST_LineString
No pain no gain – is stage 14 of the Tour de France really that hard? ArcGIS Pro will tell us
We’ll see by creating the altitude profile first by utilizing the ArcGIS Pro Profile tool from the geoprocessing toolbox. This results in a new table feature_set_0, which can be used as a basis to create a profile graph from. See the screenshots below to understand the actions involved as well as the results.
Create profile from gpx track
Create profile graph from feature class stage_14_gpx
Resulting profile graph
Total positive elevation gain – stage 14 of the Tour de France
Let’s see how much elevation gain this stage contains in total. We start by assessing our profile data structure in PostGIS first. The profile is represented as a 4-dimensional linestring, the m value represents the distance travelled in meters.
tourdefrance=# select * from geometry_columns where f_table_name ='feature_set0'; -[ RECORD 1 ]-----+------------- f_table_catalog | tourdefrance f_table_schema | editor f_table_name | feature_set0 f_geometry_column | shape coord_dimension | 2 srid | 4326 type | GEOMETRY tourdefrance=# select st_geometrytype(shape) from editor.feature_set0; -[ RECORD 1 ]---+-------------- st_geometrytype | ST_LineString tourdefrance=# select st_ndims(shape) from editor.feature_set0; -[ RECORD 1 ] st_ndims | 4 tourdefrance=# select left(st_astext(shape),150) from editor.feature_set0; -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------ left | LINESTRING ZM (6.288850000151854 46.15779000014987 472.4484999999986 0,6.297960000208718 46.1558299998365 473.56660000000556 843.0525999999954,6.29823
For gathering the total (positive) elevation gain, we look at z values “only”, thus we can freely choose between the profile or stage table. You can see the query below:
tourdefrance=# with points as (select st_dumppoints(shape) point tourdefrance(# from editor.stage_14_gpx), tourdefrance-# zdifferences as (select st_z((point).geom) - LAG(st_z((point).geom), 1) OVER ( tourdefrance(# ORDER BY tourdefrance(# (point).path tourdefrance(# ) AS z_prev_diff tourdefrance(# from points) tourdefrance-# select sum(case when z_prev_diff > 0 then z_prev_diff end) tourdefrance-# from zdifferences; sum ------ 4281 (1 row)
Extract segments above 1000 meters by PostGIS
Still not impressed by this hilly 😊 profile – we’ll filter segments by elevation greater than 1000 meters. To do so, we utilize st_locatebetweenelevations to filter elevation data. The resulting geometry type equals MULTILINESTRING ZM. For further simple visualization in ArcGIS Pro, the query is implemented as a view.
tourdefrance=# create view editor.vw_elevation as
select row_number() over (),
st_locatebetweenelevations(shape, 1000, 5000)
from editor.feature_set0;
CREATE VIEW
For our cycling pros (and upcoming altitude trainings), you can find our segments – which are located between 1000 and 5000 meters – below.
Summary
PostGIS and ArcGIS Enterprise harmonize quite well. The fact that you can also utilize PostGIS native data types within ESRI opens further possibilities. You can profit from ESRI’s enormous feature stack and work with your spatial data within PostGIS, too.
Hope you enjoyed this blog post – stay tuned for further posts dealing with this subject area.
Take a look at some of Florian Nadler’s other GIS-related posts:
- Let’s Party and Upgrade PostgreSQL and PostGIS Together
- Analyzing Historical Flight Data with MobilityDB
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.