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.
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..
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
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
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
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
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.
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 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.
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.
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);
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);
export the geodatabase schema in XML format. The XML Workspace Document is a specification that contains: • Schema of the geodatabase • Data (optionally)
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
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
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.
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)
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.
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
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
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
lmi
otanet
bluehats
kwada
askua
hwchiu
chiroito
nomu
taro_masuda
sat
ryo_nagata_
shiozaki
bkeepers
kastner
orderedlist
chriscoyier
samlambert
danielanewman
tammyeverts
addyosmani
holman
phodgson
wjessup
matthewcrist
![a l l ! [email protected] https://www.geosolutionsgroup.com/contacts/](https://files.speakerdeck.com/presentations/ef6e61562bcb4b2c81cd22ff2909099b/slide_61.jpg){kind=link}