[OSM-dev] Questions on PostGIS

Brett Henderson brett at bretth.com
Wed Feb 6 13:49:59 GMT 2008


Milo van der Linden wrote:
> Hello Brett,
>
> Brett Henderson schreef:
>> Hi All,
>>
>> I was hoping to knock up a simple PostgreSQL schema (ie. just planet 
>> data, no history) for manipulating large data sets, primarily to help 
>> with extraction of bounding boxes.  I've written my own custom 
>> database within osmosis but I don't think I'll ever get it to scale 
>> as I hoped so a real database appears to be necessary.
>>
>> I'm planning to use the PostGIS support but my knowledge is extremely 
>> lacking in this area.  
> try http://postgis.refractions.net/docs/. The documentation is realy 
> straight forward and easy to understand.
Great, thanks for that.  I was using the docs that came with my windows 
installation of postgres, but they seem fairly out of date.
>> I'm prepared to go away and do some more research but given that 
>> others already have experience with this they might have some ideas 
>> on how this should be done.  I haven't even looked at the way the 
>> mapnik database works so point me in that direction if its 
>> appropriate.  Just keep in mind that I'm trying to keep things fairly 
>> simple if possible.
>>
>> So far I've created a node table as follows (tags are in a separate 
>> table):
>> CREATE TABLE node
>> (
>>   id bigint NOT NULL,
>>   user_name text NOT NULL,
>>   coordinate point NOT NULL,
>>   tstamp timestamp without time zone NOT NULL,
>>   CONSTRAINT pk_node PRIMARY KEY (id)
>> )
>> WITHOUT OIDS;
>> ALTER TABLE node OWNER TO postgres;
>>
>>   
> * I don't know what version of postGIS you are using, but it is 
> advised not to use the one shipped with postGRES. See if you can 
> uninstall it and take the latest postGIS from 
> http://postgis.refractions.net/
I think I'm using 1.1.6 (that's the doc version anyway) which is 
definitely older than the 1.3.3 (also the doc version) on the website above.
> Anyway, the datatype point should best be replaced with GEOMETRY.
> * In postGIS there is a GeometryColumn constructor:
>
> SELECT AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );
>
> Which makes it possible for you to first define a regular table and 
> add the geometry later.
>
> When it comes to points: I often read X and Y values in normal double 
> columns in the database. When the data is loaded, I add the Geometry 
> column and then do a:
>
> ST_PointFromText ('point (X Y)', 4326)  (4326 is the SRID for the 
> lat/lon wgs84 coordinatesystem.
Great thanks.  I'll try this out.
> This is indeed an issue with the datastructure from OSM. Building 
> queries to combine nodes to dynamically generate ways on every request 
> is a performance killer!
> Basically I think it would be best to write a script that will 
> generate REAL ways (polylines) in the postGIS database in a real 
> geometric table. That way you will take optimized use of geometry 
> functions.
My biggest issue with using proper gis types is that I think I'll still 
have to write to a way_nodes table so that I can re-create real ways 
when I extract data from the database (ie. the conversion is not one 
way).  But if it makes a big difference to query performance then it's 
worthwhile.





More information about the dev mailing list