[OSM-dev] Questions on PostGIS
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
>> 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
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
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
More information about the dev