NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

Attempting to follow steps 7 and 8 here, currently: https://github.com/mapserver/mapserver/wiki/RenderingOsmDataWindows

A recently-updated osm file I'm working with (british-isles-latest.osm.bz2, from http://download.geofabrik.de/europe/british-isles.html) seems to have a number of 'integer' values that contain commas - two, both population so far.

I've loaded the data to a postgres database using osm2pgsql, and am currently attempting to run osm2pgsql-to-imposm-schema.sql, but keep running into the error 'invalid input syntax for integer: "[some number with comma separators]"'. A little investigation shows that thus far these are population values, and whilst I can manually edit the commas out of the osm, postgres will only complain about one at a time and loading the data takes half a day or so, so this doesn't seem like the best solution.

I've loaded a slightly older (few weeks older?) version of this osm file to a local copy of postgres using the same steps previously, without issue.

Is this a recent change to how population values are stored in osms, or is this something that will be corrected in the data, and in either case how can I solve this without manually fixing all population values as postgres reports errors?

Pieren: osm2pgsql version is osm2pgsql SVN version 0.83.0 (64bit id space). According to default.style it is being treated as text: 'node,way population text linear'

asked 16 Oct '14, 09:39

RPaliwoda's gravatar image

RPaliwoda
16113
accept rate: 0%

edited 16 Oct '14, 13:11

2

I can't tell you how to fix this issue but there is one important fact: OSM data will always contain errors because the API does perform (almost) no validation. Population numbers might even contain letters. The only valid solution is that each parsing tool must be able to handle unexpected contents of tags and values.

(16 Oct '14, 09:49) scai ♦
1

This is actually the only sensible answer, tag values are strings, therefore you need a filter, validation & conversion routine.

(16 Oct '14, 10:11) SK53 ♦

Normally, "population" tag is treated as text datatyp. Please report your osm2pgsql version.

(16 Oct '14, 13:00) Pieren

Having a quick look into the sql script osm2pgsql-to-imposm-schema.sql, it seems that the script is transforming the original population column into an integer type:

-- cast population column as an integer
ALTER TABLE osm_new_places ADD COLUMN population2 integer;
UPDATE osm_new_places SET population2 = cast(population as integer) WHERE population IS NOT NULL;
ALTER TABLE osm_new_places DROP COLUMN population;
ALTER TABLE osm_new_places RENAME COLUMN population2 TO population;

Which means that if you want to reuse osm2pgsql, you should drop either everything and recreate all tables or just drop this column and recreate it with its original datatyp (text I guess). Something like :

ALTER TABLE osm_new_places DROP COLUMN population;
ALTER TABLE osm_new_places ADD COLUMN population text;

or try

ALTER TABLE osm_new_places ALTER COLUMN population type text using population::text;

if it works

permanent link

answered 16 Oct '14, 13:45

Pieren's gravatar image

Pieren
9.8k2083157
accept rate: 15%

edited 16 Oct '14, 15:52

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×263
×165
×14

question asked: 16 Oct '14, 09:39

question was seen: 5,249 times

last updated: 16 Oct '14, 15:52

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum