search

omniscale / imposm3

Imposm imports OpenStreetMap data into PostGIS
http://imposm.org/docs/imposm3/latest/
Apache License 2.0
730 stars 157 forks source link

Bug: PG 15 - pq: current transaction is aborted, commands ignored until end of transaction block #288

Open jefferypalmer opened 1 year ago

jefferypalmer commented 1 year ago

Quick note, we are glad to jump in and contribute patches/fixes, but need a little bit of direction for this to get a grasp on "where to start". Thanks!

Context

All SQL commands attempted by imposm fail (on postgres 15) with the following "SQL Error: pq: current transaction is aborted, commands ignored until end of transaction block in query"

Example of log below, and all following commands also show errors. For each update, the following error shows ~10,000+ times.

[2023-02-20T00:15:20Z] 9:55:51 [info] Importing #3616 including changes till 2023-02-19 21:21:39 +0000 UTC (2h53m41s behind)
[2023-02-20T00:15:20Z] 9:55:51 [step] Starting: Importing #3616
[2023-02-20T00:15:20Z] 9:55:51 [step] Starting: Processing /import/000/003/616.osc.gz
[2023-02-20T00:15:20Z] 9:55:51 [step] Starting: Parsing changes, updating cache and removing elements
[2023-02-20T00:15:22Z] 9:55:53 [step] Finished: Parsing changes, updating cache and removing elements in 2.469450234s
[2023-02-20T00:15:22Z] 9:55:53 [step] Starting: Importing added/modified elements
[2023-02-20T00:15:22Z] 9:55:53 [progress]     2s C:       0/s (28316) N:       0/s (944) W:       0/s (5409) R:      0/s (53)
[2023-02-20T00:15:23Z] 9:55:54 [warn]:  SQL Error: pq: current transaction is aborted, commands ignored until end of transaction block in query INSERT INTO "public"."osm_building_polygon" ("osm_id", "geometry", "area", "material", "colour", "building", "buildingpart", "buildingheight", "buildingmin_height", "buildinglevels", "buildingmin_level", "height", "min_height", "levels", "min_level") VALUES ($1, $2::Geometry, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15) ([-6474398 0103000020110F00000200000011000000811B79D4790661C127659870FABF464106245D5B780661C18DB16E0F09C0464149CE1B91780661C12BA95B6809C04641FC5D0BCC770661C1EAE2BD0811C04641A6B58591770661C1FA032EA910C04641B7130DF8760661C1B1FEDC9C16C04641925256E1710661C1ED85EF4F0EC04641C793997C720661C1E320AC4908C046413DAAD0A6710661C146289BEC06C046419B488821720661C177A72C2E02C04641C3C7FE10730661C10D61B6B503C04641DC6D9A78730661C1DEC2C4AFFFBF464164C217E4730661C101DD4A6000C046416469C6D2730661C1D3A4D50C01C046415D31515D740661C11637CBEF01C04641497EFDC8750661C1ADDC56D8F3BF4641811B79D4790661C127659870FABF464107000000776EF919780661C10CB32C7DFEBF46416442165D760661C1DB090282FBBF4641F1B08B11760661C133786949FEBF46414A056B92770661C126BC0DDD00C046416747C1B4770661C1EE413995FFBF4641E3FE5DF1770661C1687F13FEFFBF4641776EF919780661C10CB32C7DFEBF4641 2419.597   yes      16.5   ])

Possible Fix

A couple of theories are a default/settings change for handling of transactions in postgresql 15, or a specific start/end transaction command being triggered by imposm, or maybe a postgis version incompatibility.

Steps to Reproduce

  1. Run imposm on postgresql 15, eg: imposm run -connection postgis://user:pass@serverip:5432/osm -mapping /mapping/mapping.yaml -cachedir /cache -diffdir /import -expiretiles-dir /import -expiretiles-zoom 14 -config data/florida.repl.json

Context

Imposm updates are not working

Your Environment

Ubuntu 22 imposm version 0.11.1 Postgresql 15.2-1.pgdg22.04+1 Postgis 3.3.2

pnorman commented 1 year ago

What's the first error message in the PostgreSQL log?

jefferypalmer commented 1 year ago 
2023-02-22 00:01:41.491 UTC [165970] osm@osm ERROR:  duplicate key value violates unique constraint "osm_route_member_network_ref_idx"
2023-02-22 00:01:41.491 UTC [165970] osm@osm DETAIL:  Key (member, network, ref)=(545593089, US:I, 75) already exists.
2023-02-22 00:01:41.491 UTC [165970] osm@osm STATEMENT:  INSERT INTO "public"."osm_route_member" ("osm_id", "member", "role", "type", "ref", "network", "name", "osmc_symbol", "colour") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
2023-02-22 00:01:41.491 UTC [165970] osm@osm ERROR:  current transaction is aborted, commands ignored until end of transaction block
2023-02-22 00:01:41.491 UTC [165970] osm@osm STATEMENT:  INSERT INTO "public"."osm_route_member" ("osm_id", "member", "role", "type", "ref", "network", "name", "osmc_symbol", "colour") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
2023-02-22 00:01:41.491 UTC [165970] osm@osm ERROR:  current transaction is aborted, commands ignored until end of transaction block
2023-02-22 00:01:41.491 UTC [165970] osm@osm STATEMENT:  INSERT INTO "public"."osm_route_member" ("osm_id", "member", "role", "type", "ref", "network", "name", "osmc_symbol", "colour") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
2023-02-22 00:01:41.491 UTC [165970] osm@osm ERROR:  current transaction is aborted, commands ignored until end of transaction block
2023-02-22 00:01:41.491 UTC [165970] osm@osm STATEMENT:  INSERT INTO "public"."osm_route_member" ("osm_id", "member", "role", "type", "ref", "network", "name", "osmc_symbol", "colour") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
pnorman commented 1 year ago

Can you provide your mapping file? It looks like there's a primary key that makes no sense

jefferypalmer commented 1 year ago

Some more details here for context. We are using docker images from https://github.com/openmaptiles/openmaptiles-tools/tree/v7.0.0

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES a4b5a0309b79 openmaptiles/openmaptiles-tools:7.0 "import-update" 3 days ago Up 3 days openmaptiles_update-osm_1

This is using the following mapping file:

tags:
  include:
  - int_name
  - loc_name
  - name
  - name:am
  - name:ar
  - name:az
  - name:be
  - name:bg
  - name:bn
  - name:br
  - name:bs
  - name:ca
  - name:co
  - name:cs
  - name:cy
  - name:da
  - name:de
  - name:el
  - name:en
  - name:eo
  - name:es
  - name:et
  - name:eu
  - name:fa
  - name:fi
  - name:fr
  - name:fy
  - name:ga
  - name:gd
  - name:he
  - name:hi
  - name:hr
  - name:hu
  - name:hy
  - name:id
  - name:is
  - name:it
  - name:ja
  - name:ja-Hira
  - name:ja-Latn
  - name:ja_kana
  - name:ja_rm
  - name:ka
  - name:kk
  - name:kn
  - name:ko
  - name:ko-Latn
  - name:ku
  - name:la
  - name:lb
  - name:lt
  - name:lv
  - name:mk
  - name:ml
  - name:mt
  - name:nl
  - name:no
  - name:oc
  - name:pa
  - name:pl
  - name:pnb
  - name:pt
  - name:rm
  - name:ro
  - name:ru
  - name:sk
  - name:sl
  - name:sq
  - name:sr
  - name:sr-Latn
  - name:sv
  - name:ta
  - name:te
  - name:th
  - name:tr
  - name:uk
  - name:ur
  - name:vi
  - name:zh
  - name:zh-Hans
  - name:zh-Hant
  - wikidata
  - wikipedia
