Select all points within a bounding box

Question

I've seen this question asked before, but the answers I'm trying aren't working.

I'm looking to query all OSM Ways that are within a bounding box. OSM data was imported with default spherical mercator. I'm querying using LAT/LON hence the transform

SELECT *
FROM planet_osm_ways
WHERE ST_Transform(
  ST_MakeEnvelope(37.808264, 37.804339, -122.275244, -122.271189, 
  4326),3857
);

When I run this I get the following error:

ERROR: argument of WHERE must be type boolean, not type geometry LINE 3: WHERE ST_Transform(ST_MakeEnvelope(37.808264, 37.804339, -12...

Answer 1

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.

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

   You incorrectly passed in ymax, ymin, xmax, xmin, srid.

2. 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.

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