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)

  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.


Carlos Kochhann said...

what about the other way around?
I want to get Lat/Lon from a gemetric point

LugerLA said...


Well, you need a mapping function of some kind that maps the point to the lat/long. I think there are some GIS programs that take a TIFF file (or other imagery file) and allow you to superimpose it on top of a GIS through the identification of a set of points in the image, then it will map the rest of the image to the GIS projection (via stretching, warping, etc.) This is usually done with people using satellite imagery with GIS (from what I know, which is not much)