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.
_______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
