Mercurial > gemma

comparison schema/search_functions.sql @ 3753:78ba9b2574ff

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
search: include id in search result to mark sections/stretches as selected
author Markus Kottlaender <markus@intevation.de>
date Tue, 25 Jun 2019 10:27:09 +0200
parents 71de5ce5a402
children 33180c25bd87
comparison
equal deleted inserted replaced
3752:bdc7ff183db0 3753:78ba9b2574ff
117 DECLARE 117 DECLARE
118 _result jsonb; 118 _result jsonb;
119 BEGIN 119 BEGIN
120 SELECT COALESCE(json_agg(r),'[]') 120 SELECT COALESCE(json_agg(r),'[]')
121 INTO _result 121 INTO _result
122 FROM (SELECT objnam AS name, 122 FROM (SELECT id,
123 objnam AS name,
123 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, 124 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
124 'section' AS type 125 'section' AS type
125 FROM waterway.sections 126 FROM waterway.sections
126 WHERE objnam ILIKE '%' || search_string || '%' 127 WHERE objnam ILIKE '%' || search_string || '%'
127 OR nobjnam ILIKE '%' || search_string || '%' 128 OR nobjnam ILIKE '%' || search_string || '%'
136 DECLARE 137 DECLARE
137 _result jsonb; 138 _result jsonb;
138 BEGIN 139 BEGIN
139 SELECT COALESCE(json_agg(r),'[]') 140 SELECT COALESCE(json_agg(r),'[]')
140 INTO _result 141 INTO _result
141 FROM (SELECT objnam AS name, 142 FROM (SELECT id,
143 objnam AS name,
142 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, 144 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
143 'stretch' AS type 145 'stretch' AS type
144 FROM waterway.stretches 146 FROM waterway.stretches
145 WHERE objnam ILIKE '%' || search_string || '%' 147 WHERE objnam ILIKE '%' || search_string || '%'
146 OR nobjnam ILIKE '%' || search_string || '%' 148 OR nobjnam ILIKE '%' || search_string || '%'