[OSM-dev] Questions on PostGIS

[Brett Henderson]

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