{"id":1415,"date":"2013-09-27T18:19:05","date_gmt":"2013-09-28T01:19:05","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=1415"},"modified":"2014-07-20T16:39:09","modified_gmt":"2014-07-20T23:39:09","slug":"distance-to-nearest-osm-road","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=1415","title":{"rendered":"Distance to Nearest OSM Road"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2012\/06\/PostgreSQL_logo.3colors.120x120.png\" alt=\"PostgreSQL_logo.3colors.120x120\" width=\"120\" height=\"120\" class=\"alignright size-full wp-image-575\" \/><\/p>\n<p><code><\/p>\n<pre>\r\nosm_ca_20=# \r\nSELECT \r\n&nbsp;&nbsp;osm_id, \r\n&nbsp;&nbsp;geometry < -> st_geomfromEWKT( 'SRID=900913;POINT(-13145550 4045137)' ) as the_distance\r\nFROM\r\n&nbsp;&nbsp;osm_new_mainroads \r\nORDER BY\r\n&nbsp;&nbsp;geometry < -> st_geomfromEWKT( 'SRID=900913;POINT(-13145550 4045137)' )  limit 1;\r\n\r\n\r\n  osm_id  |     the_distance\r\n----------+------------------\r\n 59339590 | 268.205611425265\r\n(1 row)\r\n<\/pre>\n<p><\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2013\/09\/example_osm_pt_to_road.png\" alt=\"example_osm_pt_to_road\" width=\"434\" height=\"253\" class=\"alignnone size-full wp-image-1419\" srcset=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2013\/09\/example_osm_pt_to_road.png 434w, http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2013\/09\/example_osm_pt_to_road-300x174.png 300w\" sizes=\"(max-width: 434px) 100vw, 434px\" \/><\/p>\n<p><strong>UPDATE<\/strong><\/p>\n<p>distance of all museums less than 30km away from a school, by school. Thanks to the <a href=\"http:\/\/www.geonames.org\/\" title=\"geonames\" target=\"_blank\">geonames project<\/a> for the locations.<\/p>\n<p><code><\/p>\n<pre>\r\nselect  \r\n  distinct on (s.name, m.name)\r\n  s.name as school_name, s.admin2, \r\n  m.name as museum_name, m.admin2, \r\n  st_distance( s.geom::geography, m.geom::geography )::integer as dist, \r\n  rank() over ( partition by (s.name, s.admin2)   \r\n    order by st_distance( s.geom::geography, m.geom::geography )) as rank\r\nfrom   museum m, school s\r\nwhere \r\n  s.admin2 = 'Alameda County'  AND \r\n  m.admin1 = 'California'  AND \r\n  st_dwithin( m.geom::geography, s.geom::geography, 30000 )\r\nORDER BY  s.name, m.name, dist;\r\n<\/pre>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>osm_ca_20=# SELECT &nbsp;&nbsp;osm_id, &nbsp;&nbsp;geometry < -> st_geomfromEWKT( &#8216;SRID=900913;POINT(-13145550 4045137)&#8217; ) as the_distance FROM &nbsp;&nbsp;osm_new_mainroads ORDER BY &nbsp;&nbsp;geometry < -> st_geomfromEWKT( &#8216;SRID=900913;POINT(-13145550 4045137)&#8217; ) limit 1; osm_id | the_distance &#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; 59339590 | 268.205611425265 (1 row) UPDATE distance of all museums less than 30km away from a school, by school. Thanks to the geonames project for the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1415"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1415"}],"version-history":[{"count":5,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1415\/revisions"}],"predecessor-version":[{"id":1555,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1415\/revisions\/1555"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1415"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1415"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}