Loading GPS Data into PostGIS

postgis-logo

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

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: 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s