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

[Jon Burgess]

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?
> 

I'm still using a single table at the moment, but i've abbreviated the
SQL by only printing data for non-NULL fields. Here is some examples:


insert into planet_osm (osm_id,"name",way) values (14808352,$$Highfield
Community Centre$$,GeomFromText('POINT(-0.46134678538073
2 51.7650238543195)',4326));

insert into planet_osm (osm_id,"name","amenity",way) values (16059612,
$$The Golden Eagle$$,$$pub$$,GeomFromText('POINT(-0.586965
177911276 51.7391669594696)',4326));

insert into planet_osm (osm_id,"name","place",way) values (14953542,
$$Woburn$$,$$village$$,GeomFromText('POINT(-0.61666618451309
5 51.9863052505635)',4326));

insert into planet_osm (osm_id,"name","railway",way) values (20456010,
$$Berkhampstead$$,$$station$$,GeomFromText('POINT(-0.56208
0820708258 51.7634541115064)',4326));


I'll have to think a bit more about splitting the data into multiple
tables. By moving more of the data processing in the osm2pgsql stage we
may make the osm.xml output less flexible (though it probably does make
the DB access faster and more space efficient).



> Also, I'm not entirely convinced that using TEXT is more efficient than 
> VARCHAR(<length>) ???  Was it mentioned somewhere in docs? 
> 

I don't remember why this was done, it seems Steve made the change

 r1621 | steve | 2006-11-22 11:41:20 +0000 (Wed, 22 Nov 2006) | 2 lines
 change varchars to text


> 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. 
> 

I'll have to take a look. Would linemerge fix up the ways looping back
on themselves that you reported before? What node function are you
thinking of and what would it do? 

	Jon