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.
tour de france annemasse to morzine

14th Stage of the Tour de France 2023, © (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:

  1. PostgreSQL 14.5 + PostGIS 3.2, Ubuntu 22.04.02
  2. 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 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

Deploy 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:

ArcGIS Pro Create Enterprise Geodatabase tool setup screen
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).
screenshot shows arcGIS GPX to features pro setup for
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 graph from feature class stage_14_gpx in ArcGIS Pro
Create profile from gpx track

ArcGIS Pro - how to Create profile graph from feature class stage_14_gpx
Create profile graph from feature class stage_14_gpx

ArcGIS Pro - resulting profile graph
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;
(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;

For our cycling pros (and upcoming altitude trainings), you can find our segments – which are located between 1000 and 5000 meters – below.
Map showing segments of tour de france stage 14 between 1000 and 5000 meters from PostGIS and ArcGIS Pro combined


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:

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.