Mercurial > gemma

comparison schema/gemma.sql @ 926:9e210b00ace9 geo-style

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into geo-style branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 08 Oct 2018 10:54:50 +0200
parents 254cd247826d 271561dce2e6
children 688e1530f66a
comparison
equal deleted inserted replaced
915:2ebf677fc2e1 926:9e210b00ace9
400 CREATE TABLE sounding_results ( 400 CREATE TABLE sounding_results (
401 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 401 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
402 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 402 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
403 date_info date NOT NULL, 403 date_info date NOT NULL,
404 UNIQUE (bottleneck_id, date_info), 404 UNIQUE (bottleneck_id, date_info),
405 area geography(POLYGON, 4326), 405 area geography(POLYGON, 4326) NOT NULL,
406 surtyp varchar REFERENCES survey_types, 406 surtyp varchar REFERENCES survey_types,
407 coverage varchar REFERENCES coverage_types, 407 coverage varchar REFERENCES coverage_types,
408 depth_reference char(3) NOT NULL REFERENCES depth_references, 408 depth_reference char(3) NOT NULL REFERENCES depth_references,
409 point_cloud geography(MULTIPOINTZ, 4326), 409 point_cloud geography(MULTIPOINTZ, 4326) NOT NULL,
410 -- XXX: We may raster the data later. 410 staging_done boolean NOT NULL DEFAULT false
411 -- sounding_data raster NOT NULL, 411 )
412 staging_done boolean NOT NULL DEFAULT false
413 )
414
415 CREATE TABLE meshes (
416 sounding_result_id int NOT NULL REFERENCES sounding_results,
417 geom geometry(polygonz) NOT NULL
418 )
419
420 CREATE INDEX meshes_gix ON meshes USING gist(geom)
421 412
422 CREATE TABLE octrees ( 413 CREATE TABLE octrees (
423 sounding_result_id int NOT NULL UNIQUE REFERENCES sounding_results, 414 sounding_result_id int NOT NULL UNIQUE REFERENCES sounding_results,
424 checksum varchar NOT NULL, 415 checksum varchar NOT NULL,
425 octree_index bytea NOT NULL 416 octree_index bytea NOT NULL
426 ) 417 )
427 418
428 CREATE TABLE sounding_results_contour_lines ( 419 CREATE TABLE sounding_results_contour_lines (
429 sounding_result_id int NOT NULL REFERENCES sounding_results, 420 sounding_result_id int NOT NULL REFERENCES sounding_results,
430 height numeric NOT NULL, 421 height numeric NOT NULL,
431 lines geography(multilinestringz, 4326) NOT NULL, 422 lines geography(multilinestring, 4326) NOT NULL,
432 UNIQUE (sounding_result_id, height) 423 UNIQUE (sounding_result_id, height)
433 ) 424 )
434 -- A view to help geoserver serve contour lines. 425 -- A view to help geoserver serve contour lines.
435 -- At least geoserver-2.13.2 does not serve type geography correctly 426 -- At least geoserver-2.13.2 does not serve type geography correctly
436 CREATE VIEW waterway.sounding_results_contour_lines_geoserver AS 427 CREATE VIEW waterway.sounding_results_contour_lines_geoserver AS
437 SELECT bottleneck_id, 428 SELECT bottleneck_id,
438 date_info, 429 date_info,
439 height, 430 height,
440 CAST(lines AS geometry(multilinestringz, 4326)) AS lines 431 CAST(lines AS geometry(multilinestring, 4326)) AS lines
441 FROM waterway.sounding_results_contour_lines cl 432 FROM waterway.sounding_results_contour_lines cl
442 JOIN waterway.sounding_results sr 433 JOIN waterway.sounding_results sr
443 ON sr.id = cl.sounding_result_id 434 ON sr.id = cl.sounding_result_id
444 435
445 -- 436 --