CYBERTEC PostgreSQL Logo

PostGIS, ArcGIS Enterprise and the Tour de France route

07.2023 / Category: / Tags: |

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, © 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:

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

PostGIS ArcGIS 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.

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.

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.

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:

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.

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

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:


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

2 responses to “PostGIS, ArcGIS Enterprise and the Tour de France route”

  1. Hey Florian. Greate article. Just to give a heads up - since 10.9 (I think) esri supports postgresql postgis without installing the st_geometry.dll, bc they wanted to support DBaaS where custom libs can't be installed. This simplifies your workflow a bit and also let's you use a more recent PG version (eg 15) since you don't need a compatible st_geom lib.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram