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.

1. Donwloading the Countries spatial dataset

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.

2. Importing the shapefiles into Union VMS spatial database

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.

2.1 Importing shapefiles into QGIS

• Unzip the file named CNTR_2014_03M_SH.zip
• Open QGIS
• Press the "Add vector layer" button () and then the browse button

• Select the CNTR_RG_03M_2014.shp file and press the button "Open". You should now see a map of the world similar to the following:

2.2. Configuring Union VMS spatial database in QGIS

• 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

• Once you've finished, press the "Ok" button and then the "Close" button

2.3. Importing the shapefiles into the database

• In the top menu, click in "Plugins" and then in "Manage and install plugins". Check that the "DB Manager" plugin is installed and activated. If not, install it and activate it. Press the button "Close".

• Click on the "DB manager" plugin button (). 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".

• Press the "OK" button to start importing the shapefile into your database. When finished you should be able to see your new layer listed under the spatial schema.

• 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

2.4. Importing additional Countries metadata into the database

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.

• Open QGIS
• Press the "Add vector layer" button () , select UTF-8 as the encoding option.and press the "Browse" button.
• In the new window, navigate to the folder where you have unzipped the files and select the CNTR_AT_2014.dbf file and press the "Open" button. If you don't see it listed, be sure that the option "All files" is selected.

• You should now see a table listed in the left panel of QGIS.
• Click on the "DB manager" plugin 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.
• To start importing the DBF into the spatial database press the "Import layer/file" button (). In the new window, do the following configurations

  Field name	Value
  Input	CNTR_AT_2014
  Schema	spatial
  Table	ctr_metadata

• Press the "OK" button to start importing the shapefile into your database. When finished you should be able to see your new layer listed under the spatial schema.

3. Prepare the data to be used within Union VMS

• Open QGIS
• Click on the "DB manager" plugin 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.

• 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');

• Press the "Execute (F5)" button and when finished press the "Close" button.

• On the main window of the "DB manager" plugin, refresh the spatial schema and select the table named "service_layer" on the left panel. Select the "Table" tab on the right panel and check the id value of the recently created row (row where name is Countries).

• 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');

4. Update GeoServer configuration

• Login into your GeoServer installation as explained here.
• On the left panel, in the section named "Data", click on "Layers".
• On the presented layer list, search and click on the "countries" layer.

• In the new page, scroll to the bottom and click on "Reload feature type".
• Press the "Save" button.

You have now finished all necessary steps to setup the Countries spatial dataset in the Union VMS application.