Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
1  
Couldn't you create a polygon of your area of interest and delete all roads outside of it? –  underdark Jan 23 '12 at 17:25
    
Yes, but I actually I should do it for every subarea and every city... not so easy to do. I thought it was a polygon on planet_osm_polygon with subarea. To solve this issue, I was thinking to get all polygons near a point, probabilly it works. –  Andrea Girardi Jan 23 '12 at 17:30
add comment

1 Answer

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);

share|improve this answer
    
You should read the Terms and Conditions section of the link you've posted. That API should be used for editing, not read-only purposes. And the query bbox size is fairly limited anyway. You should use OSM XAPI instead. –  Igor Brejc Jan 26 '12 at 22:13
    
There's no evidence in the OP post about their intent for the data. But I'll add an XAPI link (to the one that works, not the OSM one). –  Bryce Jan 27 '12 at 22:32
    
"Editing" means changing the data in the main OSM database. Given that OP uses his own PostGIS database filled with an OSM snapshot, and given the question is about extracting the data, this constitutes read-only usage. The Terms and Conditions are there to protect scarce OSM resources from abusing them for purposes which the main OSM API is not designed for. –  Igor Brejc Jan 27 '12 at 23:17
    
While you are probably right, the OP could well be improving the data and intending to return it to the server. That said there are a dozen ways to get the data that don't hit the API. –  Bryce Jan 29 '12 at 1:13
    
You cannot do that with a snapshot, because the data on the server might (and will) change between the time the planet extract was created and your attempt to improve the data. The objects from the snapshot might even no longer exist in the main DB. You'd only create a mess with that approach. –  Igor Brejc Jan 29 '12 at 7:23
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.