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.

There’s a bunch of location tracking apps now, all of which basically collect where you are all the time in a casual way. The key thing is that they only sample location occasionally, to save battery life.

I did a 15 minute drive in San Francisco today, then a 40 minute walk home. About 2.5 miles each way. I was running four of these tracking apps at once on my iPhone, to compare how much data they logged.

• Google Latitude: 12 points
• Geoloqi (Battery saver mode): 4 points
• OpenPaths: 6 points

I was curious about what ways in OpenStreetMap were defined as Steps, so I did some basic queries and maps and reports. The query’s overly broad, I need to learn how to intersect that with the actual city of SF.

select
  name, round(ST_Length(way)) as meters, osm_id
from planet_osm_line
where highway = 'steps'
order by meters desc;

To better understand the OSM schema, I did a bunch of queries to find out which columns in which tables actually contained meaningful data. Kind of like the query about highway types I did, but for every single column. Then I went through by hand and copied out the “interesting” columns, ones that seemed to have a lot of data.

Here’s a big list by table, then column, then  the distribution of the values in that column. SF Metro only. One thing that’s clear; even in SF the data’s not complete enough to be an unbiased sample. For instance you can’t tell from OSM how many Jewish synagogues there are vs. how many Christian churches, the data’s just not complete enough.

Table planet_osm_line has 62256 rows

aeroway
99% 62093 None
 0% 143 taxiway
 0% 16 runway
 0% 3 apron

bridge
96% 60376 None
 3% 1872 yes

foot
96% 60024 None
 3% 2060 yes
 0% 117 permissive

highway
46% 29165 residential
14% 8897 service
10% 6307 None
 8% 5252 footway
 3% 1870 motorway_link
 2% 1841 tertiary
 2% 1765 secondary
 1% 1150 motorway
 1% 1113 unclassified
 1% 1063 path

man_made
98% 61560 None
 0% 618 pier
 0% 62 pipeline
 0% 12 breakwater

oneway
85% 52927 None
14% 9163 yes
 0% 74 no

railway
97% 60397 None
 1% 787 rail
 0% 322 tram
 0% 314 subway
 0% 166 spur
 0% 79 platform
 0% 68 disused
 0% 58 abandoned
 0% 42 light_rail
 0% 19 monorail

route
98% 61516 None
 0% 420 bicycle
 0% 122 road
 0% 73 subway
 0% 39 tram
 0% 29 scenic
 0% 23 bus
 0% 18 ferry
 0% 8 hiking
 0% 4 railway

service
91% 56958 None
 5% 3588 parking_aisle
 1% 956 driveway
 0% 321 spur
 0% 191 alley
 0% 121 siding

tunnel
99% 62024 None
 0% 227 yes

waterway
97% 60497 None
 2% 1317 stream
 0% 345 canal
 0% 74 drain
 0% 12 river
 0% 7 dam
Table planet_osm_polygon has 39872 rows

aeroway
99% 39841 None
 0% 17 apron
 0% 9 terminal
 0% 3 aerodrome
 0% 1 serodrome
 0% 1 boundary

amenity
92% 36797 None
 5% 2098 parking
 1% 416 school
 0% 81 restaurant
 0% 71 place_of_worship
 0% 51 fast_food
 0% 30 library
 0% 27 hospital
 0% 25 bank
 0% 19 toilets

building
60% 24237 yes
19% 7585 None
12% 5165 residential
 5% 2196 house
 0% 228 school
 0% 106 tank
 0% 55 apartments
 0% 54 retail
 0% 35 garages
 0% 34 terrace

leisure
94% 37581 None
 2% 875 pitch
 2% 847 park
 0% 177 playground
 0% 113 common
 0% 71 swimming_pool
 0% 47 golf_course
 0% 47 garden
 0% 31 sports_centre
 0% 24 track
Table planet_osm_point has 30916 rows

aeroway
99% 30763 None
 0% 107 gate
 0% 36 helipad
 0% 8 aerodrome
 0% 1 seaplane_base
 0% 1 checkin

