Upcoming Free Webinar: From Esri Geodatabase to PostGIS and GeoServer, tips and tricks
Dear Reader,
We are excited to invite you to a new GeoSolutions free webinar “From Esri Geodatabase to PostGIS and GeoServer, tips and tricks” to be held on December 3rd at 11:00 AM ET. The Webinar will cover tips and tricks to export Esri Geodatabases to PostGIS and serving them via GeoServer.
The Esri Geodatabase is the native structure of how Esri’s products are store and share among and Esri’s based infrastructure. Making a Geodatabase available to GeoServer so it can be access through WFS, without loosing any data, can be a little bit tricky. There are sophisticate tools that can help move the data from Esri sources to PostGIS. For example, FME, is such a tool that provides the ability to extract transform and load (ETL) data from one system to another. At GeoSolutions, we try to embrace and use open source solutions as much as possible in conjunction with open standards. The approach we usually follow consists of custom Python scripts using the QGIS Graphical Modeler to guide the process.
Process to publish Geodatabase data to GeoServer
The process starts by exporting the Geodatabase to GeoPackage. Also, the workspace definition is exported so the PostGIS tables can be configured properly. We use scripts guided by QGIS Modeler to process the workspace definition and create the tables in PostGIS. Once the tables are available in PostGIS, the data can be published via GeoServer and available via WFS, Vector tiles, etc. Our process deals with the common blockers such as dealing with domain and subtypes that are instinct constructs of the Esri’s geodatabase.
Domains are used to create rules regarding the value of attributes. Let’s say I’m modeling the results the US Elections per state, where I’m only going to store the result of the winning party.
Domains
I can define a table State to capture these information. And, I can also define a rule regarding the values that the Winning_Party can take, by creating a Domain for the Winning_Party Attribute. The trick is making these information available to a Client Application that can, for example, use this information to restrict values for a web form.
The other common issues is subtypes. From Esri’s documentation, here is an example of subtypes used in conjunction with domains.
Attribute domains and subtypes (http://bit.ly/esri-subtypes-domains)
Esri Geodatabases allows the creation of subtypes allowing to define entities that might have different domain in their attributes to better enforce the integrity of the data. At GeoSolutions we use declarative partitioning to model the Esri’s subtypes constructs.
Subtypes Modeling
Following the Streets example. A table Streets is created with an attribute subtype. The attribute subtype can have value Local_Subtype or Highway_Subtype. Then the following SQL statement are executed for each subtype tables
-- Parent table
CREATE TABLE Streets (id INTEGER, subtype INTEGER, code INTEGER, name VARCHAR(100)) PARTITION BY LIST(subtype);
-- 'Local' partition
CREATE TABLE Local_Streets PARTITION OF Streets FOR VALUES IN (1);
ALTER TABLE Local_Streets ADD CONSTRAINT Streets_Local_FK FOREIGN KEY(code) REFERENCES Domain_Local_streets(code);
-- 'Highway' partition
CREATE TABLE Highway_Streets PARTITION OF Streets FOR VALUES IN (2);
ALTER TABLE Highway_Streets ADD CONSTRAINT Streets_Local_FK FOREIGN KEY(code) REFERENCES Domain_Highway_streets(code);
If done properly, all the data that is transferred that has domains and subtypes will be available in PostGIS and can properly be used by tools like GeoServer.
If you want to learn more about this topic and see a demo, I invite you to join the free webinar, which will be given one of our lead engineers, Christian Picone. Register now so you will have the opportunity to hear more about this topic and ask anything you want to Christian.
Hope to see you virtually on December 3th, meanwhile stay safe and keep strong!
Cordially,
Luis
