From Esri Geodatabase to PostGIS and GeoServer, tps and tricks
The Webinar will cover tips and tricks to export Esri Geodatabases to PostGIS and serving them via GeoServer levering GeoPackage and QGIS Graphical Modeler. A presentation and a demo will be given by Christian Picone, lead engineer at GeoSolutions.
Quick Facts Founded in 2006, ofces in Italy & US Our core products Our ofer Enterprise Support Services Deployment Subscripton Professional Training Customized Solutons GeoNod e
Trusted by more than 200 clients • UN FAO (CIOK, FIGIS, NRL, FORESTRY, ESTG), UN WFP, World Bank, DLR, EUMETSAT, JRC, ARPAT, NATO CMRE, UNESCO, IGAD, UNEP, etc.. • BAYER, BASF, DigitalGlobe, MDA, TOPCON, SwissRE, e-GEOS, Halliburton, etc..
Associatons We strongly support Open Source, it is in our core We actvely partcipate in OGC working groups and get funded to advance new open standards We support standards critcal to GEOINT
Our Distnctve Traits Lead Developers of GeoNode, GeoServer, MapStore and GeoNetwork Vast experience with Raster Serving Designed and developed JAI-Ext Designed and developed ImageIO-Ext Design and Developed most raster code in GeoTools/GeoServer Vast Experience with Vector Data Serving WFS, WMS, Vector Tiles with OGV Extensive Experience with Spatal DBMS Oracle, SQL Server, Postgis, MongoDB, etc..
Team – Key Members 27+ Staf Members, 15+ Sofware Engineers Ing. Simone Giannecchini: Founder, GeoServer PSC, GeoTools PMC, ImageIO-Ext Architect, JAI-Tools founder Dr. Luis E. Bermudez: US CEO since 2020. 2010-2020 OGC Exec, Director of the Innovaton and Compliance Programs. Ing. Andrea Aime: GeoServer Project Steering Commitee, GeoTools PMC, JAI-Tools Lead, ImageIO-Ext commiter Giovanni Allegri: Senior Project Manager, QGIS, GeoNode Ing. Alessio Fabiani: Founder, GeoServer PSC, GeoTools Commiter, MapStore Commiter Ing. Emanuele Tajariol: GeoServer Commiter, GeoTools Commiter, GeoNetwork PSC Ing Mauro Bartolomeoli: GeoServer Commiter, GeoTools Comiter, GeoBatch Commiter, MapStore2 Architect Dr. Christan Picone: Senior Lead Engineer expert in various open source products
Introduction This Webinar will cover tips and tricks to export Esri Geodatabases (with Domains and Subtypes) to PostGIS and serving them via GeoServer in an automatic way. In this webinar we are going also to explain some tricks to use QGIS to edit layers with domains and subtypes definition This is a part of a proof of concept (POC) that we are working on
Overview of the process There are sophisticate tools that can help move the data from Esri sources to PostGIS (like FME). At GeoSolutions, we try to embrace and use open source solutions as much as possible in conjunction with open standards
Overview of the process The data migration process is performed by exporting GeoPackages from the ESRI GeoDatabase and using QGIS to import them to PostGIS. In addition GeoPackages meta information is required to describes the structure of the feature classes, its domains and subtypes. These are provided by the ESRI XML workspace definition file
ESRI Domains One of the advantages of storing data in a Geodatabase is that we can define rules about how the data can be edited. We can define these rules by creating attribute domains for some fields.
ESRI Domains Attribute domains are rules that describe the legal values of a field. Multiple feature classes and tables can share domain tables stored in the database. We can implement domains creating specific tables in PostGIS and adding Foreign Key constraints in the main table.
ESRI Subtypes Esri allows us also to create subtypes for feature classes. Sometimes users need to have subsets of rows in the same feature class using different domains. Depending on the value of the subtype field, we can associate different rules and domains (and default values).
ESRI Subtypes To implement this kind of validation rule, with ESRI geodb we do not have to create separated feature classes, but we can distinguish these types of subsets from each other to establish a separated set of domains and default values using subtypes.
Key Challenge of the process The key challenge is to preserve the data model from the Esri environment to the PostGIS database. For example, porting properly subtypes and domains, that will allow clients connecting to the database (directly or through a server) to use the domains and subtypes.
PostgreSQL tables inheritance This feature can be implemented on ER databases using tables inheritance. In the case of PostgreSQL we are using declarative partitioning (by list) on the subtype field
How to represent Subtypes Example of a table with a subtype field (SUBT_NO) defining 2 subtypes (1 and 2) to apply different constraints on the “code” field
SQL Statements (TEST 1) SUBTYPE 1 => D_1 -- It is VALID INSERT INTO T_TEST (dept_nm,subt_no,code) VALUES ('GeoSolutions', 1, 1); -- It is NOT VALID INSERT INTO T_TEST (dept_nm,subt_no,code) VALUES ('GeoSolutions', 1, 12);
SQL Statements (TEST 2) SUBTYPE 2 => D_2 -- It is VALID INSERT INTO T_TEST (dept_nm,subt_no,code) VALUES ('GeoSolutions', 2, 11); -- It is NOT VALID INSERT INTO T_TEST (dept_nm,subt_no,code) VALUES ('GeoSolutions', 2, 2);
ESRI GeoDB Workspace Document Using ArcCatalog it is possible to export the geodatabase schema in XML format. The XML Workspace Document is a specification that contains: ● Schema of the geodatabase ● Data (optionally)
WorkspaceDefinition > Domains In the WorkspaceDefinition tag we have a tag named Domains (type “esri:ArrayOfDomain”) containing the list of “Domain” tags
WorkspaceDefinition > DatasetDefinitions This tag is an ArrayOfDataElement and so it contains a set of DataElements defining each Feature Class in the geodatabase
DataElement This tag is an DEFeatureClass and it contains the definition of the Feature Class, with its fields and eventually its subtype. For the data migration process we are using the following sub- elements: ● Name: name of the feature class ● Fields: it contains definition of each Field
Domain If the field has a domain constraint we have also to consider the Domain tag: We are using just the DomainName to define the table Foreign Key. Domains tables and data have been created using the Domains section
Feature Class with Subtypes When a SubType is defined for the Feature Class we have the following additional tags: ● SubtypeFieldName: this tag contains the name of the field used for subtypes definition ● DefaultSubtypeCode: the value of this tag indicates the default value for the subtype field ● Subtypes: it contains the list of subtype definitions.
Introduction For the data migration workflow we decided to use the QGIS modeler and to develop 3 algorithms: ● XML Domains Importer ● XML (+GPKG) Feature Classes Importer ● GeoServer Publisher
Main steps in the GeoServer Publisher tool These are the steps of the algorithm: ● Reads the list of features classes from the ESRI XML Workspace definition ● Uses the GeoServer information (i.e. endpoint, user, password, etc.) to connect to GeoServer via its REST API ● Checks if the PostGIS Data Store exists, otherwise it creates a new one ● Checks if the Workpsace exists otherwise it creates a new one ● For each feature class, it publishes as a new layer in GeoServer (or republish it)
QGIS Editing: Tips & Tricks To explain how we can use subtypes and domains we are going to edit the feature class named UNAC_CONOP_COGI_Operations_P and the domains defined with the field “type”. With the data migration process we already have defined for each subtypes (using the declarative partitioning) the constraints.
QGIS Editing: Tips & Tricks We correctly obtain an error beacuse the field type does not allow the code IDP for subtype 4. But working in this way could be frustrating
QGIS Editing: Tips & Tricks We are going to configure our QGIS Project to make our work a bit easier. We want to have context menu associated on the field “type” but with dynamic content based on the field “subtype”. We need to set a special widget (Value- Relation) for the field type with a filter expression on the field subtype
QGIS Editing: Tips & Tricks Now we can play with dynamic context Menu related to subtype/domain definition Feature with subtype = 4 Feature with subtype = 3
GeoServer: Tips & Tricks Current version of GeoServer cannot read and then publish partitioned table cause a change in the pgjdbc driver Don’t worry! GeoSolutions is going to fix it ASAP. Meanwhile we published layer using SQL Views
GeoServer: Tips & Tricks When we publish views in GeoServer It is important to define the field to Use as Primary Key to avoid errors using WFS. A simple way is to define a custom metadata table (gt_pk_metadata) in our database and define it in the GeoServer store
simboss
linedevth
oracle4engineer
.jpg){kind=avatar}
izzii
tmokmss
non97
kawagoi
john_smith
andpad
nagiss
hankehly
tatsuru
chrislema
samlambert
jeffersonlam
sachag
gr2m
marktimemedia
chriscoyier
sonatard
orderedlist
phodgson
tenderlove