generalized_tables:
  water_polygon_gen_z6:
    source: water_polygon_gen_z7
    sql_filter: area>power(4891.969810251282,2)
    tolerance: 1222.9924525628205
  water_polygon_gen_z7:
    source: water_polygon_gen_z8
    sql_filter: area>power(2445.984905125641,2)
    tolerance: 611.4962262814103
  water_polygon_gen_z8:
    source: water_polygon_gen_z9
    sql_filter: area>power(1222.9924525628205,2)
    tolerance: 305.74811314070513
  water_polygon_gen_z9:
    source: water_polygon_gen_z10
    sql_filter: area>power(611.4962262814103,2)
    tolerance: 152.87405657035256
  water_polygon_gen_z10:
    source: water_polygon_gen_z11
    sql_filter: area>power(305.74811314070513,2)
    tolerance: 76.43702828517628
  water_polygon_gen_z11:
    source: water_polygon
    sql_filter: area>power(152.87405657035256,2)
    tolerance: 38.21851414258814
  landuse_polygon_gen_z6:
    source: landuse_polygon_gen_z7
    tolerance: 2445.984905125641
    sql_filter: area>power(2445.984905125641,2)
  landuse_polygon_gen_z7:
    source: landuse_polygon_gen_z8
    tolerance: 1222.9924525628205
    sql_filter: area>power(2445.984905125641,2)
  landuse_polygon_gen_z8:
    source: landuse_polygon_gen_z9
    sql_filter: area>power(2445.984905125641,2) AND (landuse='residential' OR place='suburb'
      OR place='quarter' OR place='neighbourhood')
    tolerance: 611.4962262814103
  landuse_polygon_gen_z9:
    source: landuse_polygon_gen_z10
    sql_filter: area>power(1222.9924525628205,2)
    tolerance: 305.74811314070513
  landuse_polygon_gen_z10:
    source: landuse_polygon_gen_z11
    sql_filter: area>power(611.4962262814103,2)
    tolerance: 152.87405657035256
  landuse_polygon_gen_z11:
    source: landuse_polygon_gen_z12
    sql_filter: area>power(305.74811314070513,2)
    tolerance: 76.43702828517628
  landuse_polygon_gen_z12:
    source: landuse_polygon_gen_z13
    sql_filter: area>power(152.87405657035256,2)
    tolerance: 38.21851414258814
  landuse_polygon_gen_z13:
    source: landuse_polygon
    sql_filter: area>power(76.43702828517628,2) AND ST_IsValid(geometry)
    tolerance: 19.10925707129407
  park_polygon_gen_z4:
    source: park_polygon_gen_z5
    sql_filter: area>power(19567.87924100513,2)
    tolerance: 9783.939620502564
  park_polygon_gen_z5:
    source: park_polygon_gen_z6
    sql_filter: area>power(9783.939620502564,2)
    tolerance: 4891.969810251282
  park_polygon_gen_z6:
    source: park_polygon_gen_z7
    sql_filter: area>power(4891.969810251282,2)
    tolerance: 2445.984905125641
  park_polygon_gen_z7:
    source: park_polygon_gen_z8
    sql_filter: area>power(2445.984905125641,2)
    tolerance: 1222.9924525628205
  park_polygon_gen_z8:
    source: park_polygon_gen_z9
    sql_filter: area>power(1222.9924525628205,2)
    tolerance: 611.4962262814103
  park_polygon_gen_z9:
    source: park_polygon_gen_z10
    sql_filter: area>power(611.4962262814103,2)
    tolerance: 305.74811314070513
  park_polygon_gen_z10:
    source: park_polygon_gen_z11
    sql_filter: area>power(305.74811314070513,2)
    tolerance: 152.87405657035256
  park_polygon_gen_z11:
    source: park_polygon_gen_z12
    sql_filter: area>power(152.87405657035256,2)
    tolerance: 76.43702828517628
  park_polygon_gen_z12:
    source: park_polygon_gen_z13
    sql_filter: area>power(76.43702828517628,2)
    tolerance: 38.21851414258814
  park_polygon_gen_z13:
    source: park_polygon
    sql_filter: area>power(38.21851414258814,2) AND ST_IsValid(geometry)
    tolerance: 19.10925707129407
  border_disp_linestring:
    source: border_linestring
    sql_filter: ST_GeometryType(geometry) = 'ST_LineString' AND (disputed OR dispute
      OR border_status = 'disputed' OR disputed_by <> '') AND admin_level = 2
  aeroway_linestring_gen_z10:
    source: aeroway_linestring_gen_z11
    tolerance: 76.43702828517628
  aeroway_linestring_gen_z11:
    source: aeroway_linestring_gen_z12
    tolerance: 38.21851414258814
  aeroway_linestring_gen_z12:
    source: aeroway_linestring
    sql_filter: ST_IsValid(geometry)
    tolerance: 19.10925707129407
  aeroway_polygon_gen_z10:
    source: aeroway_polygon_gen_z11
    sql_filter: area>power(305.74811314070513,2)
    tolerance: 152.87405657035256
  aeroway_polygon_gen_z11:
    source: aeroway_polygon_gen_z12
    sql_filter: area>power(152.87405657035256,2)
    tolerance: 76.43702828517628
  aeroway_polygon_gen_z12:
    source: aeroway_polygon_gen_z13
    sql_filter: area>power(76.43702828517628,2)
    tolerance: 38.21851414258814
  aeroway_polygon_gen_z13:
    source: aeroway_polygon
    sql_filter: area>power(38.21851414258814,2) AND ST_IsValid(geometry)
    tolerance: 19.10925707129407
  railway_linestring_gen_z8:
    source: railway_linestring_gen_z9
    tolerance: 305.74811314070513
  railway_linestring_gen_z9:
    source: railway_linestring_gen_z10
    tolerance: 152.87405657035256
  railway_linestring_gen_z10:
    source: railway_linestring_gen_z11
    tolerance: 76.43702828517628
  railway_linestring_gen_z11:
    source: railway_linestring_gen_z12
    tolerance: 38.21851414258814
  railway_linestring_gen_z12:
    source: railway_linestring
    sql_filter: railway IN ('rail', 'narrow_gauge', 'light_rail') AND service='' AND
      ST_IsValid(geometry)
    tolerance: 19.10925707129407
  aerialway_linestring_gen_z12:
    source: aerialway_linestring
    sql_filter: ST_IsValid(geometry)
    tolerance: 19.10925707129407
  shipway_linestring_gen_z11:
    source: shipway_linestring_gen_z12
    tolerance: 38.21851414258814
  shipway_linestring_gen_z12:
    source: shipway_linestring
    sql_filter: ST_IsValid(geometry)
    tolerance: 19.10925707129407
  highway_linestring_gen_z11:
    source: highway_linestring
    sql_filter: (highway IN ('motorway', 'trunk', 'primary', 'secondary', 'tertiary',
      'motorway_link', 'trunk_link', 'primary_link', 'secondary_link', 'tertiary_link',
      'busway', 'bus_guideway') OR highway = 'construction' AND construction IN ('motorway',
      'trunk', 'primary', 'secondary', 'tertiary', 'motorway_link', 'trunk_link',
      'primary_link', 'secondary_link', 'tertiary_link', 'busway', 'bus_guideway'))
      AND NOT is_area AND ST_IsValid(geometry)
    tolerance: 38.21851414258814
