metachronistic

Today I went for a walk on the Creek with Nika and Piper and it occurred to me that the iPhone ought to be able to tell me not only where I am (which it does quite well with the Google-driven map application), but on whose property I’m walking through. It took me a little time, but I now have a webapp (http://swingleydev.com/gis/loc.html) that can do this (don’t bother clicking on the link unless you’re on an iPhone or other GPS-enabled device).

The result is a pair of pages. The first one shows you your current location, speed, and heading. With a press of a button (and a delay while the database finds the property owner) you can see the Borough information on the property you’re currently inside (assuming you’re in the Fairbanks North Star Borough—this page doesn’t do non-Borough residents any good).

Here’s how I set it up:

My web hosting provider doesn’t have a new enough version of PostgreSQL to run PostGIS, which means I need to use the spatially-enabled version of SQLite3, called Spatialite. To get the parcel database from the shapefile to Spatialite requires the following steps. Some parcels are eliminated in this process, but it’s a small fraction of the parcels in the database:

Download the parcels shapefile:

$ wget ftp://co.fairbanks.ak.us/GIS/tax_parcels.zip

Unzip it and insert it into a spatially enabled PostgreSQL database using ogr2ogr. Crash the process immediately:

$ unzip tax_parcels.zip
$ ogr2ogr -f "PostgreSQL"
    -t_srs EPSG:4326
    -overwrite -skipfailures
    PG:"dbname='test'"
    tax_parcels.shp

Fix the column definitions:

sql> DELETE FROM tax_parcels;
sql> ALTER TABLE tax_parcels ALTER COLUMN
    sqft_calc TYPE numeric (22,12);

Re-import the data:

$ ogr2ogr -f "PostgreSQL"
    -t_srs EPSG:4326
    -append -skipfailures
    PG:"dbname='test'"
    tax_parcels.shp

Convert the geometry column to MULTIPOLYGON:

sql> ALTER TABLE tax_parcels DROP CONSTRAINT "enforce_geotype_wkb_geometry";
sql> ALTER TABLE tax_parcels ADD CONSTRAINT "enforce_geotype_wkb_geometry"
     CHECK (geometrytype(wkb_geometry) = 'MULTIPOLYGON'::text OR
            wkb_geometry IS NULL OR
            geometrytype(wkb_geometry) = 'POLYGON'::text);
sql> UPDATE tax_parcels SET wkb_geometry = ST_Multi(wkb_geometry);
sql> ALTER TABLE tax_parcels DROP CONSTRAINT "enforce_geotype_wkb_geometry";
sql> ALTER TABLE tax_parcels ADD CONSTRAINT "enforce_geotype_wkb_geometry"
     CHECK (geometrytype(wkb_geometry) = 'MULTIPOLYGON'::text OR
            wkb_geometry IS NULL);
sql> UPDATE geometry_columns SET type='MULTIPOLYGON'
     WHERE f_table_name='tax_parcels' AND f_geometry_column='wkb_geometry';

Re-import the data (there will be thousands of errors, but this insert should add the MULTIPOLYGON rows that weren’t inserted the first time around):

$ ogr2ogr -f "PostgreSQL"
    -t_srs EPSG:4326
    -append -skipfailures
    PG:"dbname='test'"
    tax_parcels.shp

Get rid of the illegal polygons:

sql> DELETE FROM tax_parcels WHERE NOT ST_IsValid(wkb_geometry);

Convert to spatialite:

$ ogr2ogr -f "SQLite"
    tax_parcels.sqlite
    PG:"dbname='test'"
    -dsco SPATIALITE=YES

With the data in the proper format, I built a mobile web page that can pull location information from the mobile device, display it on the screen, and pass this information to a page that finds and displays the parcel information from the Spatialite database. JavaScript handles gathering the location information, but because I don’t control the web server, I had to write a CGI script to pass a query to spatialite and format the results as a list. This CGI is called by the main page from a <form> element. The spatialite query looks like this:

sqlite> SELECT street_add, owner_firs, owner_last,
           owner1, owner2, owner3, mail_add, ci_st_zip,
           round(acres_calc, 1) as acres, ’$’ || total as total,
           ’$’ || land as land, ’$’ || improvemen as improvements,
           pan, sub, block, lot, road_water, lot_size, units,
           neighborho, primary_us, tax_status, tax_year, mill_rate,
           business, year_built, situs_numb, situs_name
        FROM tax_parcels
        WHERE Intersects(GEOMETRY, SetSRID(MakePoint(lon, lat), 4326));

Again, implementing this would be a lot easier if I could install PostGIS on the server and use PHP to access the data directly. Because I can’t, spatialite and CGI do the trick.

Update: I added a few more steps to convert the initially imported POLYGON layers to MULTIPOLYGON, which then allows us to include the MULTIPOLYGON rows from the shapefile.

Today I made a bow from a board. I started with a 1×3 piece of red oak, and ended up with the bow seen in silhouette in the photo on the right. It’s an American flatbow, similar to what many Native American tribes (including some Inuit) used. In mine, the upper and lower limbs don’t have quite the same shape and it has a lower draw weight than I had planned on, but I think it was a good first attempt at bow-making.

The hardest part is finding a board that has straight grain lines running all the way down the face of the board. You cut and smooth the sides first (it’s about 1½” wide at the handle and gently tapers to ½” at the tips) then begin tapering the limbs (full thickness at the handle, gently tapering to ½” at the tips). After each thinning the bow is drawn slightly further up a tillering board (a piece of wood with notches cut into it to hold the string) and the shape is evaluated to make sure it’s bending the way you want. My mistake was in thinning the wrong limb too much without making similar changes to the other half of the bow. Once I realized this, I had to remove a bunch of material off the now-thicker limb and wound up with a bow that is easier to draw than intended. Since I haven’t actually shot an arrow from a bow since high school, a light drawing bow is probably a good idea until I’m ready for something more powerful. This one is reasonably easy for me to pull, and shoots sticks very smoothly.

Tools used: I used a rip saw to cut the board to rough dimension, smoothed the saw cuts with a wooden jack plane, made the initial taper with a drawknife, and did the majority of the remaining adjustments with a coffin smoother, an adjustable mouth block plane and a handled cabinet scraper. Probably should have used the scraper more and the hand planes less. Volumes 1 and 4 of The Traditional Bowyer’s Bible were invaluable, and I think I could have used the other two volumes too, if I’d had them. There’s a ton of information in those books for both the beginner, and advanced bowyer. In addition to volumes 2 and 3, I need some real arrows, a target, and more wood for more bows!