[GIS] Select bounding box using postGIS

extentsopenstreetmaposmosispostgisvector

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?

Best Answer

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.

Related Solutions

[GIS] PostGIS select by lat/long bounding box

Your data is not in lat/lon, so you need to push your box into the coordinate space of your data:

SELECT *
FROM mytable
WHERE 
  mytable.geom && 
  ST_Transform(ST_MakeEnvelope(minLon, minLat, maxLon, maxLat, 4326), 2223);

[GIS] Select all points within a bounding box

You have three problems with your statement though the error message is hinting only at part of it... "WHERE must be type boolean" means that the information you gave the WHERE is not evaluating to a boolean result.

ST_MakeEnvelope asks for its parameters in this order: xmin, ymin, xmax, ymax, srid.

You incorrectly passed in ymax, ymin, xmax, xmin, srid.
WHERE must evaluate to boolean:

To determine if a geometry and the envelope has any elements in common the WHERE should be constructed like so: WHERE geom && envelope_geom otherwise you can use ST_Contains

To determine if the geometry is contained within the envelope: `WHERE ST_Contains(envelope_geom,geom)

You didn't supply any method of comparison for the WHERE.
Table 'planet_osm_ways' does not contain any geometry column, though 'planet_osm_roads' does contain a geometry column named 'way'.

You can create a geometry column in table 'planet_osm_ways' from the related planet_osm_nodes.lat and planet_osm_nodes.lon.

Using 'planet_osm_roads', this shows how to use a bounding box against a table with a geometry column:

SELECT *
FROM planet_osm_roads
WHERE planet_osm_roads.way && ST_Transform(
  ST_MakeEnvelope(-122.271189, 37.804339, -122.275244, 37.808264, 
  4326),3857
);

or change it to this:

SELECT *
FROM planet_osm_roads
WHERE ST_Contains(
    ST_Transform(
        ST_MakeEnvelope(-122.271189, 37.804339, -122.275244, 37.808264, 
        4326)
        ,3857)
    ,planet_osm_roads.way);

Best Answer

Related Solutions

[GIS] PostGIS select by lat/long bounding box

[GIS] Select all points within a bounding box

Related Question