Upgrade to Pro — share decks privately, control downloads, hide ads and more …

From Esri Geodatabase to PostGIS and GeoServer, tps and tricks

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.


Simone Giannecchini

December 04, 2020


  1. Dr. Christan Picone Dr. Luis E. Bermudez Ing. Simone Giannecchini

    From Esri Geodatabase to PostGIS and GeoServer, tps and tricks Dec 3, 2020
  2. 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
  3. 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..
  4. Industries Smart Cites Space MetOcean Defense Natural Resources Utlites Emergency

    Response Government
  5. 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
  6. 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.. 
  7. 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
  8. Webinar - Context Open source Save in license fees RoI

    – supported by open-source community
  9. Webinar - Context Open source Save in license fees RoI

    – supported by open-source community
  10. Webinar - Context Open source Save in license fees RoI

    – supported by open-source community
  11. GeoSolutions  Founded in Italy in late 2006  Expertise

    • Image Processing, GeoSpatial Data Fusion • Java, Java Enterprise, C++, Python • JPEG2000, JPIP, Advanced 2D visualization  Supporting/Developing FOSS4G projects  GeoServer, MapStore  GeoNetwork, GeoNode, Ckan  Clients  Public Agencies  Private Companies  http://www.geo-solutions.it
  12. 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
  13. 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
  14. 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
  15. 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.
  16. 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.
  17. ESRI Domains

  18. 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).
  19. 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.
  20. PostgreSQL

  21. 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.
  22. 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
  23. 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
  24. SQL Statements (D_1) Create the Domain Table D_1 and its

  25. SQL Statements (D_2) Create the Domain Table D_2 and its

  26. SQL Statements (T_TEST) We create the Table T_TEST and define

    different rules on Subtypes: • 1 => D_1 • 2 => D_2
  27. 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);
  28. 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);
  29. PostGIS tables inheritance Parent Layer Children Layers With different constraints

  30. Workspace Definition

  31. 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)
  32. WorkspaceDefinition > Domains In the WorkspaceDefinition tag we have a

    tag named Domains (type “esri:ArrayOfDomain”) containing the list of “Domain” tags
  33. CodedValues The tag CodedValues contain list of item to insert

    in the domain table
  34. WorkspaceDefinition > DatasetDefinitions This tag is an ArrayOfDataElement and so

    it contains a set of DataElements defining each Feature Class in the geodatabase
  35. 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
  36. Fields > FieldArray > Field

  37. GeometryDef If the field is a Geometry we have also

    a GeometryDef section:
  38. 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
  39. 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.
  40. Subtypes TAG For each subtype we need: • its name

    (SubTypeName) • its code (SubTypeCode) • Info about the field-domain association list (FieldInfos)
  41. Subtype Definition (example)

  42. QGIS Modeler

  43. 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
  44. Definition of the workflow

  45. QGIS Algorithms panel These tools are available from the scripts

    group of the QGIS Algorithms panel
  46. XML Domains Importer

  47. XML Feature Classes Importer

  48. GeoServer Publisher

  49. 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)
  50. The Process GeoServer PostGIS Esri Workspace Definition GeoPackage QGIS model

  51. Playing with data

  52. 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.
  53. 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
  54. 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
  55. QGIS Editing: Tips & Tricks ?

  56. QGIS Editing: Tips & Tricks Layer > Create Vector >

    New Virtual Layer Original Tables SQL definition to add on the fly the fk field
  57. 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
  58. 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
  59. 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
  60. GeoServer: Tips & Tricks CREATE TABLE gt_pk_metadata ( table_schema VARCHAR(32)

    NOT NULL, table_name VARCHAR(32) NOT NULL, pk_column VARCHAR(32) NOT NULL, pk_column_idx INTEGER, pk_policy VARCHAR(32), pk_sequence VARCHAR(64), unique (table_schema, table_name, pk_column), check (pk_policy in ('sequence', 'assigned', 'autogenerated')) ); insert into gt_pk_metadata (table_schema, table_name, pk_column) values ('public', 'myview', 'mypkcolumn')
  61. GeoServer: Tips & Tricks

  62. a l l ! info@geo-solutions.it https://www.geosolutionsgroup.com/contacts/