7

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

  • 1
    Give a link to old questions you have been reading and used for building your query. – user30184 Jan 9 '17 at 18:20
  • Your coordinate order in ST_MakeEnvelope() do not look like they are in this format, xmin, ymin, xmax, ymax – artwork21 Jan 9 '17 at 18:23
  • @artwork21 I'm using a lat/long in ST_MakeEnvelope. Is that wrong? Do I need to convert this to a different format? I thought that's what ST_Transform was doing – theartofbeing Jan 9 '17 at 18:25
  • 1
    Sorry, I had to read the fine manuals including the schema document again wiki.openstreetmap.org/wiki/Osm2pgsql/schema#planet_osm_ways. Planet_osm_ways does not contain geometries and is rather useless for end-user. It is for the system. No chance to make bounding box queries from that table. With tables like planet_osm_roads it is possible. Still planet_osm_roads.geom does not exist, because as you see from the schema, it is named planet_osm_roads.way. – user30184 Jan 9 '17 at 21:44
  • 1
    No. I said you can't do bounding box query from planet_osm_ways because that table does not have any geometries. See: id, nodes, tags, pending - none of them contain geometries. Tables _line, _point, _polygon, and _roads are OK. And I tried to emphasize that geometry column in all the geometry tables is named as "way", not as "geom" or anything else. – user30184 Jan 9 '17 at 21:53
10

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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