tables:
  water_polygon:
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: validated_geometry
    - name: area
      type: area
    - key: name
      name: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - name: place
      key: place
      type: string
    - name: natural
      key: natural
      type: string
    - name: landuse
      key: landuse
      type: string
    - name: waterway
      key: waterway
      type: string
    - name: leisure
      key: leisure
      type: string
    - name: water
      key: water
      type: string
    - name: is_intermittent
      key: intermittent
      type: bool
    - key: tunnel
      name: is_tunnel
      type: bool
    - key: bridge
      name: is_bridge
      type: bool
    filters:
      reject:
        covered:
        - 'yes'
    mapping:
      landuse:
      - reservoir
      - basin
      - salt_pond
      leisure:
      - swimming_pool
      natural:
      - water
      - bay
      - spring
      waterway:
      - dock
      water:
      - river
      - stream
      - canal
      - ditch
      - drain
      - pond
      - basin
      - wastewater
    type: polygon
  waterway_linestring:
    type: linestring
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: waterway
      key: waterway
      type: string
    - key: name
      name: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - key: tunnel
      name: is_tunnel
      type: bool
    - key: bridge
      name: is_bridge
      type: bool
    - name: is_intermittent
      key: intermittent
      type: bool
    mapping:
      waterway:
      - stream
      - river
      - canal
      - drain
      - ditch
  waterway_relation:
    type: relation_member
    columns:
    - name: osm_id
      type: id
    - name: member
      type: member_id
    - name: role
      type: member_role
    - name: geometry
      type: geometry
    - key: waterway
      name: waterway
      type: string
    - key: name
      name: name
      type: string
    - name: tags
      type: hstore_tags
    mapping:
      waterway:
      - river
  landcover_polygon:
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: validated_geometry
    - name: area
      type: area
    - name: subclass
      type: mapping_value
    - name: mapping_key
      type: mapping_key
    mapping:
      landuse:
      - allotments
      - farm
      - farmland
      - orchard
      - plant_nursery
      - vineyard
      - grass
      - grassland
      - meadow
      - forest
      - village_green
      - recreation_ground
      natural:
      - wood
      - wetland
      - fell
      - grassland
      - heath
      - scrub
      - shrubbery
      - tundra
      - glacier
      - bare_rock
      - scree
      - beach
      - sand
      - dune
      leisure:
      - park
      - garden
      - golf_course
      wetland:
      - bog
      - swamp
      - wet_meadow
      - marsh
      - reedbed
      - saltern
      - tidalflat
      - saltmarsh
      - mangrove
    type: polygon
  landuse_polygon:
    type: polygon
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: validated_geometry
    - name: landuse
      key: landuse
      type: string
    - name: amenity
      key: amenity
      type: string
    - name: leisure
      key: leisure
      type: string
    - name: tourism
      key: tourism
      type: string
    - name: place
      key: place
      type: string
    - name: waterway
      key: waterway
      type: string
    - name: area
      type: area
    mapping:
      landuse:
      - railway
      - cemetery
      - military
      - quarry
      - residential
      - commercial
      - industrial
      - garages
      - retail
      amenity:
      - bus_station
      - school
      - university
      - kindergarten
      - college
      - library
      - hospital
      - grave_yard
      leisure:
      - stadium
      - pitch
      - playground
      - track
      tourism:
      - theme_park
      - zoo
      place:
      - suburb
      - quarter
      - neighbourhood
      waterway:
      - dam
  peak_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: name
      key: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - name: ele
      key: ele
      type: string
    - name: wikipedia
      key: wikipedia
      type: string
    mapping:
      natural:
      - peak
      - volcano
      - saddle
  mountain_linestring:
    type: linestring
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: name
      key: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - name: wikipedia
      key: wikipedia
      type: string
    mapping:
      natural:
      - ridge
      - cliff
      - arete
  park_polygon:
    type: polygon
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: validated_geometry
    - name: name
      key: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - name: landuse
      key: landuse
      type: string
    - name: leisure
      key: leisure
      type: string
    - name: boundary
      key: boundary
      type: string
    - name: protection_title
      key: protection_title
      type: string
    - name: area
      type: area
    mapping:
      leisure:
      - nature_reserve
      boundary:
      - national_park
      - protected_area
  border_linestring:
    type: relation_member
    filters:
      require:
        admin_level:
        - __any__
        boundary:
        - administrative
    columns:
    - name: relation_id
      type: id
    - name: osm_id
      type: id
      from_member: true
    - name: member
      type: member_id
    - name: type
      type: member_type
    - name: geometry
      type: geometry
    - key: name
      name: name
      type: string
      from_member: true
    - key: admin_level
      name: admin_level
      type: integer
    - key: claimed_by
      name: claimed_by
      type: string
    - key: disputed_by
      name: disputed_by
      type: string
      from_member: true
    - key: dispute
      name: dispute
      type: bool
      from_member: true
    - key: disputed
      name: disputed
      type: bool
      from_member: true
    - key: border_status
      name: border_status
      type: string
      from_member: true
    - key: maritime
      name: maritime
      type: bool
      from_member: true
    - key: boundary_type
      name: boundary_type
      type: string
      from_member: true
    - key: natural
      name: natural
      type: string
      from_member: true
    relation_types:
    - boundary
    mapping:
      boundary:
      - administrative
      border_status:
      - dispute
      boundary_type:
      - maritime
  aeroway_polygon:
    type: polygon
    columns:
    - &id002
      key: ref
      name: ref
      type: string
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: aeroway
      type: mapping_value
    - name: area
      type: area
    mapping:
      aeroway: &id001
      - aerodrome
      - heliport
      - runway
      - helipad
      - taxiway
      - apron
      area:aeroway: *id001
  aeroway_linestring:
    type: linestring
    columns:
    - *id002
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: aeroway
      key: aeroway
      type: string
    mapping:
      aeroway:
      - runway
      - taxiway
  aeroway_point:
    type: point
    columns:
    - *id002
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: aeroway
      key: aeroway
      type: string
    mapping:
      aeroway:
      - gate
  highway_linestring:
    type: linestring
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: highway
      key: highway
      type: string
    - name: construction
      key: construction
      type: string
    - name: tracktype
      key: tracktype
      type: string
    - &id003
      key: ref
      name: ref
      type: string
    - &id004
      key: network
      name: network
      type: string
    - &id005
      name: z_order
      type: wayzorder
    - &id006
      key: layer
      name: layer
      type: integer
    - &id007
      key: level
      name: level
      type: integer
    - &id008
      key: indoor
      name: indoor
      type: bool
    - &id009
      name: name
      key: name
      type: string
    - &id010
      name: name_en
      key: name:en
      type: string
    - &id011
      name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - &id012
      key: short_name
      name: short_name
      type: string
    - &id013
      key: tunnel
      name: is_tunnel
      type: bool
    - &id014
      key: bridge
      name: is_bridge
      type: bool
    - &id015
      key: ramp
      name: is_ramp
      type: bool
    - &id016
      key: ford
      name: is_ford
      type: bool
    - &id017
      key: oneway
      name: is_oneway
      type: direction
    - &id018
      name: is_area
      key: area
      type: bool
    - &id019
      key: service
      name: service
      type: string
    - key: access
      name: access
      type: string
    - key: toll
      name: toll
      type: bool
    - &id020
      key: usage
      name: usage
      type: string
    - &id021
      key: public_transport
      name: public_transport
      type: string
    - &id022
      key: man_made
      name: man_made
      type: string
    - key: bicycle
      name: bicycle
      type: string
    - key: foot
      name: foot
      type: string
    - key: horse
      name: horse
      type: string
    - key: mtb:scale
      name: mtb_scale
      type: string
    - key: sac_scale
      name: sac_scale
      type: string
    - key: surface
      name: surface
      type: string
    - key: expressway
      name: expressway
      type: bool
    mapping:
      highway:
      - motorway
      - motorway_link
      - trunk
      - trunk_link
      - primary
      - primary_link
      - secondary
      - secondary_link
      - tertiary
      - tertiary_link
      - unclassified
      - residential
      - living_street
      - road
      - pedestrian
      - path
      - footway
      - cycleway
      - steps
      - bridleway
      - corridor
      - service
      - track
      - raceway
      - busway
      - bus_guideway
      - construction
      public_transport:
      - platform
      man_made:
      - pier
      service:
      - driveway
      - parking_aisle
  railway_linestring:
    type: linestring
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - key: railway
      name: railway
      type: string
    - *id003
    - *id004
    - *id005
    - *id006
    - *id007
    - *id008
    - *id009
    - *id010
    - *id011
    - name: tags
      type: hstore_tags
    - *id012
    - *id013
    - *id014
    - *id015
    - *id016
    - *id017
    - *id018
    - *id019
    - *id020
    mapping:
      railway:
      - rail
      - narrow_gauge
      - preserved
      - funicular
      - subway
      - light_rail
      - monorail
      - tram
  aerialway_linestring:
    type: linestring
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - key: aerialway
      name: aerialway
      type: string
    - *id005
    - *id006
    - *id009
    - *id010
    - *id011
    - name: tags
      type: hstore_tags
    - *id012
    - *id013
    - *id014
    - *id015
    - *id016
    - *id017
    - *id018
    - *id019
    - *id020
    mapping:
      aerialway:
      - chair_lift
      - drag_lift
      - platter
      - t-bar
      - gondola
      - cable_car
      - j-bar
      - mixed_lift
  shipway_linestring:
    type: linestring
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - key: route
      name: shipway
      type: string
    - *id005
    - *id006
    - *id009
    - *id010
    - *id011
    - name: tags
      type: hstore_tags
    - *id012
    - *id013
    - *id014
    - *id015
    - *id016
    - *id017
    - *id018
    - *id019
    - *id020
    mapping:
      route:
      - ferry
  highway_polygon:
    type: polygon
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: highway
      key: highway
      type: string
    - name: z_order
      type: wayzorder
    - *id006
    - *id007
    - *id008
    - name: is_area
      key: area
      type: bool
    - *id021
    - *id022
    - *id019
    mapping:
      highway:
      - path
      - cycleway
      - bridleway
      - footway
      - corridor
      - pedestrian
      - steps
      public_transport:
      - platform
      man_made:
      - bridge
      - pier
  highway_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: highway
      key: highway
      type: string
    - name: z_order
      type: wayzorder
    - *id006
    - *id007
    - *id009
    - *id010
    - *id011
    - name: tags
      type: hstore_tags
    - name: ref
      key: ref
      type: string
    mapping:
      highway:
      - motorway_junction
  route_member:
    type: relation_member
    columns:
    - name: osm_id
      type: id
    - name: member
      type: member_id
    - name: role
      type: member_role
    - name: type
      type: member_type
    - *id003
    - *id004
    - *id009
    - name: osmc_symbol
      key: osmc:symbol
      type: string
    - name: colour
      key: colour
      type: string
    mapping:
      route:
      - road
      - hiking
  building_polygon:
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: validated_geometry
    - name: area
      type: area
    - name: material
      key: building:material
      type: string
    - name: colour
      key: building:colour
      type: string
    - name: building
      key: building
      type: string
    - name: buildingpart
      key: building:part
      type: string
    - name: buildingheight
      key: building:height
      type: string
    - name: buildingmin_height
      key: building:min_height
      type: string
    - name: buildinglevels
      key: building:levels
      type: string
    - name: buildingmin_level
      key: building:min_level
      type: string
    - name: height
      key: height
      type: string
    - name: min_height
      key: min_height
      type: string
    - name: levels
      key: levels
      type: string
    - name: min_level
      key: min_level
      type: string
    mapping:
      building:part:
      - __any__
      building:
      - __any__
      aeroway:
      - terminal
      - hangar
      location:
      - underground
    filters:
      reject:
        building:
        - 'no'
        - none
        - 'No'
        building:part:
        - 'no'
        - none
        - 'No'
        man_made:
        - bridge
        location:
        - underground
    type: polygon
  building_relation:
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: validated_geometry
    - name: area
      type: area
    - name: building
      key: building
      type: string
      from_member: true
    - name: material
      key: building:material
      type: string
    - name: colour
      key: building:colour
      type: string
    - name: buildingpart
      key: building:part
      type: string
      from_member: true
    - name: buildingheight
      key: building:height
      type: string
      from_member: true
    - name: height
      key: height
      type: string
      from_member: true
    - name: buildingmin_height
      key: building:min_height
      type: string
      from_member: true
    - name: min_height
      key: min_height
      type: string
      from_member: true
    - name: buildinglevels
      key: building:levels
      type: string
      from_member: true
    - name: levels
      key: levels
      type: string
      from_member: true
    - name: buildingmin_level
      key: building:min_level
      type: string
      from_member: true
    - name: min_level
      key: min_level
      type: string
      from_member: true
    - name: relbuildingheight
      key: building:height
      type: string
    - name: relheight
      key: height
      type: string
    - name: relbuildingmin_height
      key: building:min_height
      type: string
    - name: relmin_height
      key: min_height
      type: string
    - name: relbuildinglevels
      key: building:levels
      type: string
    - name: rellevels
      key: levels
      type: string
    - name: relbuildingmin_level
      key: building:min_level
      type: string
    - name: relmin_level
      key: min_level
      type: string
    - name: member
      type: member_id
    - name: index
      type: member_index
    - name: role
      type: member_role
      from_member: true
    - name: type
      type: member_type
    mapping:
      type:
      - building
    type: relation_member
  marine_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: name
      key: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - name: place
      key: place
      type: string
    - name: natural
      key: natural
      type: string
    - name: rank
      key: rank
      type: integer
    - name: is_intermittent
      key: intermittent
      type: bool
    filters:
      require:
        name:
        - __any__
    mapping:
      place:
      - ocean
      - sea
      natural:
      - bay
      - strait
  continent_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - &id023
      name: name
      key: name
      type: string
    - &id024
      name: name_en
      key: name:en
      type: string
    - &id025
      name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    filters:
      require:
        name:
        - __any__
    mapping:
      place:
      - continent
  country_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - *id023
    - *id024
    - *id025
    - &id026
      name: rank
      key: rank
      type: integer
    - name: country_code_iso3166_1_alpha_2
      key: country_code_iso3166_1_alpha_2
      type: string
    - name: iso3166_1_alpha_2
      key: ISO3166-1:alpha2
      type: string
    - name: iso3166_1
      key: ISO3166-1
      type: string
    - name: tags
      type: hstore_tags
    filters:
      require:
        name:
        - __any__
    mapping:
      place:
      - country
  island_polygon:
    type: polygon
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: area
      type: area
    - *id023
    - *id024
    - *id025
    - name: tags
      type: hstore_tags
    - *id026
    filters:
      require:
        name:
        - __any__
    mapping:
      place:
      - island
  island_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - *id023
    - *id024
    - *id025
    - name: tags
      type: hstore_tags
    - *id026
    filters:
      require:
        name:
        - __any__
    mapping:
      place:
      - island
  state_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - *id023
    - *id024
    - *id025
    - name: tags
      type: hstore_tags
    - name: place
      key: place
      type: string
    - name: is_in_country
      key: is_in:country
      type: string
    - name: is_in_country_code
      key: is_in:country_code
      type: string
    - name: ref
      key: ref
      type: string
    - *id026
    filters:
      require:
        name:
        - __any__
    mapping:
      place:
      - state
      - province
  city_point:
    type: point
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - *id023
    - *id024
    - *id025
    - name: tags
      type: hstore_tags
    - name: place
      key: place
      type: string
    - key: population
      name: population
      type: integer
    - key: capital
      name: capital
      type: string
    - *id026
    filters:
      require:
        name:
        - __any__
    mapping:
      place:
      - city
      - town
      - village
      - hamlet
      - borough
      - suburb
      - quarter
      - neighbourhood
      - isolated_dwelling
  housenumber_point:
    type: geometry
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: housenumber
      key: addr:housenumber
      type: string
    - name: street
      key: addr:street
      type: string
    - name: block_number
      key: addr:block_number
      type: string
    - name: has_name
      key: name
      type: string
    type_mappings:
      points:
        addr:housenumber:
        - __any__
      polygons:
        addr:housenumber:
        - __any__
  poi_point:
    type: point
    columns: &id027
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: area
      type: area
    - name: name
      key: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - name: subclass
      type: mapping_value
    - name: mapping_key
      type: mapping_key
    - name: station
      key: station
      type: string
    - name: funicular
      key: funicular
      type: string
    - name: information
      key: information
      type: string
    - name: uic_ref
      key: uic_ref
      type: string
    - name: ref
      key: ref
      type: string
    - name: religion
      key: religion
      type: string
    - name: level
      key: level
      type: integer
    - name: indoor
      key: indoor
      type: bool
    - name: layer
      key: layer
      type: integer
    - name: sport
      key: sport
      type: string
    - name: operator
      key: operator
      type: string
    - name: network
      key: network
      type: string
    - name: brand
      key: brand
      type: string
    mapping: &id028
      aerialway:
      - station
      amenity:
      - arts_centre
      - atm
      - bank
      - bar
      - bbq
      - bicycle_parking
      - bicycle_rental
      - biergarten
      - bus_station
      - cafe
      - cinema
      - clinic
      - college
      - community_centre
      - courthouse
      - dentist
      - doctors
      - drinking_water
      - fast_food
      - ferry_terminal
      - fire_station
      - food_court
      - fuel
      - grave_yard
      - hospital
      - ice_cream
      - kindergarten
      - library
      - marketplace
      - motorcycle_parking
      - nightclub
      - nursing_home
      - parking
      - pharmacy
      - place_of_worship
      - police
      - parcel_locker
      - post_box
      - post_office
      - prison
      - pub
      - public_building
      - recycling
      - restaurant
      - school
      - shelter
      - swimming_pool
      - taxi
      - telephone
      - theatre
      - toilets
      - townhall
      - university
      - veterinary
      - waste_basket
      barrier:
      - bollard
      - border_control
      - cycle_barrier
      - gate
      - lift_gate
      - sally_port
      - stile
      - toll_booth
      building:
      - dormitory
      highway:
      - bus_stop
      historic:
      - monument
      - castle
      - ruins
      landuse:
      - basin
      - brownfield
      - cemetery
      - reservoir
      - winter_sports
      leisure:
      - dog_park
      - escape_game
      - garden
      - golf_course
      - ice_rink
      - hackerspace
      - marina
      - miniature_golf
      - park
      - pitch
      - playground
      - sports_centre
      - stadium
      - swimming_area
      - swimming_pool
      - water_park
      office:
      - diplomatic
      railway:
      - halt
      - station
      - subway_entrance
      - train_station_entrance
      - tram_stop
      shop:
      - accessories
      - alcohol
      - antiques
      - art
      - bag
      - bakery
      - beauty
      - bed
      - beverages
      - bicycle
      - books
      - boutique
      - butcher
      - camera
      - car
      - car_repair
      - car_parts
      - carpet
      - charity
      - chemist
      - chocolate
      - clothes
      - coffee
      - computer
      - confectionery
      - convenience
      - copyshop
      - cosmetics
      - deli
      - delicatessen
      - department_store
      - doityourself
      - dry_cleaning
      - electronics
      - erotic
      - fabric
      - florist
      - frozen_food
      - furniture
      - garden_centre
      - general
      - gift
      - greengrocer
      - hairdresser
      - hardware
      - hearing_aids
      - hifi
      - ice_cream
      - interior_decoration
      - jewelry
      - kiosk
      - lamps
      - laundry
      - locksmith
      - mall
      - massage
      - mobile_phone
      - motorcycle
      - music
      - musical_instrument
      - newsagent
      - optician
      - outdoor
      - perfume
      - perfumery
      - pet
      - photo
      - second_hand
      - shoes
      - sports
      - stationery
      - supermarket
      - tailor
      - tattoo
      - ticket
      - tobacco
      - toys
      - travel_agency
      - video
      - video_games
      - watches
      - weapons
      - wholesale
      - wine
      sport:
      - american_football
      - archery
      - athletics
      - australian_football
      - badminton
      - baseball
      - basketball
      - beachvolleyball
      - billiards
      - bmx
      - boules
      - bowls
      - boxing
      - canadian_football
      - canoe
      - chess
      - climbing
      - climbing_adventure
      - cricket
      - cricket_nets
      - croquet
      - curling
      - cycling
      - disc_golf
      - diving
      - dog_racing
      - equestrian
      - fatsal
      - field_hockey
      - free_flying
      - gaelic_games
      - golf
      - gymnastics
      - handball
      - hockey
      - horse_racing
      - horseshoes
      - ice_hockey
      - ice_stock
      - judo
      - karting
      - korfball
      - long_jump
      - model_aerodrome
      - motocross
      - motor
      - multi
      - netball
      - orienteering
      - paddle_tennis
      - paintball
      - paragliding
      - pelota
      - racquet
      - rc_car
      - rowing
      - rugby
      - rugby_league
      - rugby_union
      - running
      - sailing
      - scuba_diving
      - shooting
      - shooting_range
      - skateboard
      - skating
      - skiing
      - soccer
      - surfing
      - swimming
      - table_soccer
      - table_tennis
      - team_handball
      - tennis
      - toboggan
      - volleyball
      - water_ski
      - yoga
      tourism:
      - alpine_hut
      - aquarium
      - artwork
      - attraction
      - bed_and_breakfast
      - camp_site
      - caravan_site
      - chalet
      - gallery
      - guest_house
      - hostel
      - hotel
      - information
      - motel
      - museum
      - picnic_site
      - theme_park
      - viewpoint
      - zoo
      waterway:
      - dock
  poi_polygon:
    type: polygon
    columns: *id027
    mapping: *id028
  aerodrome_label_point:
    type: geometry
    columns:
    - name: osm_id
      type: id
    - name: geometry
      type: geometry
    - name: name
      key: name
      type: string
    - name: name_en
      key: name:en
      type: string
    - name: name_de
      key: name:de
      type: string
    - name: tags
      type: hstore_tags
    - name: aerodrome_type
      key: aerodrome:type
      type: string
    - name: aerodrome
      key: aerodrome
      type: string
    - name: military
      key: military
      type: string
    - name: iata
      key: iata
      type: string
    - name: icao
      key: icao
      type: string
    - name: ele
      key: ele
      type: string
    type_mappings:
      points:
        aeroway:
        - aerodrome
      polygons:
        aeroway:
        - aerodrome
