How to extract data for a specific area from OSM? [duplicate]

Question

I need to extract all streets available on a particular area of a City. I'm able to extract all streets name using this query:

select distinct P2.*
from 
planet_osm_polygon P1,
planet_osm_line P2
where P2.name is not null AND P1.osm_id = -80277 and ST_Intersects(P2.way, P1.way) 
order by P2.name

(in this casa -324211 is the city of Toronto). Now, I need to extract the streets name of only a particular subarea of the City. I can found the information about the subarea on planet_osm_point table but, how can I extract only the line or rows are in one particular subarea?

With this I can extract all subarea of Toronto:

select  P2.osm_id, P2.boundary, P2.name
from 
planet_osm_polygon P1,
planet_osm_point P2
where P2.name is not null AND P1.osm_id = -324211 and ST_Intersects(P2.way, P1.way)  and P2.place = 'suburb'
order by P2.name

is there a way to do that?

--- UPDATE ---

One solution is, starting from a point, create a square or round polygon and extracts all streets that intersect this new polygon, something like that:

select asText(way) from planet_osm_point where osm_id = 349354234

and, when I use the results here:

select  P2.*
from 
planet_osm_line P2
where P2.name is not null AND ST_Intersects(P2.way, (ST_Buffer('POINT(-8840304.91218709 5429889.22642508)', 50) ) )

I get this error message: Operation on mixed SRID geometries

On a blog I read how to update my SRID table geometry column but, is it the right way to fix that?

thanks for your help! Any suggestion (not only a solution) will be accepted and very helpful!

Andrea

PS: I imported data using osm2pgsql but, if I understand well (I'm newbie here) I can found more information using osmosis, correct?

Answer 1

Gosh, why do it the hard way?

The operation you want is baked directly into the XML editing API. Define a bounding box, and you'll get all points within, and all lines that intersect the bounding box. For details read http://wiki.openstreetmap.org/wiki/API or more likely http://developer.mapquest.com/web/products/open/xapi . Check your license restrictions.

Osmosis can do this operation also.

Our you can handle it at import time, with a bounding box:

osm2pgsql --bbox -0.5,51.25,0.5,51.75 -m -d gis planet_subset_usa.osm.bz2

The osm2pgsql README also has a clue, if for some reason you need to query from a huge database:

How could I get e.g. all highways in a given bounding box?

gis=> select osm_id,highway,name from planet_osm_line where highway is not null and way && GeomFromText('POLYGON((0 52, 0.1 52, 0.1 52.1, 0 52.1, 0 52))',4326);