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);
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:
I was not able to format the SQL correctly in the item writer bean in-line, so I implemented an ItemWriter class in Java.
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.