Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostGIS operation queries on OSM data
-- Nearby Ameneties
SELECT name, st_geomfromtext('POINT(77.64363 12.97069)') <-> way::geography AS dist, way FROM planet_osm_point WHERE amenity = 'police' ORDER BY dist ASC LIMIT 4 ;
-- Within Distance
SELECT name, ST_Geomfromtext('POINT(77.64363 12.97069)') <-> way::geography AS dist, way FROM planet_osm_point WHERE amenity = 'school' AND ST_Dwithin(way, ST_Geomfromtext('POINT(77.64363 12.97069)',4326), 1000) ORDER BY dist ASC LIMIT 4 ;
-- Finding Length
SELECT st_length(way::geography) FROM planet_osm_line WHERE osm_id = 35133687;
-- Distance between points
SELECT ST_Distance(ST_Geomfromtext('POINT(77.12345 12.12345)')::geography, ST_Geomfromtext('POINT(77.7890 12.7890)')::geography);
-- Longest Road
SELECT name, SUM(ST_Length(ST_Transform(way,4326)::geography)) AS len FROM planet_osm_roads WHERE name IS NOT null GROUP BY name ORDER BY len DESC LIMIT 2;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment