{"id":732,"date":"2012-08-25T17:28:35","date_gmt":"2012-08-26T00:28:35","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=732"},"modified":"2012-08-28T08:30:41","modified_gmt":"2012-08-28T15:30:41","slug":"distance-to-transit-in-sacog","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=732","title":{"rendered":"Distance to Transit in SACOG"},"content":{"rendered":"<p><script type=\"text\/javascript\">\/\/ < ![CDATA[\n\/\/ < ![CDATA[\njQuery(document).ready(function($) { $(\"#gallery a\").lightBox({fixedNavigation:true});});\n\/\/ ]]><\/script><\/p>\n<p><img decoding=\"async\" src=\"wp-content\/uploads\/2012\/08\/200px-Seal_of_California.svg_.png\" alt=\"parcels\" align=\"right\" height=\"108px\" width=\"108px\"\/><\/p>\n<p>In California, there are multi-county, regional government agencies that perform some functions like planning public transportation systems. The capital city of California is <a href=\"http:\/\/en.wikipedia.org\/wiki\/Sacramento\" title=\"Sacramento\">Sacramento<\/a>, and the regional council is called the <strong>Sacramento Area Council of Governments<\/strong> (<strong><a href=\"http:\/\/www.sacog.org\/about\/advocacy\/pdf\/fact-sheets\/FactSheet_SACOG.pdf\" title=\"SACOG Fact Sheet\">SACOG<\/a><\/strong>).<\/p>\n<p>Using a database of train and light rail stations in a projection where distance measurement is accurate, and the <strong>2010 US Census<\/strong> data, it is possible to find distance from a train station to a census tract or, better yet a census block, like so:<\/p>\n<p><code><\/p>\n<pre>\r\n-- return the distance of each transit station from census tract 1768\r\nSELECT \r\n  b.ogc_fid, \r\n  st_distance( t.wkb_geometry, b.wkb_geometry)::integer as dist\r\nFROM\r\n  transit_stations_updated t,\r\n  census_2010_tracts b\r\nWHERE\r\n  t.region = 'SACOG' and b.ogc_fid = 1768\r\nORDER BY\r\n  st_distance( t.wkb_geometry, b.wkb_geometry);\r\n<\/pre>\n<p><\/code><br \/>\n&nbsp;<\/p>\n<div id=\"gallery\"><a href=\"wp-content\/uploads\/2012\/08\/transit_sacog_1768.png\"><img decoding=\"async\" src=\"wp-content\/uploads\/2012\/08\/transit_sacog_1768-150x150.png\" alt=\"parcels\" align=\"right\" \/><\/a><a href=\"wp-content\/uploads\/2012\/08\/transit_sacog_blocks.png\"><img decoding=\"async\" src=\"wp-content\/uploads\/2012\/08\/transit_sacog_blocks-150x150.png\" alt=\"parcels\" align=\"right\" \/><\/a>\n<\/div>\n<p>But wait, if we want to get the distance to a transit stop, it is far more useful to find the distance to the <em>nearest<\/em> transit stop..  and getting distance to one transit stop is straightforward, but what about <i>all<\/i> transit stops in the district, using one SQL statement for performance?<br \/>\n&nbsp; For this, we can use the handy <a href=\"http:\/\/www.postgresql.org\/docs\/8.4\/static\/functions-window.html\" title=\"Pg Window Funcs\" target=\"_blank\">WINDOW FUNCTIONS<\/a> introduced in PostgreSQL 8.4. Let&#8217;s break the problem into parts.<br \/>\n&nbsp; First, list the distances from one census block, in order, using a <strong>RANK()<\/strong> function too:<br \/>\n&nbsp;<br \/>\n<code><\/p>\n<pre>\r\n-- return the ranked distance of each transit station from census block 433144\r\nSELECT \r\n  b.ogc_fid as census_block_id,\r\n  t.ogc_fid as transit_station_id,\r\n  st_distance( t.wkb_geometry, b.wkb_geometry)::integer as dist,\r\n  rank() OVER \r\n   (partition by b.ogc_fid ORDER BY st_distance( t.wkb_geometry, b.wkb_geometry))\r\nFROM\r\n  transit_stations_updated t,\r\n  census_2010_blocks b\r\nWHERE\r\n   t.region = 'SACOG' and b.ogc_fid = 433144\r\nORDER BY \r\n   st_distance( t.wkb_geometry, b.wkb_geometry)::integer;\r\n\r\n--\r\n census_block_id | transit_station_id | dist  | rank \r\n-----------------+--------------------+-------+------\r\n          433144 |                758 |   176 |    1\r\n          433144 |                715 |   369 |    2\r\n          433144 |                716 |  1152 |    3\r\n          433144 |               1137 |  1377 |    4\r\n...\r\n<\/pre>\n<p><\/code><\/p>\n<p>&nbsp;<br \/>\nWindowing functions have been written to be very general, so the scope of any description is far larger than this post. What we can observe here is that a <strong>partition<\/strong> has been declared by census block, and the <strong>order<\/strong> within the partition is by distance between census block and transit stop. Now we have almost all of the components in place to solve: for every census block, return only the <em>nearest<\/em> transit stop. Here I have written a solution using a subselect &#8211; there are definitely other ways to write this! Note that a judicious use of indexing can speed things up quite a bit.<\/p>\n<p><code><\/p>\n<pre>\r\n-- return only the nearest transit station to each census block and if less than 100 meters\r\nSELECT census_block_id, transit_station_id, dist\r\nFROM\r\n(\r\n  SELECT \r\n    b.ogc_fid as census_block_id,\r\n    t.ogc_fid as transit_station_id,\r\n    st_distance( t.wkb_geometry, b.wkb_geometry)::integer as dist,\r\n    rank() OVER \r\n      (partition by b.ogc_fid ORDER BY st_distance( t.wkb_geometry, b.wkb_geometry)) as rank\r\n  FROM\r\n    transit_stations_updated t,\r\n    census_2010_blocks b\r\n  WHERE \r\n   st_distance(t.wkb_geometry, b.wkb_geometry) < 100 AND\r\n   b.countyfp10 = '067' AND\r\n   t.region = 'SACOG'\r\n) t1\r\n\r\nWHERE t1.rank = 1;\r\n<\/pre>\n<p><\/code><br \/>\n&nbsp;<\/p>\n<div id=\"gallery\"><a href=\"wp-content\/uploads\/2012\/08\/vmt_analysis_calibration_20mar11-1024x640.png\"><img decoding=\"async\" src=\"wp-content\/uploads\/2012\/08\/vmt_analysis_calibration_20mar11-300x187.png\" alt=\"parcels\" align=\"right\" \/><\/a>\n<\/div>\n<p>Finally, a variation of this problem is to take all locations within a certain buffer distance of <em>at least one<\/em> transit stop, and record them along with the distance to the <em>nearest<\/em> transit stop.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In California, there are multi-county, regional government agencies that perform some functions like planning public transportation systems. The capital city of California is Sacramento, and the regional council is called the Sacramento Area Council of Governments (SACOG). Using a database of train and light rail stations in a projection where distance measurement is accurate, and [&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],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/732"}],"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=732"}],"version-history":[{"count":90,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/732\/revisions"}],"predecessor-version":[{"id":833,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/732\/revisions\/833"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=732"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}