SpatiaLite

SpatiaLite spatially enables the file-based SQLite database. For more information see the SpatiaLite description page.

File listing

Similar to other database formats, the .sqlite file (.db is another common file extension for this) consists of several tables. The geometry is held in a BLOB table column.

Data Access / Connection Method

Spatialite access is available through OGR. See the OGR driver page for specific driver information. The driver is available in GDAL/OGR version 1.7.0 or later.

OGR uses the names of spatial tables within the SpatiaLite database (tables with a geometry column that are registered in the geometry_columns table) as layers.

The CONNECTION parameter must include the sqlite extension, and the DATA parameter should be the name of the spatial table (or OGR layer).

CONNECTIONTYPE OGR
CONNECTION "spatialite_db.sqlite"
DATA "layername"

OGRINFO Examples

First you should make sure that your GDAL/OGR build contains the spatialite «SQLite» driver, by using the “–formats” command:

>ogrinfo --formats

   Loaded OGR Format Drivers:
   ...
   -> "GMT" (read/write)
   -> "SQLite" (read/write)
   -> "ODBC" (read/write)
   ...

If you don’t have the driver, you might want to try the MS4W or OSGeo4W packages, which include the driver.

Once you have confirmed that you have the SQLite driver you are ready to try an ogrinfo command on your database to get a list of spatial tables:

>ogrinfo counties.sqlite

   INFO: Open of `counties.sqlite'
   using driver `SQLite' successful.
   1: mn_counties (Polygon)

Now use ogrinfo to get information on the structure of the spatial table:

>ogrinfo counties.sqlite county -summary

   INFO: Open of `counties.sqlite'
   using driver `SQLite' successful.

   Layer name: mn_counties
   Geometry: Polygon
   Feature Count: 87
   Extent: (189783.560000, 4816309.330000) - (761653.524114, 5472346.500000)
   Layer SRS WKT:
   PROJCS["NAD83 / UTM zone 15N",
       GEOGCS["NAD83",
           DATUM["North_American_Datum_1983",
               SPHEROID["GRS 1980",6378137,298.257222101,
               AUTHORITY["EPSG","7019"]],
               TOWGS84[0,0,0,0,0,0,0],
               AUTHORITY["EPSG","6269"]],
           PRIMEM["Greenwich",0,
              AUTHORITY["EPSG","8901"]],
           UNIT["degree",0.0174532925199433,
              AUTHORITY["EPSG","9122"]],
           AUTHORITY["EPSG","4269"]],
      UNIT["metre",1,
          AUTHORITY["EPSG","9001"]],
      PROJECTION["Transverse_Mercator"],
      PARAMETER["latitude_of_origin",0],
      PARAMETER["central_meridian",-93],
      PARAMETER["scale_factor",0.9996],
      PARAMETER["false_easting",500000],
      PARAMETER["false_northing",0],
      AUTHORITY["EPSG","26915"],
      AXIS["Easting",EAST],
      AXIS["Northing",NORTH]]
  FID Column = PK_UID
  Geometry Column = Geometry
  AREA: Real (0.0)
  PERIMETER: Real (0.0)
  COUNTY_ID: Integer (0.0)
  FIPS: String (0.0)
   ...

Mapfile Example

Standard connection

LAYER
   NAME "my_counties_layer"
   TYPE POLYGON
   CONNECTIONTYPE ogr
   CONNECTION "counties.sqlite"
   DATA "mn_counties"
   EXTENT 189783.560000 4816309.330000 761653.524114 5472346.500000 # for maximum performance
   PROCESSING "CLOSE_CONNECTION=DEFER" # for maximum performance
   STATUS ON
   CLASS
     NAME "mncounties"
     STYLE
       COLOR 255 255 120
     END
   END
END

Connection utilizing SQL syntax

LAYER
   NAME "my_counties_layer"
   TYPE POLYGON
   CONNECTIONTYPE OGR
   CONNECTION "counties.sqlite"
   DATA "select geometry from mn_counties"
   PROCESSING "CLOSE_CONNECTION=DEFER" # for maximum performance
   STATUS ON
   CLASS
     NAME "mncounties"
     STYLE
       COLOR 255 255 120
     END
   END
END

Connection utilizing joined table for additional attributes

LAYER
   NAME "my_counties_layer"
   TYPE POLYGON
   CONNECTIONTYPE OGR
   CONNECTION "counties.sqlite"
   DATA "SELECT mn.geometry, c.fips FROM mn_counties mn inner
         join county_data c on mn.county_id = c.county_id'
   PROCESSING "CLOSE_CONNECTION=DEFER" # for maximum performance
   STATUS ON
   CLASS
     NAME "mncounties"
     STYLE
       COLOR 255 255 120
     END
   END
END

Standard Connection with a filter

LAYER
   NAME "my_counties_layer"
   TYPE POLYGON
   CONNECTIONTYPE OGR
   CONNECTION "counties.sqlite"
   DATA "mn_counties"
   FILTER ('[fips]' = '27031')
   PROCESSING "CLOSE_CONNECTION=DEFER" # for maximum performance
   STATUS ON
   CLASS
     NAME "mncounties"
     STYLE
       COLOR 255 255 120
     END
   END
END

Filter utilizing SQL syntax

LAYER
   NAME "my_counties_layer"
   TYPE POLYGON
   CONNECTIONTYPE OGR
   CONNECTION "counties.sqlite"
   DATA "select geometry from mn_counties where fips = '27031"
   PROCESSING "CLOSE_CONNECTION=DEFER" # for maximum performance
   STATUS ON
   CLASS
     NAME "mncounties"
     STYLE
       COLOR 255 255 120
     END
   END
END