HELP WANTED: QGIS Mentor
My goal was simple, generate a heat map in QGIS that looked similar to the heat map that I generated using Google Fusion Tables with just a couple of clicks, or similar to what was eventually implemented on Garmin Connect.
This is what my test data set of 350,000 GPS points looks like in QGIS 1.9.0 on a PostGIS layer:
PostGIS Layer in Quantum GIS 1.9.0
Several attempts resulted in either no visible heat map (above) or a large blobbish, weather map looking effect, not what I wanted.
I talked to the cartographer who took my Google Fusion Table prototype from concept to production. His process bears little resemblances to my prototype. Instead of a loose collection of GPS points, he extracted running and cycling activity polylines. Instead of using a heat map plugin he wrote custom C++ code to convert the vector data into raster data.
So, I’ll switch from loose points to polylines too. Oracle Spatial and Graph has a SDO_UTIL.FROM_GML311GEOMETRY function to export the polylines in GML format. Likewise, PostGIS has a ST_GeomFromGML to import GML data into a geometry. I’ll work on creating a data set of 20,000 – 30,000 polylines.
I sure could use a QGIS mentor to answer my basic questions and to help me avoid making wrong turns. It might be time to reach out some cartographers in other departments at work. I also plan on reading “Learning QGIS 2.0,” by Anita Grase, when that comes out later this month. I guess Anita can be my virtual mentor.
The next area I will explore is heat maps using 8 Mb of GPS data collected from fitness devices near Olathe, KS. My data source is a CSV file with three columns: latitude, longitude, and intensity. Latitude and longitude were rounded to four decimal places and I kept track of how many rounded GPS locations fell into each rounded latitude/longitude cell. Intensity is a decimal number between zero and one reflecting the number of hits for each latitude/longitude cell.
The first step was to create a table with a column for intensity and a spatially enabled column to hold the rounded GPS location:
CREATE TABLE gps_points (INTENSITY Real);
SELECT AddGeometryColumn('','gps_points','LOCATION', 4326, 'POINT', 2);
CREATE INDEX gps_points_location_idx ON gps_points USING GIST (location);
ALTER TABLE gps_points ADD CONSTRAINT gps_points_location_uniq UNIQUE (location);
The GPS_POINTS Table with Index and Constraints
Next, I set-up a Spring Batch project to import the data. The reason that I took the time to create a Spring Batch project is so that I can add more item writers to load the data set into other spatial repositories like Oracle Spatial and Graph or MySQL.
Here is the item reader and item writer definition from my Spring context:
Spring Batch Item Reader and Item Writer Definitions
I was not able to format the SQL correctly in the item writer bean in-line, so I implemented an ItemWriter class in Java.
Custom Item Writer to Insert PostGIS Points
The data and the code is available from github: https://github.com/smitchell/SpatialLoader
In my next post, I’ll take a look at the data with a PostGIS layer in QGIS 1.9.
To prepare for the TileMill Crash Course I needed to install a GIS, (in addition to Oracle Spatial and Graph which I previously installed). PostGIS was an easy choice for Ubuntu 12.04 since it is available in the Ubuntu Software Center.
First, I installed PostgreSQL and pgAdmin III.
Initially, I install PostGIS from the Ubuntu Software Center, but I was unable to create the PostGIS or postgis_topology extension. In the end, I removed it and then followed the instructions found in How to install PostGIS 2.0 on Ubuntu 12.04 LTS (precise) from source. Following those instructions you will do the following:
- Install prerequisites, including gdal, if you haven’t already installed it.
- Build GEOS 3.3.x from source.
- Build PostGIS from source.
- Spatially enable your database.
I created a database and added the postgis extensions to it, and then used the function postGIS_full_version() to verify the extension.
$ createdb exploringspatial
$ psql -U postgres exploringspatial
Password for user postgres:
Type "help" for help.
exploringspatial=# CREATE EXTENSION postgis;
exploringspatial=# CREATE EXTENSION postgis_topology;
exploringspatial=# SELECT postGIS_full_version();
POSTGIS=”2.0.3 r11128″ GEOS=”3.3.8-CAPI-1.7.8″ PROJ=”Rel. 4.8.0, 6 March 2012″ GDAL=”GDAL 1.11dev, released 2013/04/13″ LIBXML=”2.8.0″ LIBJSON=”UNKNOWN” TOPOLOGY RASTER
Finally, launch the Shape File to PostGIS Importer GUI to verify it is installed:
The Shape File to PostGIS Importer GUI launched as expected, so now I can get back to the TileMill crash course:
$ sudo xhost +localhost
localhost being added to access control list
$ shp2pgsql-gui &
Next, I’ll follow the Tilemill crash course and play with heat maps.