# HG changeset patch # User Markus Kottlaender # Date 1541491925 -3600 # Node ID 6d4cc4389c8fbd222c6502801e44fcf367f105e4 # Parent 7e788814cbde8875e3ea2e4ef8b2505e402cfd8a# Parent ef7c102497b870a4be9edf8862b4d533ae18901b merged default into store-refactoring diff -r 7e788814cbde -r 6d4cc4389c8f .hgignore --- a/.hgignore Tue Nov 06 08:18:03 2018 +0100 +++ b/.hgignore Tue Nov 06 09:12:05 2018 +0100 @@ -105,3 +105,6 @@ # End of https://www.gitignore.io/api/go,vim,node,emacs,visualstudiocode TAGS + +# Import data +schema/geonames-import/data/* diff -r 7e788814cbde -r 6d4cc4389c8f client/package.json --- a/client/package.json Tue Nov 06 08:18:03 2018 +0100 +++ b/client/package.json Tue Nov 06 09:12:05 2018 +0100 @@ -1,6 +1,6 @@ { "name": "gemmajs", - "version": "1.1.0", + "version": "1.2.0-dev", "private": true, "scripts": { "run:both": "concurrently \"../cmd/gemma/gemma\" \"vue-cli-service serve\"", diff -r 7e788814cbde -r 6d4cc4389c8f client/src/application/Topbar.vue --- a/client/src/application/Topbar.vue Tue Nov 06 08:18:03 2018 +0100 +++ b/client/src/application/Topbar.vue Tue Nov 06 09:12:05 2018 +0100 @@ -213,10 +213,13 @@ if (resultEntry.geom.type == "Point") { let view = this.openLayersMap.getView(); const currentZoom = view.getZoom(); - const newZoom = - resultEntry.type === "bottleneck" - ? Math.max(17, currentZoom) - : Math.max(15, currentZoom); + let newZoom = 11; + if (resultEntry.type === "bottleneck") + newZoom = Math.max(17, currentZoom); + if (resultEntry.type === "rhm") + newZoom = Math.max(15, currentZoom); + if (resultEntry.type === "city") + newZoom = Math.max(13, currentZoom); view.animate( { zoom: newZoom, diff -r 7e788814cbde -r 6d4cc4389c8f docker/Dockerfile.db --- a/docker/Dockerfile.db Tue Nov 06 08:18:03 2018 +0100 +++ b/docker/Dockerfile.db Tue Nov 06 09:12:05 2018 +0100 @@ -1,28 +1,32 @@ -FROM centos:7 +FROM ubuntu:bionic LABEL authors="tom.gottfried@intevation.de" -# Add the PostgreSQL PGP key to verify the official yum repository packages -RUN rpm --import https://yum.postgresql.org/RPM-GPG-KEY-PGDG-10 &&\ - # Add PostgreSQL's repository. It contains the most recent release - # of PostgreSQL, 10: - yum -q -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm &&\ - # Install PostgreSQL 10 and PostGIS - yum -q -y install postgresql10-server &&\ - yum -q -y install epel-release &&\ - yum -q -y install postgis24_10 pgtap10 +RUN apt-get update &&\ + apt-get -y install --no-install-recommends curl gnupg + +# Add PostgreSQL's repository for current PostgreSQL release and extensions: +RUN echo 'deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main' \ + >> /etc/apt/sources.list &&\ + curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | \ + sudo apt-key add - &&\ + apt-get update &&\ + apt-get -y install postgresql-11-postgis-2.5 postgresql-11-pgtap + USER postgres -ENV PGBIN /usr/pgsql-10/bin/ +ENV PGBIN /usr/lib/postgresql/11/bin +ENV PGDATA /var/lib/postgresql/11/main +ENV PGCONF /etc/postgresql/11/main/postgresql.conf -# initdb PostgreSQL 10: -ENV PGDATA /var/lib/pgsql/10/data -ENV PGCONF /var/lib/pgsql/10/data/postgresql.conf -RUN $PGBIN/initdb -E UTF8 2>&1 < /dev/null &&\ - # Adjust PostgreSQL configuration so that remote connections to the - # database are possible. - echo "host all all 0.0.0.0/0 md5" >> /var/lib/pgsql/10/data/pg_hba.conf &&\ +RUN \ + # Adjust PostgreSQL configuration to allow remote connections + echo "host all all 0.0.0.0/0 md5" \ + >> /etc/postgresql/11/main/pg_hba.conf &&\ echo "listen_addresses='*'" >> $PGCONF &&\ + # Set port to standard value + sed -i '/port/s/543./5432/' $PGCONF &&\ # Keep log on stderr to be able to use docker logs sed -i '/logging_collector/s/on/off/' $PGCONF + # Expose the PostgreSQL port EXPOSE 5432 @@ -35,4 +39,4 @@ $PGBIN/pg_ctl stop -m smart # Set the default command to run when starting the container -CMD ["/usr/pgsql-10/bin/postgres", "-D", "/var/lib/pgsql/10/data"] +CMD ["/usr/lib/postgresql/11/bin/postgres", "-D", "/etc/postgresql/11/main"] diff -r 7e788814cbde -r 6d4cc4389c8f pkg/controllers/search.go --- a/pkg/controllers/search.go Tue Nov 06 08:18:03 2018 +0100 +++ b/pkg/controllers/search.go Tue Nov 06 09:12:05 2018 +0100 @@ -30,12 +30,7 @@ FROM waterway.distance_marks_virtual WHERE (location_code).hectometre = $1) r` - searchBottleneckSQL = `SELECT COALESCE(json_agg(r),'[]') -FROM (SELECT objnam AS name, - ST_AsGeoJSON(ST_Centroid(area))::json AS geom, - 'bottleneck' AS type - FROM waterway.bottlenecks - WHERE objnam ILIKE $1) r` + searchMostSQL = `SELECT search_most($1)::text` listBottlenecksSQL = ` SELECT COALESCE(json_agg(r),'[]') @@ -95,8 +90,8 @@ // Hande search for bottlencks: err = db.QueryRowContext( req.Context(), - searchBottleneckSQL, - "%"+s.SearchString+"%", + searchMostSQL, + s.SearchString, ).Scan(&result) } diff -r 7e788814cbde -r 6d4cc4389c8f schema/demo-data/published_services.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/demo-data/published_services.sql Tue Nov 06 09:12:05 2018 +0100 @@ -0,0 +1,6 @@ +INSERT INTO sys_admin.published_services (name) VALUES + ('waterway.fairway_dimensions'), + ('waterway.distance_marks_geoserver'), + ('waterway.sounding_results_contour_lines_geoserver'), + ('waterway.bottlenecks'), + ('waterway.bottleneck_overview') diff -r 7e788814cbde -r 6d4cc4389c8f schema/geonames-import/import-geonames.sh --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/geonames-import/import-geonames.sh Tue Nov 06 09:12:05 2018 +0100 @@ -0,0 +1,147 @@ +#!/bin/bash + +# This is Free Software under GNU Affero General Public License v >= 3.0 +# without warranty, see README.md and license for details. + +# SPDX-License-Identifier: AGPL-3.0-or-later +# License-Filename: LICENSES/AGPL-3.0.txt + +# Copyright (C) 2018 by via donau +# – Österreichische Wasserstraßen-Gesellschaft mbH +# Software engineering by Intevation GmbH + +# Author(s): +# * Sascha Wilde + + +ME=$(basename $0) +MYHOME=$(realpath $(dirname $0)) +CCs="AT BG HR HU RO SK" + +# Defaults: +datadir=$(realpath "$MYHOME/data") +db=gemma +port=5432 +drop=0 + +usage() +{ + cat <&2 "$1" + exit 23 +} + +dl() { + [ -d "$datadir" ] || mkdir "$datadir" + cd "$datadir" + for cc in $CCs ; do + if [ -f "${cc}.zip" ] ; then + echo "Keeping existing ${cc}.zip ..." + else + echo "Fetching ${cc}.zip ..." + curl -O "https://download.geonames.org/export/dump/${cc}.zip" + fi + done +} + +unpack() +{ + cd "$datadir" || fatal "$datadir not missing!" + for cc in $CCs ; do + if [ -f "${cc}.zip" ] ; then + if [ -f "${cc}.txt" ] ; then + echo "Reusing existing ${cc}.txt" + else + echo "Extracting ${cc}.zip ..." + unzip "${cc}.zip" "${cc}.txt" + fi + else + fatal "Missing archive: ${cc}.zip" + fi + done +} + +drop_data() +{ + echo "Dropping geonames data..." + psql -q -p "$port" -d "$db" -c "TRUNCATE TABLE waterway.geonames;" +} + +import_data() +{ + for cc in $CCs ; do + fqfn="${datadir}/${cc}.txt" + echo "Importing $fqfn into database..." + psql -qtv ON_ERROR_STOP= -p "$port" -d "$db" \ + -v filename="$fqfn" \ + -f "$MYHOME/import-geonames.sql" + done +} + +# Parse options: + +OPTS=`getopt \ + -l help,db:,port:,datadir:,drop \ + -o D:d:p: -n "$ME" -- "$@"` +[ $? -eq 0 ] || { usage ; exit 1 ; } + +eval set -- "$OPTS" + +while true ; do + case "$1" in + --db|-d) + db="$2" + shift 2 + ;; + --port|-p) + port="$2" + shift 2 + ;; + --datadir|-D) + datadir="$2" + shift 2 + ;; + --drop) + drop=1 + shift 1 + ;; + --help) + { usage ; exit 0 ; } + ;; + --) + shift + break + ;; + esac +done + + +# Main ------------------------------------------------------------ + +if [ $drop -eq 0 ] ; then + dl + unpack + drop_data + import_data +else + drop_data +fi diff -r 7e788814cbde -r 6d4cc4389c8f schema/geonames-import/import-geonames.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/geonames-import/import-geonames.sql Tue Nov 06 09:12:05 2018 +0100 @@ -0,0 +1,57 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2018 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde + + +CREATE TEMP TABLE geonames_import_tmp ( + geonameid int PRIMARY KEY, + name varchar(200) NOT NULL, + asciiname varchar(200), + alternatenames varchar(10000), + lat float NOT NULL, + lon float NOT NULL, + feature_class char(1), + feature_code varchar(10), + country_code char(2), + cc2 varchar(200), + admin1_code varchar(20), + admin2_code varchar(80), + admin3_code varchar(20), + admin4_code varchar(20), + population text, + elevation text, + dem text, + timezone varchar(40), + modification_date timestamp +); + +COPY geonames_import_tmp FROM :'filename' WITH NULL ''; + +INSERT INTO waterway.geonames + SELECT geonameid, + name, + asciiname, + alternatenames, + ST_MakePoint(lon,lat), + feature_class, + feature_code, + country_code, + cc2, + admin1_code, + admin2_code, + admin3_code, + admin4_code, + population::int, + elevation::int, + dem::int, + timezone, + modification_date FROM geonames_import_tmp; diff -r 7e788814cbde -r 6d4cc4389c8f schema/geonames.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/geonames.sql Tue Nov 06 09:12:05 2018 +0100 @@ -0,0 +1,38 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2018 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde + + +-- Tables for data from geonames database +-- https://download.geonames.org/ +-- used for searching cities and villages... + +CREATE TABLE waterway.geonames ( + geonameid int PRIMARY KEY, + name varchar(200) NOT NULL, + asciiname varchar(200), + alternatenames varchar(10000), + location geography(POINT, 4326) NOT NULL, + feature_class char(1), + feature_code varchar(10), + country_code char(2), + cc2 varchar(200), + admin1_code varchar(20), + admin2_code varchar(80), + admin3_code varchar(20), + admin4_code varchar(20), + population int, + elevation int, + dem int, + timezone varchar(40), + modification_date timestamp +); diff -r 7e788814cbde -r 6d4cc4389c8f schema/install-db.sh --- a/schema/install-db.sh Tue Nov 06 08:18:03 2018 +0100 +++ b/schema/install-db.sh Tue Nov 06 09:12:05 2018 +0100 @@ -15,6 +15,7 @@ -p, --port=PORT connect do the postgresql cluster at PORT. Default is the postgresql standard port 5432 -D, --demo also install demo accounts and data + -G, --geonames also import geonames data --adminpw set the password to use for the "sysadmin" account. Default is a random password. --metapw set the password to use for the "meta_login" account. @@ -43,6 +44,7 @@ db=gemma port=5432 demo=0 +geonames=0 drop=0 adminpw=`genpw 15` metapw=`genpw 15` @@ -50,8 +52,8 @@ # Parse options: OPTS=`getopt \ - -l help,demo,db:,port:,drop,adminpw:,metapw: \ - -o Dd:p: -n "$ME" -- "$@"` + -l help,demo,geonames,db:,port:,drop,adminpw:,metapw: \ + -o DGd:p: -n "$ME" -- "$@"` [ $? -eq 0 ] || { usage ; exit 1 ; } eval set -- "$OPTS" @@ -78,6 +80,10 @@ demo=1 shift 1 ;; + --geonames|-G) + geonames=1 + shift 1 + ;; --drop) drop=1 shift 1 @@ -103,22 +109,29 @@ -c "SET client_min_messages TO WARNING;" \ -f "$BASEDIR/isrs.sql" \ -f "$BASEDIR/gemma.sql" \ + -f "$BASEDIR/search.sql" \ + -f "$BASEDIR/geonames.sql" \ -f "$BASEDIR/manage_users.sql" \ -f "$BASEDIR/auth.sql" \ -f "$BASEDIR/default_sysconfig.sql" + # setup initial login roles with given passwords: - psql -qt -p "$port" -d "$db" \ + psql -qt -P pager=off -p "$port" -d "$db" \ -v adminpw="$adminpw" -v metapw="$metapw" \ -f "$BASEDIR/std_login_roles.sql" if [[ $demo -eq 1 ]] ; then - psql -qv ON_ERROR_STOP= -p "$port" \ + psql -qv ON_ERROR_STOP= -p "$port" -d "$db" \ -f "$BASEDIR/demo-data/responsibility_areas.sql" \ - -f "$BASEDIR/demo-data/users.sql" -d "$db" + -f "$BASEDIR/demo-data/users.sql" \ + -f "$BASEDIR/demo-data/published_services.sql" psql -q -p "$port" -f "$BASEDIR/demo-data/roles.sql" \ -d "$db" + fi + if [[ $geonames -eq 1 ]] ; then + "$BASEDIR/geonames-import/import-geonames.sh" -p "$port" -d "$db" fi else diff -r 7e788814cbde -r 6d4cc4389c8f schema/search.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/search.sql Tue Nov 06 09:12:05 2018 +0100 @@ -0,0 +1,47 @@ +CREATE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT objnam AS name, + ST_AsGeoJSON(ST_Centroid(area))::json AS geom, + 'bottleneck' AS type + FROM waterway.bottlenecks + WHERE objnam ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + +CREATE FUNCTION search_cities(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT name || ' (' || country_code || ')' AS name, + ST_AsGeoJSON(location)::json AS geom, + 'city' AS type + FROM waterway.geonames + WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') + AND (name ILIKE '%' || search_string || '%' + OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) + ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], + feature_code::text), + name) r; + RETURN _result; +END; +$$; + +CREATE FUNCTION search_most(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +BEGIN + RETURN search_bottlenecks(search_string) || search_cities(search_string); +END; +$$;