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 want to create a query to select all ways and their nodes that exist within a bounding box using postGIS. The bounding box shall includes all details as osmosis "--bounding-box" command will retrieves.

Is there any way to do that?

Thanks!

share|improve this question

4 Answers 4

up vote 15 down vote accepted

For the osmosis docs, I see the command option:

--bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201

for PostGIS you can use ST_MakeEnvelope(left, bottom, right, top, srid) to build a bounding box, then the && bounding box operator to find where the bounding boxes intersect:

SELECT *
FROM mytable
WHERE mytable.geom && ST_MakeEnvelope(10.9351, 49.3866, 11.201, 49.5138, 4326);

The SRID 4326 is for WGS84 Lat/Long, and is only required for PostGIS 1.5; it can be omitted for later versions.

share|improve this answer
    
Thanks. the ST_MakeEnvelope function needs one more parameter, srid. I don't know what to put there.. any idea? –  uriel May 21 '12 at 22:32
    
Looks like you are using PostGIS 1.5, which requires that parameter. I think that SRID is ignored, so any value might yield the same results. If you have lat/long data, generally use an SRID of 4326. –  Mike T May 21 '12 at 22:43
    
Most tools these days let you let you choose the SRID for OSM data when you load it. The default OSM SRID is 3857 (spherical mercator). The default SRID for most lat/lon data is SRID 4326 (Lat/Lon AKA WGS84). If you load the data with SRID 3857, for example, you will have to do a conversion from LAT/LON WGS84 into 3857: ST_Transform(ST_MakeEnvelope(LON1, LAT1, LON2, LAT2, 4326),3857) Some tools (like imposm3) currenly only support SRID 3857 –  Justin Swanhart Jan 17 at 22:03

I think it will be something like this: The bounding box in PostGIS is created by

ST_GeomFromText('POLYGON((ulx uly, urx ury, llx llr, lrx lry, ulx uly))', <srid>)

The query will use ST_Intersection with a subquery.

SELECT bbox_nodes.id, bbox_nodes.tag, nodes_geom 
FROM (SELECT nodes.id, nodes.tag, 
   ST_Intersection(nodes.the_geom, 
      ST_GeomFromText('POLYGON((ulx uly, urx ury, llx llr, lrx lry, ulx uly))', <srid> )).geom AS nodes_geom
   FROM nodes 
   WHERE ST_Intersects(nodes.the_geom, 
      ST_GeomFromText('POLYGON((ulx uly, urx ury, llx llr, lrx lry, ulx uly))', <srid> )) AS bbox_nodes
WHERE ST_Dimension(bbox_nodes.nodes_geom)=0;

I more or less took this from The PostGIS help pages
A second query, on the ways table, designed similar to the above (but with ST_Dimension()=1 ) should get the ways.

HTH, Micha

share|improve this answer
    
Hi, Thanks! what the srid? what shoul I need to insert in <srid>? and ".geom" (line 4) it seems to be invalid, it's should be there? –  uriel May 21 '12 at 22:27
    
Sorry, I missed your comment from last week. The srid is the Coordinate Reference System code. i.e. 2039 for Israel. The .geom addition extracts the Geometry part of a "GeometryCollection. You might be right that it's not required here. –  Micha May 29 '12 at 5:54

there is a topic here which similar to your question here...

ST_Intersection — (T) Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84.

1.you can also get some information here about Geometry Constructing Functions.

SELECT ST_AsText(ST_Intersection(
  ST_Buffer('POINT(0 0)', 2),
  ST_Buffer('POINT(3 0)', 2)
));

intersection

2.Another information here about Intersects Intersection: PostGIS - ST_Intersects, ST_Intersection...

SELECT b.the_geom As bgeom, p.the_geom As pgeom, 
        ST_Intersection(b.the_geom, p.the_geom) As intersect_bp
    FROM buildings b INNER JOIN parcels p ON ST_Intersection(b,p)
    WHERE ST_Overlaps(b.the_geom, p.the_geom)
    LIMIT 1;

intersection

i hope it helps you...

share|improve this answer

This is a comment on @Micha's code.

The coordinate pairs for the POLYGON should follow a clockwise (or a counter clockwise) order : upper left, upper right, lower right, lower left, upper left again.

So, clockwise, the function call should be :

ST_GeomFromText('POLYGON((ulx uly, urx ury, lrx lry, llx llr, ulx uly))', <srid>)

Or counter clockwise :

ST_GeomFromText('POLYGON((ulx uly, llx llr, lrx lry, urx ury, ulx uly))', <srid>)
share|improve this answer

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.