pnorman commented 1 year ago

I don't see anything that would cause the osm_route_member_network_ref_idx index to be created. And this happens with a brand new database, not running any commands other than imposm run -connection postgis://user:pass@serverip:5432/osm -mapping /mapping/mapping.yaml -cachedir /cache -diffdir /import -expiretiles-dir /import -expiretiles-zoom 14 -config data/florida.repl.json?

jefferypalmer commented 1 year ago

Oh that's a good lead. Found the source of that index here: https://github.com/openmaptiles/openmaptiles/commit/bc9bbd2e67bb90163b9e85789c89fd4d87561b8d

I will give it a quick test by specifically pulling openmaptiles tag v3.14 which doesn't have this index.

Question to resolve this, should the imposm config definition be updated to include this index? Or is the index incorrect and I should open an issue on the openmaptiles repo to remove/resolve?

We are generating/populating the database with osm data by roughly performing the following:

IMPORT_AREA=florida
git clone https://github.com/openmaptiles/openmaptiles
cd openmaptiles/
make
make import-data
make download area=${IMPORT_AREA}
make import-osm area=${IMPORT_AREA}
make import-wikidata area=${IMPORT_AREA}
make import-sql area=${IMPORT_AREA}
pnorman commented 1 year ago

Looks like you did a different set of commands then you documented in the first post and all the issues are outside imposm, so this can be closed.

