public
Last active

seatmate spatial query, route line closest to point

  • Download Gist
improved.sql
SQL
1 2 3 4
SELECT "RTE" as route, "RTE_DESC" as description,
min(distance(PointFromText('POINT(-122.613639 45.499541)', 4326), the_geom)) as distance
from tm_routes group by route, description
order by distance limit 10;
improved_results.txt
1 2 3 4 5 6 7 8 9 10 11 12 13
route | description | distance
-------+------------------------+---------------------
9 | Powell/Broadway | 0.00200889736828729
14 | Hawthorne | 0.00229043846550735
71 | 60th Ave/122nd Ave | 0.00460586966268184
4 | Division/Fessenden | 0.00575370636240205
75 | Cesar E Chavez/Lombard | 0.00898150226029743
66 | Marquam Hill/Hollywood | 0.00898150861891694
17 | Holgate/NW 21st | 0.00924074516961275
10 | Harold St | 0.0148568363291083
15 | Belmont/NW 23rd | 0.0170180326549672
19 | Woodstock/Glisan | 0.020370095002805
(10 rows)
results.txt
1 2 3 4 5 6 7 8 9 10 11 12 13
route | description | direction | distance
-------+------------------------+--------------------------------+---------------------
9 | Powell/Broadway | To Powell & 98th or Gresham TC | 0.00200889736828729
9 | Powell/Broadway | To Saratoga & 27th | 0.00200890251737265
14 | Hawthorne | To Foster & 94th | 0.00229043846550735
14 | Hawthorne | To Portland City Center | 0.00229044244641688
71 | 60th Ave/122nd Ave | To Foster & 94th | 0.00460586966268184
71 | 60th Ave/122nd Ave | To Clackamas Town Center | 0.00460588437600594
4 | Division/Fessenden | To Gresham TC | 0.00575370636240205
4 | Division/Fessenden | To St Johns | 0.00575370636240205
75 | Cesar E Chavez/Lombard | To St. Johns | 0.00898150226029743
75 | Cesar E Chavez/Lombard | To Milwaukie | 0.00898150543661629
(10 rows)
spatial.sql
SQL
1 2 3
SELECT "RTE" as route,"RTE_DESC" as description,"DIR_DESC" as direction,
distance(PointFromText('POINT(-122.613639 45.499541)', 4326), the_geom) as distance
from tm_routes order by distance limit 10;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.