Mercurial > gemma

comparison schema/gemma.sql @ 1446:1e19184472bf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add configuration of primary key metadata used by geoserver. This fixes the duplication of distance marks, when navigating on the map.
author Sascha Wilde <wilde@intevation.de>
date Fri, 30 Nov 2018 15:56:34 +0100
parents 0e1d89241cda
children 6ad1f431bc85
comparison
equal deleted inserted replaced
1445:a1bff497d4aa 1446:1e19184472bf
324 SELECT location_code::VARCHAR, 324 SELECT location_code::VARCHAR,
325 geom::Geometry(POINT, 4326), 325 geom::Geometry(POINT, 4326),
326 related_enc, 326 related_enc,
327 (location_code).hectometre 327 (location_code).hectometre
328 FROM waterway.distance_marks_virtual 328 FROM waterway.distance_marks_virtual
329
330 -- We need to configure primary keys for the views used by
331 -- geoserver for wfs, otherwise it will generate ids on the fly,
332 -- which will change for the same feature...
333 -- See
334 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html
335 -- for details.
336 CREATE TABLE gt_pk_metadata (
337 table_schema VARCHAR(32) NOT NULL,
338 table_name VARCHAR(32) NOT NULL,
339 pk_column VARCHAR(32) NOT NULL,
340 pk_column_idx INTEGER,
341 pk_policy VARCHAR(32),
342 pk_sequence VARCHAR(64),
343 unique (table_schema, table_name, pk_column),
344 check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
345 )
329 346
330 CREATE TABLE sections_stretches ( 347 CREATE TABLE sections_stretches (
331 id varchar PRIMARY KEY, 348 id varchar PRIMARY KEY,
332 is_section boolean NOT NULL, -- maps 'function' from interface 349 is_section boolean NOT NULL, -- maps 'function' from interface
333 stretch isrsrange, 350 stretch isrsrange,
524 waterway.sounding_results 541 waterway.sounding_results
525 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id 542 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
526 ORDER BY objnam 543 ORDER BY objnam
527 ; 544 ;
528 545
546 -- Configure primary keys for geoserver views
547 INSERT INTO waterway.gt_pk_metadata VALUES ('waterway',
548 'distance_marks_geoserver',
549 'location_code');
550
551
529 -- 552 --
530 -- Import queue and respective logging 553 -- Import queue and respective logging
531 -- 554 --
532 CREATE TYPE waterway.import_state AS ENUM ( 555 CREATE TYPE waterway.import_state AS ENUM (
533 'queued', 'running', 'failed', 556 'queued', 'running', 'failed',