# HG changeset patch # User Raimund Renkert # Date 1362052044 -3600 # Node ID cad911029c9d43227db3977207138d52d3045ddd # Parent 8e52b4829cd1f5a83911341baba3e58f9d744c12# Parent 86e79fbb8fa312c3c32bd25446e27f87baafaa93 Merged with 'dc-km-filter-rr' branch. diff -r 8e52b4829cd1 -r cad911029c9d flys-artifacts/doc/conf/meta-data.xml --- a/flys-artifacts/doc/conf/meta-data.xml Thu Feb 28 12:37:07 2013 +0100 +++ b/flys-artifacts/doc/conf/meta-data.xml Thu Feb 28 12:47:24 2013 +0100 @@ -1,7 +1,10 @@ - + + Statement to load data from wsts. + + SELECT wst_columns.id AS prot_column_id, wst_columns.name AS prot_column_name, @@ -10,10 +13,49 @@ wst_ranges.a AS deffrom, wst_ranges.b AS defto FROM wst_columns, wst_ranges - WHERE wst_columns.wst_id = ${prot_id} AND wst_ranges.wst_column_id = wst_columns.id + WHERE wst_columns.wst_id = ${prot_id} + AND wst_ranges.wst_column_id = wst_columns.id + AND (${fromkm} BETWEEN wst_ranges.a AND wst_ranges.b + OR ${tokm} BETWEEN wst_ranges.a AND wst_ranges.b + OR wst_ranges.a BETWEEN ${fromkm} AND ${tokm} + OR wst_ranges.b BETWEEN ${fromkm} AND ${tokm}) ORDER by wst_columns.position - + + + + Load user specific distance information from artifact. + + + + + + + SELECT COALESCE(ld_mode, '') AS ldm, + COALESCE(ld_locations, '') AS ldl, + COALESCE(ld_from, '') AS ldf, + COALESCE(ld_to, '') AS ldt + FROM master_artifacts_range + WHERE gid = CAST(${artifact-id} as uuid) + + + + + + + + + + + + + + + + + + System part. Load data for the given river. + @@ -21,13 +63,15 @@ WHERE lower(name) LIKE lower(${river}) + - Base-data macros (mostly data imported from wst-files) + Base-data macros (mostly data imported from wst-files). + - + SELECT id AS prot_id, description AS prot_description @@ -51,11 +95,14 @@ + + + - + SELECT id AS prot_id, description AS prot_description @@ -79,12 +126,14 @@ + + - + SELECT id AS prot_id, description AS prot_description @@ -108,12 +157,14 @@ + + - + SELECT id AS prot_id, description AS prot_description @@ -137,12 +188,14 @@ + + - + SELECT id AS prot_id, description AS prot_description @@ -166,125 +219,135 @@ + - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 2 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - - + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 2 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + + - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 2 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 2 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 2 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 2 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 2 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 2 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + + - + SELECT id AS prot_id, description AS prot_description @@ -308,107 +371,149 @@ + - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 4 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 4 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 4 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 4 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 4 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 4 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + - - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 5 AND river_id = ${river_id} - - - - - - - + + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 5 AND river_id = ${river_id} + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + SELECT id AS prot_id, + description AS prot_description + FROM wsts WHERE kind = 5 AND river_id = ${river_id} + + + + + + @@ -420,42 +525,11 @@ - - - - - - - - - - - - - SELECT id AS prot_id, - description AS prot_description - FROM wsts WHERE kind = 5 AND river_id = ${river_id} - - - - - - - - - - - - - - - - - - - - - + + + + + @@ -512,97 +586,129 @@ - - - - - SELECT id AS prot_id, - description AS prot_description - FROM cross_sections WHERE river_id = ${river_id} - - - - - - - - - - + + + + + + SELECT DISTINCT ON (cs.id) + cs.id AS prot_id, + cs.description AS prot_description + FROM cross_sections cs + JOIN cross_section_lines csl ON csl.cross_section_id = cs.id + WHERE cs.river_id = ${river_id} + AND csl.km BETWEEN ${fromkm} AND ${tokm} + + + + + + + + + + + - - - - - SELECT id AS hyk_id, - description AS hyk_description - FROM hyks WHERE river_id = ${river_id} - - - - - - - - - - + + + + + + SELECT DISTINCT ON (h.id) + h.id AS hyk_id, + h.description AS hyk_description + FROM hyks h + JOIN hyk_entries he ON he.hyk_id = h.id + WHERE river_id = ${river_id} + AND he.km BETWEEN ${fromkm} AND ${tokm} + + + + + + + + + + + - - - - SELECT id AS fvmid, - description AS fvmd - FROM flow_velocity_measurements WHERE river_id = ${river_id} - - - - - - - - - SELECT id, description, station, datetime, v, w, q - FROM flow_velocity_measure_values - WHERE measurements_id = ${fvmid} - - - - - - - - - - + + + + + SELECT id AS fvmid, + description AS fvmd + FROM flow_velocity_measurements WHERE river_id = ${river_id} + + + + + + + + + SELECT id, description, station, datetime, v, w, q + FROM flow_velocity_measure_values + WHERE measurements_id = ${fvmid} + AND station BETWEEN ${fromkm} AND ${tokm} + + + + + + + + + + + + + + + + + + SELECT id AS bedh_id, + year AS bedh_year, + description AS bedh_descr + FROM bed_height_single WHERE river_id = ${river_id} + + + + + + + + + + - - - - - - - - - - + + + + + + + + + + - + River-Node - - @@ -712,23 +818,7 @@ MINFO bedheight middle - - - - SELECT id AS bedh_id, - year AS bedh_year, - description AS bedh_descr - FROM bed_height_single WHERE river_id = ${river_id} - - - - - - - - - - + @@ -1284,18 +1374,45 @@ - - - - Get the user and collection-id. - - + + + + + + Get the user and collection-id. + + SELECT u.id AS user_id, c.id AS collection_id, c.name as collection_name FROM collections c JOIN users u ON c.user_id = u.id WHERE u.gid = CAST(${user-id} AS uuid) ORDER BY c.creation DESC + + + + SELECT m.id AS a_id, + m.state AS a_state, + m.gid AS a_gid, + m.creation AS a_creation, + COALESCE(ld_mode, '') AS ld_m, + COALESCE(ld_locations, '') AS ld_l, + COALESCE(ld_from, '') AS ld_f, + COALESCE(ld_to, '') AS ld_t + FROM master_artifacts_range m + WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) + AND EXISTS ( + SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) + + + + + + + + + + @@ -1303,11 +1420,27 @@ - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation, ardg.v as gaugy, arv.v as wqsingle - FROM master_artifacts m, artifact_data ardg, artifact_data arv - WHERE m.collection_id = ${collection_id} AND m.gid = CAST(${artifact-id} AS uuid) AND ardg.artifact_id = m.id AND ardg.k = 'ld_gaugename' AND arv.artifact_id = m.id AND arv.k = 'wq_single' - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) + SELECT m.id AS a_id, + m.state AS a_state, + m.gid AS a_gid, + m.creation AS a_creation, + ardg.v AS gaugy, + arv.v AS wqsingle + FROM master_artifacts m, + artifact_data ardg, + artifact_data arv + WHERE m.collection_id = ${collection_id} + AND m.gid = CAST(${artifact-id} AS uuid) + AND ardg.artifact_id = m.id + AND ardg.k = 'ld_gaugename' + AND arv.artifact_id = m.id + AND arv.k = 'wq_single' + AND EXISTS ( + SELECT id + FROM artifact_data ad + WHERE ad.artifact_id = m.id + AND k = 'river' + AND v = ${river}) @@ -1335,18 +1468,11 @@ SHOW W-DIFFERENCES - + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - + SELECT a.gid as aid, f.id AS fid, f.name AS facet_name, f.num AS facet_num, f.description as facet_description @@ -1363,28 +1489,22 @@ - + - + SHOW REFERENCE CURVE - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - + SELECT a.gid as aid, f.id AS fid, f.name AS facet_name, f.num AS facet_num, f.description as facet_description @@ -1401,28 +1521,21 @@ - + - + SHOW COMPUTED DISCHARGE CURVES - + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - + SELECT a.gid as aid, f.id AS fid, f.name AS facet_name, f.num AS facet_num, f.description as facet_description @@ -1439,43 +1552,37 @@ - + - + CROSS SECTION - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - - - - SELECT id AS out_id - FROM outs - WHERE artifact_id = ${a_id} AND name = 'cross_section' - - - - - SELECT name AS facet_name, num as facet_num, description AS facet_description - FROM facets - WHERE out_id = ${out_id} - ORDER BY num ASC, name DESC - - + + + + SELECT id AS out_id + FROM outs + WHERE artifact_id = ${a_id} AND name = 'cross_section' + + + + + SELECT name AS facet_name, num as facet_num, description AS facet_description + FROM facets + WHERE out_id = ${out_id} + ORDER BY num ASC, name DESC + + @@ -1486,30 +1593,23 @@ - - - - - + + + + + - + - + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - - + + SELECT id AS out_id FROM outs @@ -1538,25 +1638,18 @@ - + - + - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - - + SELECT id AS out_id @@ -1587,25 +1680,17 @@ - + - + - + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - - + SELECT id AS out_id @@ -1635,25 +1720,18 @@ - + - + - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - - + SELECT id AS out_id @@ -1683,25 +1761,18 @@ - + - + - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - - + SELECT id AS out_id @@ -1731,24 +1802,18 @@ - + - + - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - + SELECT a.gid as aid, f.id AS fid, f.name AS facet_name, f.num AS facet_num, f.description as facet_description @@ -1765,29 +1830,23 @@ - + - + + WATERLEVELS - ONLY SHOW Ws - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - - + SELECT id AS out_id @@ -1817,28 +1876,22 @@ - + - + SHOW FLOODMAPS - + + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - + SELECT a.gid as aid, f.id AS fid, f.name AS facet_name, f.num AS facet_num, f.description as facet_description @@ -1855,28 +1908,21 @@ - + - + MINFO bedheight difference - + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - + SELECT a.gid as aid, f.id AS fid, f.name AS facet_name, f.num AS facet_num, f.description as facet_description @@ -1898,28 +1944,21 @@ - + - + MINFO bedheight middle - + - - SELECT m.id AS a_id, m.state AS a_state, m.gid AS a_gid, m.creation AS a_creation - FROM master_artifacts m - WHERE m.collection_id = ${collection_id} AND m.gid <> CAST(${artifact-id} AS uuid) - AND EXISTS ( - SELECT id FROM artifact_data ad WHERE ad.artifact_id = m.id AND k = 'river' AND v = ${river}) - - + SELECT a.gid as aid, f.id AS fid, f.name AS facet_name, f.num AS facet_num, f.description as facet_description @@ -1940,13 +1979,58 @@ - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +