SpatiaLite Provider Code for SharpMap 0.9

I mentioned in an earlier post that I was working on building a SpatiaLite data provider for SharpMap 0.9. That effort was mainly a learning exercise for me as work has already begun on one for SharpMap 2.0. I am making the source code available below. I have unit tested it (thanks mbUnit3 and Gallio) so it should be pretty solid.

I started developing it against SpatiaLite 2.2 and finished working with 2.3 but I only tested it against 2.3. It is written in C#. It is also dependent on the System.Data.SQLite OLEDB provider.

I will probably leave off with this for a bit now. There has been a lot of talk about using SpatiaLite as a transfer format for sharing data in sneaker-net or quasi-sneaker-net. In order to push that a little farther along, I plan to turn my attention to building an exporter/importer tool for use in ArcGIS. My thought is to be able to export an ArcGIS feature class to SpatiaLite and import one back in from it. I wrote a similar tool for PostGIS a while back but the code is ugly and builds an intermediary SQL file. This give me a chance to clean that up. That won’t help with being able to read a SpatiaLite table in ArcGIS and have it behave like a feature class but I think we’ll get around to that sooner than later. I have no timetable for that effort since client work comes first but I’ll post it when it’s done.

Have fun!

[sourcecode language=”csharp”]
// Copyright 2009 – William Dollins
//
// This file is part of SharpMap.
// SharpMap is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// SharpMap is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with SharpMap; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

using SharpMap;
using System.Data.SQLite;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Text;
using System.Data;
using SharpMap.Data;
using SharpMap.Data.Providers;
using SharpMap.Converters.WellKnownBinary;

namespace SharpMap.Data.Providers
{
public class SpatiaLite : IProvider, IDisposable
{
//string conStr = “Data Source=C:\Workspace\test.sqlite;Version=3;”;
public SpatiaLite(string ConnectionStr, string tablename, string geometryColumnName, string OID_ColumnName)
{
//Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, new SQLiteConnection(ConnectionStr)).ExecuteScalar();
this.ConnectionString = ConnectionStr;
this.Table = tablename;
this.GeometryColumn = geometryColumnName; //Name of column to store geometry
this.ObjectIdColumn = OID_ColumnName; //Name of object ID column
}

#region IProvider Members

public System.Collections.ObjectModel.Collection GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection features = new Collection();
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{
//conn.Open();
//Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
string BoxIntersect = GetBoxClause(bbox);

string strSQL = “SELECT AsBinary(” + this.GeometryColumn + “) AS Geom “;
strSQL += “FROM ” + this.Table + ” WHERE “;
strSQL += BoxIntersect;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += ” AND ” + this.DefinitionQuery;

using (SQLiteCommand command = new SQLiteCommand(strSQL, conn))
{
conn.Open();
Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
using (SQLiteDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
SharpMap.Geometries.Geometry geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
if (geom != null)
features.Add(geom);
}
}
}
conn.Close();
}
}
return features;
}

public System.Collections.ObjectModel.Collection GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection objectlist = new Collection();
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{
string strSQL = “SELECT ” + this.ObjectIdColumn + ” “;
strSQL += “FROM ” + this.Table + ” WHERE “;

strSQL += GetBoxClause(bbox);

if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += ” AND ” + this.DefinitionQuery + ” AND “;

using (SQLiteCommand command = new SQLiteCommand(strSQL, conn))
{
conn.Open();
Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
using (SQLiteDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = Convert.ToUInt32(dr[0]);
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return objectlist;
}

public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
SharpMap.Geometries.Geometry geom = null;
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{

string strSQL = “SELECT AsBinary(” + this.GeometryColumn + “) AS Geom FROM ” + this.Table + ” WHERE ” + this.ObjectIdColumn + “='” + oid.ToString() + “‘”;
conn.Open();
Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
using (SQLiteCommand command = new SQLiteCommand(strSQL, conn))
{
using (SQLiteDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
//byte[] b = dr[0] as byte[];
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return geom;
}

public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{

string strSQL = “SELECT *, AsBinary(” + this.GeometryColumn + “) AS sharpmap_tempgeometry “;
strSQL += “FROM ” + this.Table + ” WHERE “;
strSQL += GetOverlapsClause(geom);

if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += ” AND ” + this.DefinitionQuery;

using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(strSQL, conn))
{
conn.Open();
Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != “sharpmap_tempgeometry” && !col.ColumnName.StartsWith(“Envelope_”))
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != “sharpmap_tempgeometry” && !col.ColumnName.StartsWith(“Envelope_”))
fdr[col.ColumnName] = dr[col];
if (dr[“sharpmap_tempgeometry”] != DBNull.Value)
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[“sharpmap_tempgeometry”]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox box, FeatureDataSet ds)
{
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{

string strSQL = “SELECT *, AsBinary(” + this.GeometryColumn + “) AS sharpmap_tempgeometry “;
strSQL += “FROM ” + this.Table + ” WHERE “;
strSQL += GetBoxClause(box);

if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += ” AND ” + this.DefinitionQuery;

using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(strSQL, conn))
{
conn.Open();
Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != “sharpmap_tempgeometry” && !col.ColumnName.StartsWith(“Envelope_”))
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != “sharpmap_tempgeometry” && !col.ColumnName.StartsWith(“Envelope_”))
fdr[col.ColumnName] = dr[col];
if (dr[“sharpmap_tempgeometry”] != DBNull.Value)
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[“sharpmap_tempgeometry”]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}

public int GetFeatureCount()
{
int count = 0;
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{

string strSQL = “SELECT COUNT(*) as numrecs FROM ” + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += ” WHERE ” + this.DefinitionQuery;
using (SQLiteCommand command = new SQLiteCommand(strSQL, conn))
{
conn.Open();
SQLiteDataReader dtr = command.ExecuteReader();
if (dtr[“numrecs”] != null)
{
count = Convert.ToInt32(dtr[“numrecs”]); // (int)command.ExecuteScalar();
}
else
{
count = -1;
}
conn.Close();
}
}
return count;
}

public FeatureDataRow GetFeature(uint RowID)
{
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{

string strSQL = “SELECT *, AsBinary(” + this.GeometryColumn + “) AS sharpmap_tempgeometry FROM ” + this.Table + ” WHERE ” + this.ObjectIdColumn + “='” + RowID.ToString() + “‘”;
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(strSQL, conn))
{
DataSet ds = new DataSet();
conn.Open();
Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != “sharpmap_tempgeometry” && !col.ColumnName.StartsWith(“Envelope_”))
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
if (ds.Tables[0].Rows.Count > 0)
{
System.Data.DataRow dr = ds.Tables[0].Rows[0];
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != “sharpmap_tempgeometry” && !col.ColumnName.StartsWith(“Envelope_”))
fdr[col.ColumnName] = dr[col];
if (dr[“sharpmap_tempgeometry”] != DBNull.Value)
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[“sharpmap_tempgeometry”]);
return fdr;
}
else
return null;

}
else
return null;
}
}
}

