Using GIS Data

From PetalumaOpen
Jump to: navigation, search

User:DanLyke has been playing around with data from the Sonoma County GIS portal.

At a first pass, here are the procedures to create a PostGIS database. First, create a PostgreSQL database:

createdb sonoma

Next, enable PostGIS extensions (the paths here are from Ubuntu Linux release 10.10):

createlang plpgsql sonoma
psql -d sonoma -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d sonoma -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql
psql -d sonoma -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql 

Finally, populate the database with code from the .shp files. The .shp files from the Sonoma Count GIS portal all appear to be in the "NAD83 / California zone 2 (ftUS)" projection which has an SRID of EPSG:2226 :

mkdir -p sql
for a in *.shp ; do shp2pgsql -s 2226 `basename $a .shp` > sql/`basename $a .shp`.sql ; done
for a in sql/*.sql ; do psql -d sonoma -f $a ; done

You can then do queries against this data. If you have Mapnik installed, you can pass a query to a datasource to show all subdivisions that have "Petaluma" in their name:

(SELECT the_geom FROM par_parcels WHERE subdivisio ILIKE '%PETALUMA%') AS the_geom

Which could give you a map like:

subdivisions containing "PETALUMA" in their name are highlighted in red

A more interesting query might be all parcels within 50 meters of a stream:

SELECT DISTINCT(par_parcels.the_geom) FROM par_parcels , hyd_usgsstrm 
   WHERE ST_DWithin(par_parcels.the_geom, hyd_usgsstrm.the_geom, 50)

SampleMapnikOutput-AllParcelsWithin50MetersOfAUSGSStream.png

Or 1 meter of a stream:

SELECT DISTINCT(par_parcels.the_geom) FROM par_parcels , hyd_usgsstrm 
   WHERE ST_DWithin(par_parcels.the_geom, hyd_usgsstrm.the_geom, 1)

SampleMapnikOutput-AllParcelsWithin1MeterOfAUSGSStream.png

Personal tools