Due to some specific terms and conditions the Countries spatial dataset cannot be distributed together with the application. In order to use Union VMS you will need to process and import this dataset manually. This section describes in detail this procedure.
Union VMS strictly requires a Countries spatial dataset in its spatial database. Without this dataset the system will not work as expected. |
The recommended Countries spatial dataset to use within the Union VMS application can be downloaded at the Eurostat website. You should download two specific datasets as described in the following table.
| Administrative or Statistical Unit | Scale | Format | File name |
|---|---|---|---|
| Countries 2014 | 1:3 million | Shapefile | CNTR_2014_03M_SH.zip |
| 1:20 million | Shapefile | CNTR_2014_20M_SH.zip |
The use of these datasets requires that you agree with some terms and conditions. Please read them carefully. |
We recommend the use of QGIS (an open source Desktop GIS) to import these files into the Union VMS spatial database. The following instructions will assume that you have downloaded and installed QGIS (available here) in your system.
) and then the browse button 
Click the "Add PostGIS layers" button (
) and then in the "New" button inside the "Connections" section and fill in all the fields with your current Union VMS settings. You can press the button "Test connect" to validate your definitions.
| Field name | Description | Example Value |
|---|---|---|
| Name | The name to be used as an alias for the connection parameters | union-vms |
| Host | The IP address or hostname of the server where the Union VMS database is deployed | localhost |
| Port | The Union VMS database server port | 5432 (if you have used doker installation this will be 25432) |
| Database | The Union VMS database name | db71u |
| Username | The name of the user for the database connection | postgres |
| Password | The password for the specified user |
). On the left panel, expand the "PostGIS" node and should see the connection node you have created in the previous step (in this case called union-vms). Expand that node and you should see all the schemas that are used within the Union VMS application (e.g. spatial, rules, mdr, asset, ...). 
To start importing the shapefile into the spatial database press the "Import layer/file" button (
). In the new window, do the following configurations
| Field name | Value |
|---|---|
| Input | CNTR_RG_03M_2014 |
| Schema | spatial |
| Table | ctr_3m |
| Primary key | gid |
| Geometry column | geom |
| Source SRID | 4258 |
| Target SRID | 4326 |
| Encoding | UTF-8 |
Be sure to check the option "Create sptatial index". |
Repeat the previous steps for the other zip file (CNTR_2014_20M_SH.zip) you have downloaded changing your configurarions according to the following:
| Field name | Value |
|---|---|
| Input | CNTR_RG_20M_2014 |
| Schema | spatial |
| Table | ctr_20m |
| Primary key | gid |
| Geometry column | geom |
| Source SRID | 4258 |
| Target SRID | 4326 |
| Encoding | UTF-8 |
Within the datasets that you have downloaded there is a DBF file (CNTR_AT_2014.dbf) that contains additional metadata that is important to have within Union VMS. The next steps are describing how this file can be imported into the database.
) , select UTF-8 as the encoding option.and press the "Browse" button. 
). On the left panel, expand the "PostGIS" node and should see the connection node you have created before (in this case called union-vms). Expand that node and you should see all the schemas that are used within the Union VMS application.). In the new window, do the following configurations| Field name | Value |
|---|---|
| Input | CNTR_AT_2014 |
| Schema | spatial |
| Table | ctr_metadata |
). On the left panel, expand the "PostGIS" node and should see the connection node you have created before (in this case called union-vms). Expand that node and you should see all the schemas that are used within the Union VMS application.To prepare the data you will need to run some SQL scripts. To do that, press the "SQL window" button (
). In the new window paste the following SQL script:
-- CREATE COUNTRIES TABLE
CREATE TABLE spatial.countries AS (SELECT a.gid, a.geom AS geom, b.geom AS geom_20, a.cntr_id, c.iso3_code AS code, c.name_engl AS name
FROM spatial.ctr_3m AS a, spatial.ctr_20m AS b, spatial.ctr_metadata AS c
WHERE a.cntr_id = b.cntr_id AND a.cntr_id = c.cntr_id);
ALTER TABLE spatial.countries OWNER TO spatial;
-- ADD NECESSARY COLUMNS FOR UNIONVMS
ALTER TABLE spatial.countries ADD COLUMN enabled character varying(1) NOT NULL default 'Y';
ALTER TABLE spatial.countries ADD COLUMN enabled_on timestamp without time zone;
-- ADD PRIMARY KEY
ALTER TABLE spatial.countries ADD PRIMARY KEY (gid);
-- ADD SEQUENCE
CREATE SEQUENCE spatial.countries_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 256
CACHE 1;
ALTER TABLE spatial.countries_seq OWNER TO spatial;
-- ADD SPATIAL INDEXES
CREATE INDEX spatial_countries_geom_index
ON spatial.countries
USING gist
(geom);
CREATE INDEX spatial_countries_geom_20_index
ON spatial.countries
USING gist
(geom_20);
-- DROP OLD TABLES
DROP TABLE spatial.ctr_3m;
DROP TABLE spatial.ctr_20m;
DROP TABLE spatial.ctr_metadata;
-- UPDATE SERVICE LAYER TABLE
INSERT INTO spatial.service_layer(name, layer_desc, provider_format_id, geo_name, srs_code, short_copyright, long_copyright, is_internal, style_geom, style_label, style_label_geom, subtype)
VALUES ('Countries', 'Countries', 2, 'uvms:countries', 4326, '© <b>Countries</b>: EuroGeographics for the administrative boundaries.', '© <b>Countries</b>: EuroGeographics for the administrative boundaries.', 'Y', 'countries', 'countries_label', 'countries_label_geom', 'others'); |
If this value is different tham 14, please update the following script by replacing 14 with your value. Run the script like you just did before this step.
-- UPDATE AREA LOCATION TYPES TABLE INSERT INTO spatial.area_location_types(service_layer_id, type_name, area_type_desc, area_db_table, is_system_wide, is_location) VALUES (14, 'COUNTRY', 'Country', 'countries', 'N', 'N'); |
You have now finished all necessary steps to setup the Countries spatial dataset in the Union VMS application.