Mercurial > gemma
annotate schema/search.sql @ 1158:da75faa8043f
added central moveMap method to pan and zoom the map on certain events
it was requested to prevent zooming out if the zoomlevel is already higher then the desired default zoom (for certain zoom/pan targets)
so there's a parameter preventZoomOut which, if set to true, lets this method only zoom in but not out.
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Tue, 13 Nov 2018 14:22:35 +0100 |
parents | 74a75a5ce770 |
children | 6590208e3ee1 |
rev | line source |
---|---|
1109
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 CREATE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 LANGUAGE plpgsql |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 AS $$ |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 DECLARE |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 _result jsonb; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 BEGIN |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 SELECT COALESCE(json_agg(r),'[]') |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 INTO _result |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 FROM (SELECT objnam AS name, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 ST_AsGeoJSON(ST_Centroid(area))::json AS geom, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 'bottleneck' AS type |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 FROM waterway.bottlenecks |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 WHERE objnam ILIKE '%' || search_string || '%' |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 ORDER BY name) r; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 RETURN _result; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 END; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 $$; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 CREATE FUNCTION search_cities(search_string text) RETURNS jsonb |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 LANGUAGE plpgsql |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 AS $$ |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 DECLARE |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 _result jsonb; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 BEGIN |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 SELECT COALESCE(json_agg(r),'[]') |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 INTO _result |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 FROM (SELECT name || ' (' || country_code || ')' AS name, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 ST_AsGeoJSON(location)::json AS geom, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 'city' AS type |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 FROM waterway.geonames |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 AND (name ILIKE '%' || search_string || '%' |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 feature_code::text), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 name) r; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 RETURN _result; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 END; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 $$; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 CREATE FUNCTION search_most(search_string text) RETURNS jsonb |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 LANGUAGE plpgsql |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 AS $$ |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 BEGIN |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 RETURN search_bottlenecks(search_string) || search_cities(search_string); |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 END; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 $$; |