Monday, September 06, 2010

Loading Lat/Long data into Spatialite

The task: Load spreadsheets with Longitude and Latitude data into a spatial format. Preferably in a way that can be done by people who are not good programmers but can follow directions to use command line tools.

Initial data: Spreadsheets with addresses and Longitude and Latitude. Note: this can be created using the Google Maps API accessed through various programming interfaces.

Tools: Spatialite and SQLite Manager (Firefox plug-in)

Steps:
  1. Save spreadsheet as a comma separated format
  2. Import into SQLite using SQLite Manager plug in
  3. Open Spatialite using command line interface.
  4. Add the geometry to the table using: SELECT AddGeometryColumn('addresstable', 'the_geom', 4326, 'POINT', 2);
  5. Note that 'addresstable' is the table within the database that contains the long/lat data. 'the_geom' will be the new field that contains the spatial data. 4326 represents the SRID for the projection to be used (in this case WGS 84). 'POINT' is the spatial data type. 2 indicates this is in a 2-D space.
  6. Calculate and insert the spatial data using: UPDATE 'addresstable' SET the_geom = GeomFromText('POINT('||Longitude||' '||Latitude||')', 4326);
  7. Longitude and Latitude are the field names containing the longitude and latitude data in the table. The names do not matter. But they order does. And they are separated by a single space.
  8. Method is taken from PostGIS in Action by Obe and Hsu. Spatialite function names are similar to PostGIS function names. In many (but not all) cases, the 'ST_' is removed from the beginning of the function name. Note that for the exceptions, the Spatialite documentation references two function names, one without 'ST_' and one with.
Post a Comment