# HG changeset patch # User Raimund Renkert # Date 1361267201 -3600 # Node ID 86e79fbb8fa312c3c32bd25446e27f87baafaa93 # Parent 63617e142dfecf3afaa8cb3e469747fe94731bec Added km filter to dc-config. * Use a new macro to get the current range. * Use min/max km to filter dc data. * Extracted new macro in user part. diff -r 63617e142dfe -r 86e79fbb8fa3 flys-artifacts/doc/conf/meta-data.xml --- a/flys-artifacts/doc/conf/meta-data.xml Fri Feb 15 10:56:08 2013 +0100 +++ b/flys-artifacts/doc/conf/meta-data.xml Tue Feb 19 10:46:41 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 - - @@ -709,23 +815,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} - - - - - - - - - - + @@ -1318,18 +1408,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}) + + + + + + + + + + @@ -1337,11 +1454,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}) @@ -1369,18 +1502,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 @@ -1397,28 +1523,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 @@ -1435,28 +1555,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 @@ -1473,43 +1586,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 + + @@ -1520,30 +1627,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 @@ -1572,25 +1672,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 @@ -1621,25 +1714,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 @@ -1669,25 +1754,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 @@ -1717,25 +1795,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 @@ -1765,24 +1836,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 @@ -1799,29 +1864,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 @@ -1851,28 +1910,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 @@ -1889,28 +1942,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 @@ -1932,28 +1978,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 @@ -1974,13 +2013,58 @@ - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +