sun, 21-feb-2010, 18:45

Back 40

Back lot

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.

tags: app  GPS  iPhone  location  Nika  Piper  property 
fri, 28-aug-2009, 15:48

Peat ponds panorama

Peat ponds panorama

Across from the office is a protected area that was the site of a some sort of resource extraction. It’s a nice spot to take a break at lunchtime, and I enjoy walking Nika and Piper on it in the winter (or around it when it’s not frozen). The hill in the distance is Ester Dome, the high point of the Equinox Marathon that Andrea will be running in a few weeks.

The photo was constructed from three photos using an iPhone app called AutoStitch. I’ve done this process on my Mac using Hugin, and it works well. But AutoStitch can do the whole process right on the iPhone, now has a cropping feature to trim off the extraneous curvature at the edges, and does the whole thing with almost no effort on the user’s part. Very slick.

tags: app  iPhone  panorama  Peat ponds 
Meta Photolog Archives