[OSM-dev] Using NULL values in mapnik / postgres DB

[Jon Burgess]

See below...

On Tue, 2007-02-20 at 22:12 +0000, Artem Pavlenko wrote:
> Jon,
> 
> Sounds good to me! Also, it might be a good idea to split osm data into 
> multiple tables in postgresql and only keep fields that are required :
> 
> 1. roads (LINESTRING) :  osm_id,highway,level,name ...
> 2. all points (POINT) : osm_id, amenity,...
> 3. all polygons (POLYGONS): osm_id, natural, leisure etc.
> 
> Thoughts?
> 
> Also, I'm not entirely convinced that using TEXT is more efficient than 
> VARCHAR(<length>) ???  Was it mentioned somewhere in docs? 
> 
> And last one:) . How about correcting geometries at the translation stage? I 
> think we can use GEOS (c++ lib) to 'node' and 'linemerge' _bad_ ways into 
> clean ones. 
> 
> Artem
> 
> On Tuesday 20 February 2007 21:40, Jon Burgess wrote:
> > I received an email from Martin Spott which suggested that we change the
> > Postgres database fields used by osm2pgsql + mapnik to utilise NULL in
> > place of empty text strings.
> >
> > Martin believes this will make the database lookups more efficient and
> > i'm inclined to believe him.
> >
> > To make this change we need to change both osm2pgsql.c and the osm.xml
> > mapnik rules to generate and parse the NULL SQL values.
> >
> > I've done a quick trial on my local machine and it is fairly trivial to
> > implement this. For reference the osm.xml changes look like:
> >
> >   char_length(foo)=0   changes to   foo IS NULL
> >   char_length(foo)>0   changes to   foo IS NOT NULL
> >
> > Any thoughts about whether I should go ahead and make this change?
> >
> > Jon

I've just commited the relevant changes to use NULLs in place of empty
strings into both mapnik/osm.xml and osm2pgsql/osm2pgsql.c

The two new versions need to go together else you will get lots of
unrendered stuff in your map. 

If you have a custom osm.xml file which you want to update to this new
style then running your file through the attached script should convert
the relevant pieces of SQL for you:

./nullify my-osm.xml > new-osm.xml

	Jon

-------------- next part --------------
#!/bin/sed -f
#
# Updates osm.xml to work with NULL DB fields in place of ""
#
#   char_length(foo)=0   changes to   foo IS NULL
#   char_length(foo)>0   changes to   foo IS NOT NULL

s/char_length(\([a-z_\"]*\)) *= *0/\1 IS NULL/gi
s/char_length(\([a-z_\"]*\)) *> *0/\1 IS NOT NULL/gi