SF Steps revisited

I improved my query for all the steps in San Francisco, by narrowing it down to steps that are really just in the city of SF itself and not whatever happens to be in the OSM Metro import I have in my database.

First I had to find where in the database was the actual boundary of San Francisco:

select name, * from planet_osm_polygon where boundary = 'administrative';

Visually inspecting that list turns up OSM_ID -111968, San Francisco. Below is a quick drawing of that polygon in OSM. It’s not quite correct; all the water is fine, but it leaves out the Farallons and includes a tiny bit of Alameda and Angel Island. Still, close enough.

Then it’s just a matter of doing the geographic intersection. This query makes me scratch my SQL head a little bit, I cribbed it from the PostGIS book, but it runs pretty fast. Much slower if you try this intersection on all the roads in the SF Metro.

select
planet_osm_line.name, round(ST_Length(planet_osm_line.way)) as meters, planet_osm_line.osm_id
from planet_osm_line
inner join planet_osm_polygon
on ST_Intersects(planet_osm_line.way, planet_osm_polygon.way)
where planet_osm_polygon.osm_id = '-111968'
and planet_osm_line.highway = 'steps'
order by meters desc;

And here we go, 366 stairs.

meters name
280 (none)
245 Sand Ladder
222 (none)
198 Lyon Street
195 Lulu Alley
193 Oakhurst Lane
191 Vulcan Steps
169 Pemberton Place
158 Onique Lane
154 Filbert Steps
154 (none)
148 (none)
147 Harry Street
141 (none)
136 Blairwood Lane
129 (none)
129 Quintara Street
125 Ortega Way
124 Path Street
122 (none)
122 Mount Lane
118 Greenwich Street Stairs
117 Hawkins Lane
114 (none)
114 Terrace Walk
113 Flibert Steps
111 (none)
110 Peter Macchiarini Steps
110 Peter Macchiarini Steps
109 (none)
109 (none)
108 Aerial Way
107 Cascade Walk
106 (none)
104 Joy Street
103 (none)
102 (none)
102 Moraga St. Steps
102 8th Avenue
102 Linda Vista Steps Street
102 Vallejo Street
101 Coralino Lane
101 Mandalay Lane
99 Athens Street
99 Avoca Alley
97 (none)
96 (none)
96 Aerial Way
96 Pacheco Street
95 Glenhaven Lane
95 Farnsworth Lane
94 Oriole Way
93 (none)
93 (none)
92 Esmeralda Avenue
91 (none)
89 (none)
89 (none)
87 (none)
87 Saturn Street Stairs
85 The Agave Trail
83 (none)
83 Acme Alley
83 Waller Street
80 Onique Lane
80 (none)
80 Amatista Lane
80 Stairs
80 (none)
79 Ward Street
79 Vallejo Street Stairway
77 Sunglow Lane
77 Blairwood Lane
77 (none)
76 (none)
76 Fair Avenue
76 Monument Way
74 (none)
74 Macondray Lane
74 Esmeralda Avenue
74 (none)
73 (none)
73 Greenwich Street Stairs
73 (none)
73 Esmeralda Avenue
73 Hillview Court
72 Eugenia Avenue
72 Griffith Street
72 (none)
72 (none)
71 (none)
71 (none)
68 (none)
68 (none)
68 (none)
68 Iron Alley
67 Greenwich Street
67 Vista Lane
67 Terrace Walk
66 Naglee Avenue
66 27th Street
65 (none)
65 Beeman Lane
65 (none)
65 (none)
63 (none)
63 Sunbeam Lane
63 (none)
62 (none)
61 Liberty Street
61 (none)
61 Prospect Avenue
60 3rd Avenue
60 (none)
58 (none)
58 Acme Alley
58 (none)
58 (none)
54 Andover Street
53 (none)
53 Harrison Street
53 (none)
53 (none)
53 (none)
53 (none)
52 (none)
52 (none)
51 Saint Mary’s Avenue
51 Opalo Lane
51 Oneida Avenue
50 Reno Place
50 (none)
49 Chestnut Street Steps
49 Mono Street
49 (none)
49 Douglass Street
47 (none)
47 Greenwich Street Stairs
47 Clifford Terrace Stairway
47 (none)
46 (none)
46 (none)
45 Kenny Alley
45 20th Street
44 Fair Avenue
44 20th Street
44 Levant Street Stairway
43 (none)
42 (none)
42 (none)
42 Corbin Place
41 (none)
41 (none)
41 (none)
41 (none)
39 (none)
39 (none)
38 (none)
38 Duncan Street
38 (none)
38 (none)
37 (none)
37 (none)
37 Priest Street
37 (none)
37 Larkin Street
37 (none)
35 Houston Street
35 (none)
35 (none)
35 (none)
35 (none)
35 (none)
34 (none)
34 (none)
33 (none)
33 Aztec Street
33 (none)
33 (none)
32 (none)
32 (none)
32 (none)
31 (none)
31 (none)
31 (none)
31 Glendale Street
31 (none)
31 (none)
30 (none)
30 (none)
30 (none)
30 Bessie Street
29 (none)
29 (none)
29 (none)
29 (none)
28 (none)
28 (none)
28 Bronte Street
28 (none)
28 (none)
27 (none)
27 (none)
27 (none)
27 (none)
27 (none)
27 (none)
27 (none)
26 (none)
26 Forest Hill Path
25 (none)
25 (none)
25 20th Street
25 (none)
24 (none)
24 (none)
24 (none)
24 (none)
24 (none)
24 (none)
24 (none)
23 (none)
23 Gilroy Street
23 Steps to the soutbound Caltrain platform
23 (none)
23 Havens Street
22 (none)
22 (none)
22 Franconia Street
22 (none)
22 (none)
21 (none)
21 (none)
21 (none)
21 (none)
21 (none)
21 (none)
21 (none)
21 (none)
21 (none)
21 (none)
21 (none)
20 (none)
20 (none)
20 (none)
20 Kingston Street
20 (none)
20 (none)
20 (none)
20 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 (none)
19 Tuscany Alley
18 (none)
18 (none)
18 (none)
18 (none)
18 (none)
18 (none)
17 Codman Place
17 (none)
17 (none)
17 (none)
17 (none)
17 (none)
17 De Forest Way
17 Filbert Steps
17 (none)
16 Moraga Street
16 (none)
16 (none)
16 (none)
16 (none)
16 (none)
16 (none)
16 (none)
16 (none)
15 (none)
15 (none)
15 (none)
15 (none)
15 (none)
15 (none)
14 (none)
14 (none)
14 (none)
14 (none)
14 (none)
14 (none)
14 (none)
14 (none)
14 (none)
14 (none)
14 (none)
13 (none)
13 (none)
13 (none)
13 El Camino del Mar
13 (none)
13 (none)
13 (none)
12 (none)
12 (none)
12 (none)
12 (none)
12 Franconia Street
12 (none)
12 (none)
12 (none)
11 (none)
11 (none)
10 (none)
10 (none)
10 (none)
10 (none)
10 (none)
10 (none)
10 (none)
9 Broadway Street
9 (none)
9 (none)
9 (none)
9 (none)
9 (none)
9 (none)
8 (none)
8 (none)
8 (none)
8 (none)
8 (none)
8 (none)
8 (none)
8 (none)
7 Jack Micheline Place
7 (none)
7 (none)
6 (none)
6 (none)
6 (none)
6 (none)
6 (none)
5 (none)
5 (none)
4 (none)
4 (none)
4 (none)
4 (none)
4 (none)
3 (none)
3 (none)
3 (none)