Select bounding box using postGIS?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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

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;

i hope it helps you...

Answer 4

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