1

I'm trying to query specific amenities in a specific region on a postGIS database.

I'd like to be able to select multiple amenities in the query, and output their OSM ID, type, lat, lon, & name.

For example:

a nice query on amenity:

SELECT osm_id,ST_X(ST_TRANSFORM(way,4674)) AS X, ST_Y(ST_TRANSFORM(way,4674)) as Y,name
from planet_osm_point where amenity='school' limit 5;

I can't get any results from a bounding box search, and that's part of the problem:

/*with "bbox() ~ way"*/
SELECT osm_id,ST_X(ST_TRANSFORM(way,4674)) AS X, ST_Y(ST_TRANSFORM(way,4674)) as Y,name
from planet_osm_point where ST_MakeEnvelope(-125.0011, 24.9493, -66.9326, 49.5904, 4326) ~ way limit 5;

/*with "way @ st_MakeEnvelope()"*/
SELECT osm_id,ST_X(ST_TRANSFORM(way,4674)) AS X, ST_Y(ST_TRANSFORM(way,4674)) as Y,name
from planet_osm_point where way @ ST_MakeEnvelope(-125.0011, 24.9493, -66.9326, 49.5904, 4326) limit 5;

Here's a gist with a bunch of example queries I've tried to make work on the DBs.

  • Related gis.stackexchange.com/questions/83387/… @ST_Contains – Mapperz Mar 17 '17 at 16:43
  • @Mapperz - Neither SELECT osm_id,ST_X(ST_TRANSFORM(way,4674)) AS X, ST_Y(ST_TRANSFORM(way,4674)) as Y,name from planet_osm_point where way @ ST_MakeEnvelope(-125.0011, 24.9493, -66.9326, 49.5904, 4326) limit 5; nor SELECT osm_id,ST_X(ST_TRANSFORM(way,4674)) AS X, ST_Y(ST_TRANSFORM(way,4674)) as Y,name from planet_osm_point where ST_MakeEnvelope(-125.0011, 24.9493, -66.9326, 49.5904, 4326) ~ way limit 5; return results. – user93486 Mar 17 '17 at 16:55
  • your using 4674 epsg.io/4674 and your bounds limits are -122.19 -59.87m,-25.28,32.72 I see a mistake in your bbox. – Mapperz Mar 17 '17 at 17:15
  • @Mapperz Thank you! I ended up using SELECT osm_id,ST_X(ST_TRANSFORM(way,4674)) AS X, ST_Y(ST_TRANSFORM(way,4674)) as Y,name from planet_osm_point where (amenity='school' OR amenity='fire_station') AND ST_TRANSFORM(way,4674) @ ST_MakeEnvelope(-125.0011, 24.9493, -66.9326, 49.5904, 4674) limit 5; – user93486 Mar 17 '17 at 19:41
1

Answering from the comments.

My mistake was using the wrong coordinates system, everyting should have been in epsg/4674.

SELECT osm_id,ST_X(ST_TRANSFORM(way,4674)) AS X, ST_Y(ST_TRANSFORM(way,4674)) as Y,name
from planet_osm_point where
(amenity='school' OR amenity='fire_station')
AND ST_TRANSFORM(way,4674)
@
ST_MakeEnvelope(-125.0011, 24.9493, -66.9326, 49.5904, 4674) limit 5;

Your Answer

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