1

The Problem

Comparing population with CartoCSS in my TileMill project doesn't work unless I specifically cast population to a numeric type in my Postgres query. The problem with that, is that it is not stable. I can do this without problems for The Netherlands, but there apparently is a population tag as 27 813 in Germany. This leads to Postgis Plugin: ERROR: invalid input syntax for type double precision: "27 813".

This lead me to believe that population is a string/text type in the Postgres database. Looking at the default osm2pgsql style file that I used during import confirms my suspicion that it is returned as a string.

The Postgresql Query

( SELECT way, place AS type, name, z_order, population
  FROM planet_osm_point
  WHERE place in ('country', 'state', 'city', 'town', 'village', 'hamlet', 'suburb', 'neighbourhood', 'locality', 'isolated_dwelling','city_block','borough','islet','island')
  ORDER BY population DESC NULLS LAST
) AS data

A snippet from the CartoCSS

CartoCSS snippet

Question

Is there a right way to compare the population, without casting it or getting casting problems? Is casting it the right way to do it?

Thermometer
  • 2,567
  • 3
  • 20
  • 41
  • 1
    Raw data can contain all kinds of errors. This means the value of the population tag might even contain letters. Either be prepared for errors in your database or fix them / throw them away during import. Look at the [population tag at taginfo](http://taginfo.openstreetmap.org/keys/population#values), type any letter you like into the search field, hit enter and take a look at all the erroneous results. Make sure you can handle such errors in your data. – scai Nov 26 '14 at 16:55
  • 1
    I agree with @scai. Fix it on the way in by enforcing datatypes in the db. This might be a pain initially, but you will end up with reliable data, and won't have to resort to ugly hacks like casting population to a numeric type. – John Powell Nov 26 '14 at 19:25
  • Thanks, that does indeed sound like a good idea. I find it weird that MapBox mentions [comparing population here](https://www.mapbox.com/tilemill/docs/guides/selectors/#numeric-value-comparison-filters), like it should work 'out of the box'. I am using osm2pgsql to import the OSM data. Would you recommend somehow filtering the population values there? (Which means modifying osm2pgsql) or building a query to remove them after import? – Thermometer Nov 27 '14 at 10:33

0 Answers0