From OpenStreetMap Wiki
Jump to navigation Jump to search

SQLite (sqlite.org) is a local Database Management System. Data is stored in local file, the format of which is standardised, and can be shared across different technical architectures. Being a general purpose relation database, Sqlite can be used to store geodata e.g. simple latitude & longitude columns, however more advanced spatial indexing and functions are available via an extension called SpatiaLite [1].

Converting OSM data into an SQLite DB

OSMLib can convert to and from SQLite and OSM XML. See osmlib-sqlite docs

HOT Exports web tool will generate an SQLite DB as one of several exported formats

SQLite files and QGIS

Often, it's more useful to store geodata in small SQLite. It doesn't have geo-functions or a spatial index, like SpatiaLite does, but this is often not needed at all. SQLite gives a very lightweight way to store such information.

QGIS can produce both kinds of DBs, but it doesn't add the correct CRS/SRS (information about the coordinate system being used). That might be a bug, but doesn't work up to version 2.18.3 and maybe later. The problem results into QGIS complaining about a missing CRS/SRS on re-opening at file and forces it to be EPSG:4326. That's good for OpenStreetMap data, but not really usefull for anything else.

To add the missing CRS/SRS, keep in mind to store metadata in QGIS and select "SQLite" as export format (not "SpatiaLite"!). In the following example, I'll set SRID to 3035 used in Europe for maps about statistics:

  1. Open the SQLite file, e.g., with sqlite3: sqlite3 database.sqlite
  2. Add the information about spatial reference to the geometry column (table is "demo_data"):
UPDATE geometry_columns SET srid = 3035 WHERE table = 'demo_data';
  1. Add the definition of the spatial reference:
INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext) VALUES (3035, 'epsg', 3035, 'PROJCS["ETRS89 / ETRS-LAEA",GEOGCS["ETRS89",DATUM["European_Terrestrial_Reference_System_1989",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","6258"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4258"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Lambert_Azimuthal_Equal_Area"],PARAMETER["latitude_of_center",52],PARAMETER["longitude_of_center",10],PARAMETER["false_easting",4321000],PARAMETER["false_northing",3210000],AUTHORITY["EPSG","3035"],AXIS["X",EAST],AXIS["Y",NORTH]]');

Now you're done. You can look up the string for spatial reference, f.e. on this website: http://spatialreference.org/ref/epsg/3035/postgis/ But this is important:

  1. Remove "proj4text," from the bracketed part and remove "+proj=..." from the second one.
  2. Make the first number of the second bracketed part be equal to your EPSG-code, f.e. "3035".

Tools using SQLite

Microcosm is a map API server that uses Sqlite and the R*tree module.

SQLite for tile data

MBTiles is based on SQLite. It's a format for storing raster tile images

See also