Versions Compared

Old Version 9

changes.mady.by.user Hugo Martins

Saved on

compared with

New Version 10

changes.mady.by.user Hugo Martins

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • 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 nameValue
    InputCNTR_RG_20M_2014
    Schemaspatial
    Tablectr_20m
    Primary keygid
    Geometry columngeom
    Source SRID4258
    Target SRID4326
    EncodingUTF-8


2.4. Importing additional Countries metadata into the database

...

  • 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 nameValue
    InputCNTR_AT_2014
    Schemaspatial
    Tablectr_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.

...

  • 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 (Image Modified). In the new window paste the following SQL script:

    Code Block
    languagesql
    -- 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.

          Image Added

  • 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).

          Image Added

  • 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.

    Code Block
    languagesql
    -- 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.