public SharpMap.Geometries.BoundingBox GetExtents()
{
//TODO: Update GetExtents
SharpMap.Geometries.BoundingBox box = null;
using (SQLiteConnection conn = new SQLiteConnection(_ConnectionString))
{

//string strSQL = “SELECT Min(minx) AS MinX, Min(miny) AS MinY, Max(maxx) AS MaxX, Max(maxy) AS MaxY FROM ” + this.Table;
string strSQL = string.Format(“SELECT max(MbrMaxY({0})) as maxy, max(MbrMaxX({0})) as maxx, min(MbrMinY({0})) as miny, min(MbrMinX({0})) as minx from {1};”, _GeometryColumn, _Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += ” WHERE ” + this.DefinitionQuery;
using (SQLiteCommand command = new SQLiteCommand(strSQL, conn))
{
conn.Open();
Object retVal = new SQLiteCommand(“SELECT load_extension(‘libspatialite-2.dll’);”, conn).ExecuteScalar();
using (SQLiteDataReader dr = command.ExecuteReader())
if (dr.Read())
{
box = new SharpMap.Geometries.BoundingBox((double)dr[“minx”], (double)dr[“miny”], (double)dr[“maxx”], (double)dr[“maxy”]);
}
conn.Close();
}
return box;
}
}

public string ConnectionID
{
get { return _ConnectionString; }
}

private bool _IsOpen;

///
/// Returns true if the datasource is currently open
///

public bool IsOpen
{
get { return _IsOpen; }
}

///
/// Opens the datasource
///

public void Open()
{
//Don’t really do anything. mssql’s ConnectionPooling takes over here
_IsOpen = true;
}
///
/// Closes the datasource
///

public void Close()
{
//Don’t really do anything. mssql’s ConnectionPooling takes over here
_IsOpen = false;
}

private int _srid = -2;

///
/// Spatial Reference ID
///

public int SRID
{
get { return _srid; }
set { _srid = value; }
}

#endregion

#region IDisposable Members

public void Dispose()
{
this.Dispose();
GC.SuppressFinalize(this);
}

//internal void Dispose(bool disposing)
//{
// if (!disposed)
// {
// if (disposing)
// {
// //Close();
// }
// disposed = true;
// }
//}
#endregion

#region Native Members

private string _ConnectionString;

///
/// Connectionstring
///

public string ConnectionString
{
get { return _ConnectionString; }
set { _ConnectionString = value; }
}

private string _Table;

///
/// Data table name
///

public string Table
{
get { return _Table; }
set { _Table = value; }
}

private string _GeometryColumn;

///
/// Name of geometry column
///

public string GeometryColumn
{
get { return _GeometryColumn; }
set { _GeometryColumn = value; }
}

private string _ObjectIdColumn;

///
/// Name of column that contains the Object ID
///

public string ObjectIdColumn
{
get { return _ObjectIdColumn; }
set { _ObjectIdColumn = value; }
}

private string GetBoxClause(SharpMap.Geometries.BoundingBox bbox)
{
//TODO:: make a diagonal line from bbox and convert to WKT. MBR of line will be identical to bbox.
string wkt = SharpMap.Converters.WellKnownText.GeometryToWKT.Write(LineFromBbox(bbox));
string retval = “MBRIntersects(GeomFromText(‘” + wkt + “‘),” + _GeometryColumn + “)=1”;
return retval;
//return String.Format(SharpMap.Map.numberFormat_EnUS,
// “(minx < {0} AND maxx > {1} AND miny < {2} AND maxy > {3})”,
// bbox.Max.X, bbox.Min.X, bbox.Max.Y, bbox.Min.Y);
}

private SharpMap.Geometries.IGeometry LineFromBbox(SharpMap.Geometries.BoundingBox bbox)
{
Collection PointColl = new Collection();
PointColl.Add(bbox.Min);
PointColl.Add(bbox.Max);

return (SharpMap.Geometries.IGeometry)new SharpMap.Geometries.LineString(PointColl);
}

public string GetOverlapsClause(SharpMap.Geometries.Geometry geom)
{
string wkt = SharpMap.Converters.WellKnownText.GeometryToWKT.Write((SharpMap.Geometries.IGeometry)geom);
string retval = “Intersects(GeomFromText(‘” + wkt + “‘),” + _GeometryColumn + “)=1”;
return retval;
//return String.Format(SharpMap.Map.numberFormat_EnUS,
// “(minx < {0} AND maxx > {1} AND miny < {2} AND maxy > {3})”,
// bbox.Max.X, bbox.Min.X, bbox.Max.Y, bbox.Min.Y);
}

private string _defintionQuery;

///
/// Definition query used for limiting dataset
///

public string DefinitionQuery
{
get { return _defintionQuery; }
set { _defintionQuery = value; }
}

#endregion

}
}
[/sourcecode]

  • Bill,
    This sounds great. I’ll add it to my SpatiaLite tutorial links. So far of what I’ve used of SpatiaLite, I like it a lot.

    http://www.bostongis.com/PrinterFriendly.aspx?content_name=spatialite_tut01

  • Thank you, Regina. I hope you find it useful.

    I’ve been very impressed with SpatiaLite so far, as well. I like the GEOS functions that extend it beyond just MBR relationships. SQLite seems to be a pretty robust engine for its footprint and SpatiaLite extends it quite nicely.

    Next up for me is tackling interop with ArcGIS.

  • FObermaier

    Bill,
    I hope there is nothing -terribly- wrong with the SpatiaLite provider in the trunk of SharpMaps repository on googlecode.

    Thanks for the 0.9 Version.
    Cheers
    FObermaier

  • Glad to put it out there. One of these days, I’ll actually get around to rolling up to v2. Time is my enemy. The code looks pretty nice, though. You guys are putting a lot of thought/work into it.

    Thanks for stopping by.

  • agelos

    created a demo vb.net project with sharpmap v0.9 compiled with
    spatiallite provider.
    I also tested the functionality to load ( .loadshp) and save shape
    ( .dumpshp) files to sqllite. in .net i had an error executing these
    command and had to create a batch file and execute it using the
    shell .
    I want now to save some nts geometries to spatialite and the write the
    shp (using dumpshp) file.

    I would now probably have to write sql scripts to add the nts
    geometries to the db using wkt i suppose.

    Please advice me if you have time 🙂

    Anyone interested will be happy with this demo just to start and test
    things.
    Download from here:

    http://energy.chemeng.ntua.gr/SharpmapAndSQLiteSpatial.rar

    Regards,
    Agelos

    • BTW, I believe Agelos’ issue was ultimately resolved via the SpatiaLite Google Group.

  • If someone else has not started it, I might take a shot at a direct reader for ArcGIS Desktop / Server for SpatiaLite. I’ve done it before for another third-party spatial data store and it worked out pretty well.

    • It’s probably something that could be done pretty easily from the zigGIS code base.

  • Kev

    Thanks for posting the code. I have recently made use of this and added support for supporting a spatial index

    http://epsg27700.blogspot.com/2009/08/adventures-with-spatialite.html

    • Hey, nice pickup! Thanks for the head’s up on the modification.

      Bill

  • Amjad

    Bill,
    Thanks for the great work, i hope u can help me, i am new to the gis programming… i need to import data from an AXF file this is what the arcgis guys provide for exchange, i need to display the data on the map… can you give me any hints on where to start.
    Thanks