DB2GIS is a module that allows you to connect tables or views from a database (we support: Oracle, PostgreSQL, MySQL) to generic layers in the System.
This allows you to give a spatial aspect to your data.
Use case
You have:
- Customer data (sales volume, category, etc.) in a non-spatial database.
- A layer of address points (e.g. downloaded from geoportal.gov.pl) as a general layer.
With DB2GIS, it is possible to combine geometry from the general layer with attributes from an external database. This makes it possible to visualise customers on a map.
Find out more
How do you connect a view to a generic layer?
Before proceeding with the configuration in the Administration Panel, you must first prepare:
– A data source in the GIS having an attribute with the identifier of the objects to be joined (join key)
– A table/view in an external database having an identifier of the objects to be joined (join key)
1. As the system administrator, go to the DB2GIS module configuration in the Administration Panel.
2. Enter the table name in the non-spatial database. Click the green plus to open the connection configuration window:
View name: a custom name entered by the administrator. The name should be a unique string without special characters.
Connection type: choose a database from a list of acceptable types, i.e. PostgreSQL, Oracle Database, SQL Server
Host: host to the database
Port: the port to the database
Username: the name of the user accessing the database
Password: password of the user accessing the database
Database name: the name of the database
Schema: the name of the schema in which the table is stored
Table name: name of the table to join
Relation field of the view: identifier with primary key, i.e. the field being the common denominator with the GIS data
Next, the administrator indicates whether he will connect to a data source in the system or to a table/view at the database level.
To connect to a data source in the GIS, select: Data source
Data source: selection of the data source from the GIS system
Data source relationship field: selection of the field, which is the primary key from the data source from the GIS system
Type of join:
More about the available join types (database systems logic)
a) INNER in the effect of a join, only the objects related by the joining key INNER JOIN Internal join – only matched rows
b) RIGHT in the effect of the join will be both the objects related by the join key, but also those that are only present in the view in the external system = we see the whole data set coming from the external system. RIGHT OUTER JOIN External right join – all rows from an external table
c) LEFT LEFT OUTER JOIN External left join – all rows from the table in the GIS system
(d) FULLFULL OUTER JOIN Full external join – all rows internally matched and all unmatched rows from both tables
Migration type:
1. overwrite
2. update (here with automatic generation of an ID field for the external data, which will be the identifier for the resulting source)
SQL query:
Configuration of calculated fields – in the form of SQL i.e. the user using SQL provides the syntax of the operation e.g. `Attribute1/Attribute2*100%`.
[] possible option to select, newly created read-only data source
New read-only data source parameter which only blocks editing of attributes.
This will result in a new data source according to the schema: EXTERNAL TABLE NAME_view
The system registers the new data source for the created view. Columns from the external data source are uneditable.
We perform the first data migration in the background. The migration always takes place to the created table. Thereafter, we perform new data migrations from the external system every set period (default: 24 hours).
In addition, the administrator has the possibility to call up data migration on demand by “refresh view”.
