PerryGeo

Slashgeo pointed me to a very interesting set of projects - SpatiaLite and VirtualShape. They provide a spatial data engine for the sqlite database. Think of it as the PostGIS of SQLite. It looks like this extends sqlite’s spatial capabilities far beyond the sqlite OGR driver.

SpatiaLite provides many of the basic OGC Simple Features functions - transforming geometries between projections, spatial operations of bounding boxes, and some basic functions to disect, analyze and export geometries.

VirtualShape provides the really neat ability to access a shapefile using the SpatiaLite/SQlite interface without having to import a copy - it reads directly off the shapefile by exposing the shapefile and its attributes as a “virtual table”. I can think of a million uses for this. For example, lets say you have a shapefile of US counties and the number of voter in the 2004 election as an attribute in the dbf. You want to find the total voter count in each state:

$ ls -1 counties.*
counties.dbf
counties.prj
counties.shp
counties.shx
$ sqlite3 test.db
sqlite> .load 'SpatiaLite.so'
sqlite> .load 'VirtualShape.so'
sqlite> CREATE virtual table virtual_counties using VirtualShape(counties);
sqlite> select sum(voters) as total_voters, state_name
           from virtual_counties
           group by state_name
           order by total_voters desc;
9830550.0|California
7563055.0|Florida
7346779.0|Texas
...

Now this is fairly straightforward non-spatial SQL but the ability to run it against a shapfile without having to export to an intermediate data format is a very valuable tool.

Links:
When to use SQlite.
A video presentation by Richard Hipp (the author of sqlite).

It just keeps getting easier and easier to get a fully functional open source GIS workstation up and running thanks to Ubuntu. The following instructions will take your vanilla installation of Ubuntu 7.10 and add the following top-notch desktop GIS applications:

• Postgresql/PostGIS : a relational database with vector spatial data handling
• GRASS : A full blown GIS analysis toolset
• Quantum GIS: A user-friendly graphical GIS application
• GDAL, Proj, Geos : Libraries and utilities for processing spatial data
• Mapserver : web mapping program and utilites
• Python bindings for QGIS, mapserver and GDAL
• GPSBabel : for converting between various GPS formats
• R : a high-end statistics package with spatial capabilities
• GMT : the Generic Mapping Tools for automated high-quality map output

While this is not a comprehensive list of open source GIS software, these packages cover most of my needs. If you want to live on the bleeding edge and have to have the absolute latest versions, you’ll be better off installing these from source. But for those of us that want a stable and highly functional GIS workstation with minimal fuss, this is the way to go:

1. Go to System > Administration > Software Sources and make sure the universe and multiverse repositories are turned on. Close the window and the list of available software packages will be refreshed.
2. Open up a terminal (ie the command line) via Applications > Accessories > Terminal and type the following:
   

   sudo apt-get -y install qgis grass qgis-plugin-grass mapserver-bin gdal-bin cgi-mapserver \
   python-qt4 python-sip4 python-gdal python-mapscript gmt gmt-coastline-data \
   r-recommended gpsbabel shapelib libgdal1-1.4.0-grass

   The sudo part indicates that the command will be run as the administrator user, apt-get -y install is the command telling it to install the list of packages and answer yes to any questions that pop up.

3. There is one package that is worth upgrading to the latest and greatest - Quantum GIS. The latest version (0.9) is due out very shortly and has the ability to write plugins using the python programming language. A big plus!

   Download the latest build from http://qgis.org/uploadfiles/testbuilds/qgis0.9.0.debs_ubuntu_gutsy.tar.gz and extract it ( right-click > Extract Here ). In the directory you’ll see 4 .deb files, only 3 of which you’ll need unless you plan on doing any development work.

   Double click libqgis1_0.9.0_i386.deb and you’ll get a message saying an older version is available from directly from ubuntu. We already know this so just close and ignore it. Click Install Package and wait for it to complete then close out.

   Repeat for qgis_0.9.0_i386.deb and qgis-plugin-grass_0.9.0_i386.deb (in that order).

And there we have it, about 15 minutes depending on your internet speed and you’ve installed a high-end GIS workstation built completely on free and open source software.

Jose Gomez-Dans posted a great example of using OGR, Postgis and Matplotlib with Python - OGR, Python y Matplotlib (Spanish only).

Don’t you just love getting CAD data into GIS! I received a .dwg file with study areas delineated as polylines which we needed as polygons for analysis purposes. And it wasn’t just one polyline surrounding each study area … there were hundreds of little line segments which outlined a couple dozen areas (what was this CAD tech thinking?) . Luckily each segment had a name to associate it with the proper area.

I found that ArcMap’s tools for doing this are painfully inadequate so I turned to postgis. After converting the dataset to a shapefile, the solution was simple:

shp2pgsql “study_areas.shp” areas | psql -d gisdata
pgsql2shp -f “study_areas_poly.shp” gisdata \
“SELECT BuildArea(collect(the_geom)) AS the_geom, name
FROM areas
GROUP by name”

Viola… a new shapefile with my proper polygons instead of CAD chicken scratch.