The headline feature of the HALE 2.8.0 release is the support for reading schemas and data from SQL Databases. Out of the box, PostGreSQL is supported. How you can access a PostGreSQL/PostGIS database in HALE and work with its content is detailed in this workflow. It will take you through the basic steps for mapping a database in HALE. In this concrete case the target is the current version of the INSPIRE Planned Land Use GML schema. Thus the starting point is the “Map to INSPIRE Planned Land Use” template which already includes the target schema and the respective code lists. You can access the template projects in HALE by clicking
File -> New project from template....
1. Importing the Schema
After the template project has been loaded, we have to load the database schema as the source schema. To import the database schema (
File -> Import -> Source Schema -> From Database (JDBC)) you need the host your database is running on, the name of the database and a user and password with at least read privileges:
HALE then reads the schema from the database, and each table or view is represented as a type with its columns as properties. More information is available in the online help. At this stage, foreign key relations are not yet taken into account:
Depending on your database the list of tables might include much more than you need for the mapping. Remove any tables that are not relevant – this removes the clutter from the Schema Explorer and ensures that if you are working with source data, only the relevant data is loaded. Note that the tables are organized per schema in the dialog, you can easily select or de-select a whole schema. In our example we only keep tables from the
2. Importing sample data
An important feature of HALE is the continous testing of a defined mapping using real data. Sometimes using an entire dataset will slow down the workflow, though. To take only a subset of the data, enable instance sampling, e.g. to only load the first 1000 rows for each table. You can configure Instance Sampling by going to
Window -> Settings -> Project -> Source Data:
Now import the data from your database, as you did before with the schema. To connect to the same database as before you can use the Recent resources in the From URL tab of the Load Source Data Dialog:
Once the data is loaded you can use the usual methods in HALE to inspect and analyse it.
3. Perform the Mapping
If you have only one relevant source type – i.e. all information you need for mapping to a target type is in a single table, you can simply use the
Retype function. In most cases however, as we are dealing with a relational database, we need to Join multiple tables to combine the information.
In our example we want to map objects from the
ft_landuse table to the
ZoningElement type, but only objects that have the value
planned in the
discr column. So first we add a condition context on the
In the example database, several tables are linked to
ft_landuse that hold information we need to populate
ZoningElement properly. So we use the
Join function to combine these types for the mapping:
After selecting and confirming which types from the source need to be joined, the next step in the configuration of the Join is specifying the Join order. The first type must be the main source type that corresponds to the target, in this case ft_landuse. In other words, for every source Feature with type ft_landuse, a target Feature of type
ZoningElement will be created, with attributes of the other source features merged in. The main source type is followed by the other feature types it is directly related to. Next, for each feature type, the Join condition that specifies how it is linked to other types is specified. This is determined automatically if there are corresponding foreign key constraints defined in the database, so you can save yourselves this step if your database is set up completely. In many cases, a primary key and a foreign key need to be equivalent to have the features be joined:
After creating the type relation the transformation will produce empty
ZoningElements. So, for each type relation you should then proceed with mapping the properties. With a
Join just map the properties of the individual source types to the target type, as you would do it with a simple retype. The complete mapping for the
ZoningElement example looks like this:
As we configured HALE before to only load a sub-set of the data, the transformation may not find all relevant objects to Join and thus information may be missing in the transformed data. To transform the whole data either disable the sampling to load all data in HALE or use Transform project data in the Transformation menu to directly transform and encode the complete data. Having done that, we now have a nice, INSPIRE-compliant dataset:
This post was prepared by Simon Templer. Thanks a lot for his contribution!