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 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"
   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"
   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'
   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')
   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"
   STATUS ON
   CLASS
     NAME "mncounties"
     STYLE
       COLOR 255 255 120
     END
   END
END