jefferypalmer commented 1 year ago

Question to resolve this, should the imposm config definition be updated to be aware of / include this index? Or is the index incorrect and I should open an issue on the openmaptiles repo to remove/resolve? These are additional indexes that openmaptile creates to index searches, but unsure if that is supposed to be replicated into the imposm config, or if this is an issue with the openmaptile index?

pnorman commented 1 year ago

Question to resolve this, should the imposm config definition be updated to be aware of / include this index? Or is the index incorrect and I should open an issue on the openmaptiles repo to remove/resolve? These are additional indexes that openmaptile creates to index searches, but unsure if that is supposed to be replicated into the imposm config, or if this is an issue with the openmaptile index?

I suspect a usage error, but if there is an error, it's in the openmaptiles code. Regardless, it doesn't appear to be an imposm error.

ZeLonewolf commented 1 year ago

It looks like there's a primary key that makes no sense

@pnorman can you elaborate on this? I added the offending index so I'd like to understand what I need to fix.

pnorman commented 1 year ago

It looks like there's a primary key that makes no sense

@pnorman can you elaborate on this? I added the offending index so I'd like to understand what I need to fix.

(member, network, ref) is not guaranteed to be unique. In the context of an imposm3 import - the originally reported problem, this key will result in failures for some imports. OpenMapTiles may do some processing to allow it to make this index, but you never want to have imposm touch the table after that processing has happened.

TomPohys commented 1 year ago

This issue should be fixed by https://github.com/openmaptiles/openmaptiles/pull/1501.