Learning some GIS

Today’s hacking project is getting up to speed with modern GIS. Specifically, PostGIS (Postgres with spatial features) as a database, QGis as a graphical frontend for viewing graphics data, and GeoDjango for building webapps.

QGis is awesome: you can quickly add layers, including layers that come from PostGIS. Loaded layers are on the tray to the left, you can right click them to set display options, enable a query to filter what’s displayed, etc. There’s also an edit mode for editing geometry.

PostGIS is encouraging. Some notes:

After finishing all the setup I loaded the cities and counties file referenced in that first tutorial (at USGS) It was easy to load them up into QGis for display. “Show me county outlines, and the names and locations of all cities with more than 100,000 residents”.

Playing in PostGIS, here’s some interesting queries:

select county, ST_Area(wkb_geometry) as area from us_counties
where state = 'CA'  order by area desc limit 5;
Biggest county. Units of Area are.. degrees?
                       county                       |       area
----------------------------------------------------+------------------
 San Bernardino County                              | 5.13534296766738
 Inyo County                                        | 2.66716021408502
 Kern County                                        | 2.09407653001836
 Riverside County                                   | 1.84319135357509
 Siskiyou County                                    | 1.77534424474288

select county, ST_AsText(ST_Simplify(wkb_geometry, 0.1))
  from us_counties where state = 'CA'  limit 1;
(returns a simplified polygon of the county outline)

\d us_counties
shows all the columns. The interesting one is
  wkb_geometry | geometry
Geometry is a polymorphic type, in the case of this shapefile it's a Polygon. The "WKB"
name is just a descriptive, but implies the import source was in "well known binary" format. 

select name, ST_X(wkb_geometry), ST_Y(wkb_geometry)
  from us_cities where name like 'San %' and state = 'CA';
 San Anselmo                                      | -122.561653137207 | 37.9746437072754
 San Rafael                                       | -122.531089782715 | 37.9735336303711
 San Quentin                                      | -122.487464904785 | 37.9412689208984

select name, state,
  floor(ST_Distance_Sphere(wkb_geometry, ST_GeomFromText('POINT(-122.4522 37.7555)'))) as distance_from_SF
  from us_cities where state='CA' order by distance_from_sf   limit 5;

                       name                       | state | distance_from_sf
--------------------------------------------------+-------+------------------
 San Francisco                                    | CA    |             3602
 Daly City                                        | CA    |             5596
 Broadmoor                                        | CA    |             8207
 Colma                                            | CA    |             8767
 Brisbane                                         | CA    |             9495


select county from us_counties where
  ST_Contains(wkb_geometry, ST_GeomFromText('POINT(-122.4522 37.7555)'));

shows which county contains an arbitrary lat/lon
select county, ST_Area(wkb_geometry) as area from us_counties where state = ‘CA’  order by area desc limit 5;
county                       |       area
—————————————————-+——————
San Bernardino County                              | 5.13534296766738
Inyo County                                        | 2.66716021408502
Kern County                                        | 2.09407653001836
Riverside County                                   | 1.84319135357509
Siskiyou County                                    | 1.77534424474288
select county, ST_Area(wkb_geometry) as area from us_counties where state = ‘CA’  order by area desc limit 5;
county                       |       area
—————————————————-+——————
San Bernardino County                              | 5.13534296766738
Inyo County                                        | 2.66716021408502
Kern County                                        | 2.09407653001836
Riverside County                                   | 1.84319135357509
Siskiyou County                                    | 1.77534424474288