[OSM-dev] Postgres/PostGIS + OSM newbie questions

[Igor Brejc]

Hi everyone,

For the past few days I've been experimenting with PostGIS, trying to 
create some basic support for it from Kosmos. I've managed to setup the 
DB, import the UK data and even had some moderate success with accessing 
the data from Kosmos code.

Since I'm not very good at SQL (having to relearn tips & tricks every 6 
months or so) and I'm totally new to spatial queries, I'm having some 
practical problems with retrieving data.

For example, I'm trying to fetch a list of OSM ways (and its nodes) for 
a certain area, so that all of ways which have at least one node within 
an area appear in the results. And, at the same time, all of the nodes 
of such ways should be listed too (regardless of whether a node is 
within the area or not).

My current query only returns nodes within the area (so I don't get a 
complete list of way's nodes):

SELECT
  ways.id,
  way_nodes.node_id,
  way_nodes.sequence_id,
  way_tags.k,
  way_tags.v
FROM
  public.ways,
  public.way_tags,
  public.way_nodes,
  public.nodes
WHERE
  way_tags.way_id = ways.id AND
  way_nodes.way_id = ways.id AND nodes.id = way_nodes.node_id AND 
nodes.geom && SetSRID('BOX(15.556640625 46.4983922585976,15.64453125 
46.5588603031172)'::box2d,-1) ORDER BY ways.id, sequence_id')

I'm probably doing this totally wrong, so I have some general questions:

    * should I be doing things differently?
    * are temporary tables a good option for complex queries (from the
      performance point of view)?
    * is there some good + open source of example PostGIS queries
      specific to OSM data schema (the simple one)?

Any help would be greatly appreciated.

Thanks
Igor

-- 
http://igorbrejc.net