[OSM-dev] Questions on PostGIS

[Artem Pavlenko]

Hi Bret,

Have a look at osm2pgsql.  It will give you a head start. It is fast  
and it runs on Linux,Mac OS X and windows. Resulting database is  
optimized for bounding box queries. You can then modify schema to  
match your requirements.

Cheers,
Artem

On 6 Feb 2008, at 12:25, Brett Henderson wrote:

> 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.  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;
>
> CREATE INDEX idx_node_location
>   ON node
>   USING gist
>   (circle(coordinate, 0::double precision));
>
> Does this look to be on the right track?  What is the right way of
> indexing point types?  Or should I be using different data types?   
> I'm a
> bit confused about the difference between geometry types and types  
> such
> as point, circle, etc.
>   More importantly, how do I query within a bounding box?
>
> One other thing I noticed is that PostGIS has its own methods for  
> adding
> geometry columns to tables.  Is this something I should be using?   
> Will
> this adversely affect the size of the on-disk data?
>
> The next question is how do I support ways?  My current schema just  
> has
> a way table and a way_node table which aligns with the way the
> production mysql db works.  Perhaps if I used geo-spatial types  
> instead
> I could do smarter things with bounding box queries (ie. find ways  
> that
> cross a box without any nodes residing within it).  My main  
> concerns are
> that this may 1. drastically increase load times 2. increase data  
> size.
>
> The way tables in question are below (again way tags are elsewhere):
>
> CREATE TABLE way
> (
>   id bigint NOT NULL,
>   user_name text NOT NULL,
>   tstamp timestamp without time zone NOT NULL,
>   CONSTRAINT pk_way PRIMARY KEY (id)
> )
> WITHOUT OIDS;
> ALTER TABLE way OWNER TO postgres;
>
> CREATE TABLE way_node
> (
>   way_id bigint NOT NULL,
>   node_id bigint NOT NULL,
>   sequence_id smallint NOT NULL,
>   CONSTRAINT pk_way_node PRIMARY KEY (way_id, sequence_id)
> )
> WITHOUT OIDS;
> ALTER TABLE way_node OWNER TO postgres;
>
> Sorry for the unstructured mess of thoughts above but any input  
> would be
> appreciated,
>
> Cheers,
> Brett
>
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev