From: Sarah Hoffmann Date: Sun, 13 Dec 2020 08:48:21 +0000 (+0100) Subject: Merge pull request #2105 from lonvia/fix-use-of-records X-Git-Tag: v3.6.0~1 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/eed2e3f2a8d52c1729f41da435cc8ee397fdee94?hp=76cd8bf258d68434239e9aab254bd52dd3ed9926 Merge pull request #2105 from lonvia/fix-use-of-records Use a typed record for place info in get_addressdata --- diff --git a/.github/actions/setup-postgresql/action.yml b/.github/actions/setup-postgresql/action.yml index 98086f2d..0742e663 100644 --- a/.github/actions/setup-postgresql/action.yml +++ b/.github/actions/setup-postgresql/action.yml @@ -1,26 +1,45 @@ name: 'Setup Postgresql and Postgis' +inputs: + postgresql-version: + description: 'Version of PostgreSQL to install' + required: true + postgis-version: + description: 'Version of Postgis to install' + required: true + runs: using: "composite" steps: - - name: Install postgis + - name: Remove existing PostgreSQL + run: | + sudo apt-get update -qq + sudo apt-get purge -yq postgresql* + shell: bash + + - name: Install PostgreSQL run: | - sudo apt-get update -qq - sudo apt-get install -y -qq postgresql-13-postgis-3 postgresql-13-postgis-3-scripts postgresql-server-dev-13 + sudo apt-get install -y -qq --no-install-suggests --no-install-recommends postgresql-client-${PGVER} postgresql-${PGVER}-postgis-${POSTGISVER} postgresql-${PGVER}-postgis-${POSTGISVER}-scripts postgresql-contrib-${PGVER} postgresql-${PGVER} postgresql-server-dev-${PGVER} shell: bash + env: + PGVER: ${{ inputs.postgresql-version }} + POSTGISVER: ${{ inputs.postgis-version }} - name: Adapt postgresql configuration run: | - echo 'fsync = off' | sudo tee /etc/postgresql/13/main/conf.d/local.conf - echo 'synchronous_commit = off' | sudo tee -a /etc/postgresql/13/main/conf.d/local.conf - echo 'full_page_writes = off' | sudo tee -a /etc/postgresql/13/main/conf.d/local.conf - echo 'shared_buffers = 1GB' | sudo tee -a /etc/postgresql/13/main/conf.d/local.conf + echo 'fsync = off' | sudo tee /etc/postgresql/${PGVER}/main/conf.d/local.conf + echo 'synchronous_commit = off' | sudo tee -a /etc/postgresql/${PGVER}/main/conf.d/local.conf + echo 'full_page_writes = off' | sudo tee -a /etc/postgresql/${PGVER}/main/conf.d/local.conf + echo 'shared_buffers = 1GB' | sudo tee -a /etc/postgresql/${PGVER}/main/conf.d/local.conf + echo 'port = 5432' | sudo tee -a /etc/postgresql/${PGVER}/main/conf.d/local.conf shell: bash + env: + PGVER: ${{ inputs.postgresql-version }} - name: Setup database run: | - sudo systemctl start postgresql + sudo systemctl restart postgresql sudo -u postgres createuser -S www-data sudo -u postgres createuser -s runner shell: bash diff --git a/.github/workflows/ci-tests.yml b/.github/workflows/ci-tests.yml index f1da4616..dd97aefa 100644 --- a/.github/workflows/ci-tests.yml +++ b/.github/workflows/ci-tests.yml @@ -6,6 +6,15 @@ jobs: tests: runs-on: ubuntu-20.04 + strategy: + matrix: + postgresql: [9.5, 13] + include: + - postgresql: 9.5 + postgis: 2.5 + - postgresql: 13 + postgis: 3 + steps: - uses: actions/checkout@v2 with: @@ -25,6 +34,9 @@ jobs: key: nominatim-data-${{ steps.get-date.outputs.date }} - uses: ./.github/actions/setup-postgresql + with: + postgresql-version: ${{ matrix.postgresql }} + postgis-version: ${{ matrix.postgis }} - uses: ./.github/actions/build-nominatim - name: Install test prerequsites @@ -65,6 +77,9 @@ jobs: key: nominatim-data-${{ steps.get-date.outputs.date }} - uses: ./.github/actions/setup-postgresql + with: + postgresql-version: 13 + postgis-version: 3 - uses: ./.github/actions/build-nominatim - name: Create configuration diff --git a/sql/functions/address_lookup.sql b/sql/functions/address_lookup.sql index 34ef7ca7..4d7cc789 100644 --- a/sql/functions/address_lookup.sql +++ b/sql/functions/address_lookup.sql @@ -79,6 +79,18 @@ END; $$ LANGUAGE plpgsql STABLE; +DROP TYPE IF EXISTS addressdata_place; +CREATE TYPE addressdata_place AS ( + place_id BIGINT, + country_code VARCHAR(2), + housenumber TEXT, + postcode TEXT, + class TEXT, + type TEXT, + name HSTORE, + address HSTORE, + centroid GEOMETRY +); -- Compute the list of address parts for the given place. -- @@ -87,7 +99,7 @@ CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber IN RETURNS setof addressline AS $$ DECLARE - place RECORD; + place addressdata_place; location RECORD; current_rank_address INTEGER; location_isaddress BOOLEAN; @@ -98,9 +110,9 @@ BEGIN -- first query osmline (interpolation lines) IF in_housenumber >= 0 THEN SELECT parent_place_id as place_id, country_code, - in_housenumber::text as housenumber, postcode, + in_housenumber as housenumber, postcode, 'place' as class, 'house' as type, - null::hstore as name, null::hstore as address, + null as name, null as address, ST_Centroid(linegeo) as centroid INTO place FROM location_property_osmline @@ -111,10 +123,10 @@ BEGIN --then query tiger data -- %NOTIGERDATA% IF 0 THEN IF place IS NULL AND in_housenumber >= 0 THEN - SELECT parent_place_id as place_id, 'us'::varchar(2) as country_code, - in_housenumber::text as housenumber, postcode, + SELECT parent_place_id as place_id, 'us' as country_code, + in_housenumber as housenumber, postcode, 'place' as class, 'house' as type, - null::hstore as name, null::hstore as address, + null as name, null as address, ST_Centroid(linegeo) as centroid INTO place FROM location_property_tiger @@ -125,10 +137,10 @@ BEGIN -- %NOAUXDATA% IF 0 THEN IF place IS NULL THEN - SELECT parent_place_id as place_id, 'us'::varchar(2) as country_code, + SELECT parent_place_id as place_id, 'us' as country_code, housenumber, postcode, 'place' as class, 'house' as type, - null::hstore as name, null::hstore as address, + null as name, null as address, centroid INTO place FROM location_property_aux @@ -141,8 +153,8 @@ BEGIN SELECT parent_place_id as place_id, country_code, null::text as housenumber, postcode, 'place' as class, 'postcode' as type, - null::hstore as name, null::hstore as address, - null::geometry as centroid + null as name, null as address, + null as centroid INTO place FROM location_postcode WHERE place_id = in_place_id; @@ -167,8 +179,8 @@ BEGIN select coalesce(linked_place_id, place_id) as place_id, country_code, housenumber, postcode, class, type, - null::hstore as name, address, - null::geometry as centroid + null as name, address, + null as centroid INTO place FROM placex where place_id = in_place_id; END IF;