How to deal with commas in integers?

Question

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'

Accepted Answer

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

How to deal with commas in integers?

Follow this question

Related questions