amenity
73% 22602 None
 3% 1225 restaurant
 3% 997 place_of_worship
 3% 945 school
 2% 687 fire_hydrant
 1% 500 post_box
 1% 421 cafe
 0% 266 fast_food
 0% 263 bench
 0% 256 parking

highway
68% 21209 None
13% 4047 turning_circle
 5% 1850 traffic_signals
 4% 1534 crossing
 3% 979 bus_stop
 2% 903 stop
 0% 189 motorway_junction
 0% 184 mini_roundabout

leisure
97% 30131 None
 1% 524 park
 0% 151 playground
 0% 27 sports_centre
 0% 13 garden
 0% 10 slipway
 0% 9 designated smoking area
 0% 8 common
 0% 8 swimming_pool
 0% 8 picnic_table

man_made
99% 30620 None
 0% 100 water_tower
 0% 85 tower
 0% 30 crane
 0% 22 pier
 0% 12 monitoring_station
 0% 10 water_tank
 0% 6 mine
 0% 5 survey_point
 0% 4 lighthouse

natural
99% 30718 None
 0% 86 peak
 0% 44 bay
 0% 28 beach
 0% 18 tree
 0% 8 wood
 0% 7 spring
 0% 4 wetland
 0% 2 cave_entrance
 0% 1 cliff

power
93% 28782 None
 6% 2038 tower
 0% 90 pole
 0% 3 sub_station
 0% 2 station
 0% 1 generator

railway
97% 30282 None
 0% 260 level_crossing
 0% 168 tram_stop
 0% 63 subway_entrance
 0% 60 station
 0% 54 switch
 0% 21 crossing
 0% 4 turntable
 0% 3 buffer_stop
 0% 1 stop

religion
96% 29948 None
 3% 937 christian
 0% 14 jewish
 0% 12 buddhist
 0% 3 muslim
 0% 1 unitarian
 0% 1 scientologist

I had a little todo item today, “Edit OpenStreetMap”. Thought it’d be fun to try out the editor. After 10 minutes I declare failure with Potlatch; the Flash app on the main server is so terribly slow as to be unusable. I need to read the Beginner’s Guide and other editing options.

After a bit of patience, Potlatch loaded usefully. I ended up editing the map to put a name on the tiny little Duncan-Castro park. I hope I did this right, the UI for adding a name to a park is not clear. I ended up using the Advanced view to add a name=Duncan-Castro field to the list for the way.

My big plan was to edit one of the 6000+ entries in the SF Metro extract that have no “highway” tagged on them. Turns out that’s not necessary; they tend to be other features, like creeks or county boundaries or stuff. D’oh.

I did find one entry for an uncategorized “Center Street” in SF, osm_id 8489931. But there’s so many other overlapping “Center Streets” in the database I think the fact this one is untagged is not a problem.

select
  name,
  ST_Length(ST_Transform(way, 2163))/1609 as miles
from planet_osm_line
where
  highway is null
order by miles desc;

"Golden Gate National Recreation Area";50.114480677903
"";50.1127120036684
"San Francisco";50.1127120036684
"San Mateo County";49.8960294825062
"";49.6164354346274
"Alameda County";49.5445744035326
"";49.3526266530211
"San Francisco County";49.3158064558191
"";49.2591797769354
"";49.2302041734416
"";49.2049183432214
"";49.1809376585467
"";49.0948344172935

I revisted my earlier OSM experiment only using TileMill instead of QuantumGIS to make the map. TileMill has much nicer output, particularly if you want to ship a set of slippy map tiles or frame out specific high quality images / prints. It’s also more of a textual programming kind of paradigm, which I like better. OTOH TileMill also has very specific limits on what it’s capable of doing; it’s not a generic GIS toolset.

It was pretty easy; the hardest part was remembering the weird way to filter a table with select in TileMill; their PostGIS support is a bit idiosyncratic. Also it guessed wrong on the SRS for my tracks; they’re in WGS84 but it was trying to draw them as if they were in Google Mercator, way out of scale. That seems like a bug.

Anyway, I remade my map of my walks around SF, here’s an image and some code.

        "table": "(select * from planet_osm_polygon\n where leisure = 'park'\n  and geometrytype(\"way\") in ('POLYGON', 'MULTIPOLYGON')\n) as parks",
        "table": "(select * from planet_osm_line\n where \"railway\" is null\n) as road",

@road: #444444;
@border: #222222;
@park: rgb(196, 243, 170);
@water: #b8dee6;
@bg: #ffffff;
@track: rgb(237,101,22);

Map { background-color: @water; }

#sanfrancisco {
  polygon-opacity:1;
  polygon-fill:@bg;
  line-width: 2;
  line-color: @border;
}

#tracks {
  line-color: @track;
  line-width: 3.0;
}
#road {
  line-color: @road;
  [highway='motorway'] { line-width: 3.0; }
  [highway='motorway_link'] { line-width: 2.0; }
  [highway='footway'] { line-width: 0.7; line-color: lighten(@road, 20%); }
}
#parks {
  line-color: @border;
  line-width: 1.0;
  polygon-opacity: 1;
  polygon-fill: @park;
}

OpenStreetMap is all kinds of awesome, but boy the data schema is a mess. planet_osm_roads doesn’t really contain all the roads, you want planet_osm_line. (Yes, one name is singular, one is plural). planet_osm_line sure contains a lot of lines, what are they all? The “highway” column tells you. here’s the most popular types of “highway” in the San Francisco metro extract.

select
  count(*) as cnt, highway
from planet_osm_line
group by highway
order by cnt desc;

  cnt  |        highway        
-------+-----------------------
 29165 | residential
  8897 | service
  6307 | 
  5252 | footway
  1870 | motorway_link
  1841 | tertiary
  1765 | secondary
  1150 | motorway
  1113 | unclassified
  1063 | path
   959 | track
   890 | primary
   712 | steps
   594 | cycleway
   191 | trunk
   150 | primary_link
   116 | pedestrian
    65 | trunk_link
    44 | construction
    39 | road
    35 | secondary_link
    24 | tertiary_link
     3 | footway_unconstructed
     2 | raceway
     2 | proposed
     2 | bridleway
     1 | service; residential
     1 | platform
     1 | crossing
     1 | living_street
     1 | abandoned
(31 rows)

It’s not terrible; things that are correctly classified really are. But “all roads” requires identifying several classes, there’s some ambiguity, and some stuff clearly doesn’t belong there. Still the data exists and is free, so I shouldn’t complain too much!

(Migurski’s High Road is a set of SQL views that present a cleaner schema of roads. But just roads, nothing else.)

I’m playing with OpenStreetMap data in PostGIS, drawing maps in QGIS. No serious cartography, mind you, just trying to get a feel for the data. I’m starting with Mike Migurski’s metro extracts, the .osm.bz2 file for San Francisco

Import that into PostGIS, which gives this schema which describes these map features.

osm2pgsql -d walks --style /usr/share/osm2pgsql/default.style san-francisco.osm.bz2

For the SF metro, there’s 8000 roads, 40,000 polygons, 31,000 points, and 62,000 lines. You can import all these tables directly from pgsql into QGIS and render them for a basic view. The lines contain the street grid; roads contains just the larger highways, etc.

QGIS makes it easy to explore the tables. When you add a new PostGIS layer and choose a table, you can then choose specific fields and sample their values. Exploring around you can find that “leisure=park” is an interesting subset of polygons, for instance.

Roads are terribly complicated, so to simplify I tried using Migurski’s High Road. It creates custom views with names like “planet_osm_line_z14” where just roads relevant at that zoom level are present. Also some fancy stuff to make drawing casings vs. road surfaces easier. z15plus is the big set of all roads (170,000); _big is 24000 roads and _small is the other 144,000 lines (footpaths and such). Unfortunately QGIS can’t use the High Road views; details in this issue I filed.

