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"
   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

DOCS PREVIEW