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