I was able to use the High Road code as a guide for categorizing road by size, though. The “highway” field is the key one. It’s a messy schema, there’s about 7 synonyms for “street”.

Here’s a map of the walks I’ve taken in my neighborhood, with streets and parks.

I’ve been using RunKeeper to track my walking. I wanted to work with the 40+ GPS tracks they’ve logged for me and didn’t want to download them one at a time. Some quick notes on how to scrape them all out.

1. Log into the website in your browser and go to your Activities page. It has a URL like http://runkeeper.com/user/USERNAME/activity/70000177
2. Save that big HTML page to a file named activities.html
3. Capture the IDs of all your activities from that page with a regexp like this:
   printMatches  ‘link=”/user/USERNAME/activity/([0-9]+)'< activities.html > ids
4. You can now download a GPX for that ID with a URL like http://runkeeper.com/download/activity?activityId=70000177&downloadType=gpx. However, the download requires a URL, at least if your activities are private.
5. Look in your browser for your JSESSIONID cookie. That’ll stand in for a login.
6. Use wget with that cookie. Here’s my little shell script to get them all

cat ids | while read id; do
  wget --no-cookies \
       --header 'Cookie: JSESSIONID=C~2BF2D2156284732CE8D8BDA900000000' \
       -O $id.gpx \
       "http://runkeeper.com/download/activity?activityId="$id"&downloadType=gpx";
done

Set up a PostGIS database with all the data

1. createdb walks
2. psql -d walks -f postgis.sql
3. psql -d walks -f spatial_ref_sys.sql
4. psql -d walks -f postgis_comments.sql
5. for f in *gpx; do echo $f; ogr2ogr -append -f “PostgreSQL” PG:dbname=walks $f; done

ogr2ogr will create a fancy schema for you that contains every field ever seen in a GPX file. For RunKeeper data, the salient things are:

• tracks.wkb_geometry: one row per track, multiline string
• track_points: one row per point with a point, elevation, timestamp, etc.

QGIS did a fine job rendering the tracks table for me. I also pulled in a raster OSM map for a base layer from http://wms.latlon.org/.

Here’s a quicky query to get the length of a track in miles. Need to understand the ST_Transform better, also join to something else to get proper data about each imported track.

select name, ST_Length(ST_Transform(wkb_geometry, 2163)) /1609 as miles from tracks order by name;

A quicky view to simplify the tracks a bit, plus a query that compares the length of the original and simplified views.

create or replace view simple as
select
 ogc_fid,
 ST_Simplify(wkb_geometry,0.00005) as track,
 name
from tracks;

select
  tracks.name,
  ST_Length(ST_Transform(simple.track, 2163)) /1609 as miles,
  ST_Length(ST_Transform(tracks.wkb_geometry, 2163)) /1609 as miles
from simple inner join tracks
on simple.name = tracks.name order by name;

I’ve finally got an editing tool that does enough with Markdown to be useful: Notational Velocity. No WYSIWYG, but the live preview is pretty great. Shockingly there’s no really concise quick reference to all of Multimarkdown; it’s full of ugly complex stuff. The Human Markdown Reference is pretty good, but I made my own.

# Header 1
## Header 2
### Header 3

Text can be _italics_ or **bold** or `fixed width`.

> This is a blockquote.

*  lists are
*  implicitly formed

We can make [basic links](http://www.somebits.com). You can also do this [Stupid link style][1].

[1]: http://www.google.com/

    code samples come
    by indenting four spaces
    
| Features | Advanced? |
- | -
Tables | Yes
Paragraphs | No

----

Multi-markdown link: <http://fletcherpenney.net/multimarkdown>

Header 1

Header 2

Header 3

Text can be italics or bold or fixed width.

This is a blockquote.

• lists are
• implicitly formed

We can make basic links. You can also do this Stupid link style.

code samples come
by indenting four spaces

Multi-markdown link: http://fletcherpenney.net/multimarkdown