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.
Table of Contents
14th Stage of the Tour de France 2023, © letour.fr (Image credit: ASO)
Our technical environment consists of the following components:
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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) |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
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
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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) |
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.
|
1 2 3 4 5 |
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.
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.
+43 (0) 2622 93022-0
office@cybertec.at
Yes, as long as you stay with PostGIS as "spatial type", this works out.
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.