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

Give a link to old questions you have been reading and used for building your query. — user30184, Jan 09 '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 09 '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 — hummmingbear, Jan 09 '17 at 18:25
@user30184 http://gis.stackexchange.com/questions/25797/select-bounding-box-using-postgis — hummmingbear, Jan 09 '17 at 18:26
You can use lat/lng, your ordering of coordinates seems to be not correct though (needs to be xmin, ymin, xmax, ymax) — artwork21, Jan 09 '17 at 18:27
@user30184 and http://gis.stackexchange.com/questions/60700/postgis-select-by-lat-long-bounding-box — hummmingbear, Jan 09 '17 at 18:28
@artwork21 even if I switch them I'm still getting an error... — hummmingbear, Jan 09 '17 at 18:28
You don't do spatial comparison at. In the answer you reference there is the bbox intersects && but you can use ST_Intersects if you wish. — user30184, Jan 09 '17 at 18:30
Also your envelope is wrong and it should be minx miny maxx maxy where x=longitude and y=latitude. — user30184, Jan 09 '17 at 18:34
@user30184 the answer I listed used mytable.geom && ST_Transform.... I'm not sure what mytable.geom field there is? — hummmingbear, Jan 09 '17 at 18:49
Read is "select features from my_table where the geometries of my_table intersects with the reference geometry, which is this envelope transformed into EPSG:3857". If you play with OSM data imported with osm2pgsql the "mytable" is planet_osm_ways and geom it the geometry field of that, called "way". — user30184, Jan 09 '17 at 19:40
@user30184 planet_osm_ways.geom isn't a field that exists in that table. What am I missing here? — hummmingbear, Jan 09 '17 at 21:30
Sorry, I had to read the fine manuals including the schema document again http://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 09 '17 at 21:44
@user30184 I have planet_osm_roads...but are you saying it is not possible to create a bounding box query with OSM data? — hummmingbear, Jan 09 '17 at 21:48
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 09 '17 at 21:53
I agree with @user30184. Provide the \d planet_osm_ways here. You can not spatially query a table without spatial-datatypes (or things that can be coerced into them). End of story. — Evan Carroll, Jan 10 '17 at 03:46

kttii · Answer 1 · 2017-01-10T15:37:56.583

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

Select all points within a bounding box

1 Answers1