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

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.

Loading GPS Data into PostGIS


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:

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

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

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

Custom Item Writer to Insert PostGIS Points

The data and the code is available from github:

In my next post, I’ll take a look at the data with a PostGIS layer in QGIS 1.9.