ManagedSpatiaLite and SqLite in Trunk 101083

Topics: SharpMap v0.9 / v1.x, WinForms Controls
Jan 8, 2013 at 8:29 AM
Edited Jan 8, 2013 at 11:14 AM


Because I couldn't get the SpatiaLite data working in 0.9-Trunk-2012.04.17,

I downloaded the sourcecode (101083) and compiled all in the directory Trunk (except Examples\WinFormSamples because of missing code files);

created a new project and referenced the SharpMap, SharpMapUI, SharpMap.Extensions and all the other stuff (GeoAPI, BruTile ......) from the compiled sourcecode. The only thing which didn't work was the System.Data.SQLite and System.Data.SQLite.Linq in SharpMap.Extensions\bin\Release, which was an older version (1.0.82) but 1.0.83 was needed, so I got it with NuGet;

added my SpatiaLite data, created with spatialite_gui from a shapefile, with this code

    string connectionString = @"Data Source=D:\data\MyDB.sqlite;Version=3;";
    ManagedSpatiaLite spatiaLite = new ManagedSpatiaLite(connectionString, "MyTable""geometry""PK_UID");
    spatiaLite.SRID = 3812;
    spatiaLite.UseSpatiaLiteIndex = true;
    VectorLayer vectorLayer = new VectorLayer("roadsegments");
    vectorLayer.DataSource = spatiaLite;
    vectorLayer.Enabled = true;

but the map stays blank, no segments are shown, although _spatiaLite.GetFeatureCount() gives me the correct number of features.

But when I ask FeatureDataRow fdr = _spatiaLite.GetFeature(125) it gives me this error message:

System.ApplicationException: Unsupported geom type!
   at SharpMap.Converters.SpatiaLite.GeometryFromSpatiaLite.Parse(Byte[] spatialliteGeom, IGeometryFactory factory) in D:\DotNetZone\sharpmap-101083_source\Trunk\SharpMap\Converters\SpatiaLite\GeometryFromSpatiaLite.cs:line 60
   at SharpMap.Data.Providers.ManagedSpatiaLite.GetFeature(UInt32 rowId) in D:\DotNetZone\sharpmap-101083_source\Trunk\SharpMap.Extensions\Data\Providers\ManagedSpatiaLite.cs:line 511
   at SQLiteSharpMap.mainForm.openDatabase32ToolStripMenuItem_Click(Object sender, EventArgs e) in D:\DotNetZone\CSharp.Net\SQLiteSharpMap\SQLiteSharpMap\mainForm.cs:line 144

When I use SqLite as provider

    SqlLite sqLite = new SqlLite(connectionString, "MyTable""geometry""PK_UID");
    sqLite.SRID = 3812;

it gives this error message in mapBox.Map.ZoomToBox(vectorLayer.Envelope):

System.Data.SQLite.SQLiteException (0x80004005): SQL logic error or missing database
no such column: minx
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader()
   at SharpMap.Data.Providers.SqlLite.GetExtents() in D:\DotNetZone\sharpmap-101083_source\Trunk\SharpMap.Extensions\Data\Providers\SqlLite.cs:line 322
   at SharpMap.Layers.VectorLayer.get_Envelope() in D:\DotNetZone\sharpmap-101083_source\Trunk\SharpMap\Layers\VectorLayer.cs:line 151
   at SQLiteSharpMap.mainForm.openDatabase32ToolStripMenuItem_Click(Object sender, EventArgs e) in D:\DotNetZone\CSharp.Net\SQLiteSharpMap\SQLiteSharpMap\mainForm.cs:line 141

What am I doing wrong? The data is visble in Quantum GIS.

Thanks in advance,


Jan 14, 2013 at 12:28 PM

Nobody any clue?

Have a nice day,


Jan 14, 2013 at 12:30 PM

What kind of data are you trying to display? (What geometry type)?

Jan 14, 2013 at 12:35 PM

A dataset with (poly)lines in a SQLite/SpatiaLite database (.sqlite) created with spatialite_gui.exe

Jan 14, 2013 at 2:48 PM

Which version of spatialite_gui?

You are not using the compressed geometries?

Which dimension model do the geometries have (XY)

Jan 15, 2013 at 7:07 AM

Spatialite version 1.6.0, all extensions enabled.

Geometry Spatial Metadata:

f_table_name f_geometry_column geometry_type coord_dimension srid auth_name auth_srid ref_sys_name
roadsegments geometry 3002 4 3812 epsg 3812 ETRS89 / Belgian Lambert 2008

Geometry type is 3002 : LINESTRINGZM; R*Tree spatial index used, but when I remove or rebuild the spatial index, the error remains. All geometries are valid.

Jan 15, 2013 at 12:17 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jan 16, 2013 at 8:29 AM

Thanks so far, but how do I apply the patch? I do not have a 'working copy'. This is what TortoiseMerge says when I choose 'Apply patch' in Explorer and are urged to select the proper directory:

'D:\MyDirectory\Trunk' is not a working copy

Specifying the full path in 'Index' doesn't help either. I could change the source manually, but there must be another way.

Jan 16, 2013 at 8:30 AM


I'm applying it right now... wait a couple of minutes and it will be in trunk....


Best Regards,


Jan 16, 2013 at 9:21 AM

Please try the latest code in trunk if that works for you now...

Jan 16, 2013 at 9:48 AM

Many thanks Peter (and FObermaier) ! It works now, but is not a speed champion: with the OSM as background layer, a zoom from OSM extents to the extents of my data (30k lines) takes several seconds. But that's less important ;-)

Kind greetings,


Jan 16, 2013 at 10:50 AM

What is causing the delay, the Tile- or the VectorLayer?

Jan 16, 2013 at 12:06 PM
Edited Jan 16, 2013 at 12:09 PM

Starting the application with empty MapBox:

  • loading vectorlayer = 10 seconds
  • adding tilelayer = 10 seconds
  • zoom to tilelayer extents = almost immediately
  • now zoom to vectorlayer extends = 10 seconds
  • zoom to detail in vectorlayer = about 1 second

Same application without vectorlayer:

  • add tilelayer, zoom to tilelayer extends or details: almost immediately
Jan 16, 2013 at 12:31 PM

Are you reprojecting your data on-the-fly using ProjNet or DotSpatial.Projections, or is it all in WebMercator?

Jan 16, 2013 at 12:46 PM

Reprojecting on-the-fly with ProjNet to WebMercator (OSM to Lambert 2008 didn't work), for vectorlayer I define both CoordinateTransformation and ReverseCoordinateTransformation

Jan 16, 2013 at 12:47 PM

Could you recheck performance without applying reprojection. I assume that it is the bottleneck.

Jan 16, 2013 at 12:54 PM

10 seconds becomes 4, quite a lot better, but now I'm close to Benin ;-)

I could (re)define the SRID of the dataset as WorldMercator (and all the additional vectordata too), which would help speeding up, because knowing the coordinates in our coordinate system is not important.

Jan 16, 2013 at 2:05 PM

One last question, you don't apply theming, do you?

Jan 17, 2013 at 9:01 AM

Yes, I do, and also labelling.

New benchmark times (without OSM background layer):

  • reprojection, labels and theme : 9 s
  • reprojection, theme : 5 s
  • reprojection, labels : 7 s
  • reprojection, no theme nor labels: 3 s
  • - - - -
  • no reprojection, with labels and theme : 6 s
  • no reprojection, with theme : 3 s
  • no reprojection, with labels : 3 s
  • no reprojection, no theme nor labels: 1 s

Reprojection on both vectorlayer and labellayer ;-)

Labelling and theme both on the same text attribute of vectorlayer data

        private VectorStyle RoadSegmentStyle(FeatureDataRow row)
            VectorStyle segmentStyle = new VectorStyle();
            string finalName = row["FINALNAME"].ToString().ToUpper();
            bool hasName = (finalName != string.Empty);
            bool hasSpecialName = finalName.StartsWith("["|| _specialNames.Contains(finalName);
            Color lineColor = (hasName ? (hasSpecialName ? Color.MediumBlue : Color.Black) : Color.Red);
            float lineWidth = (hasName && !hasSpecialName ? 2 : 1);
            segmentStyle.Enabled = true;
            segmentStyle.Line = new Pen(lineColor, lineWidth);
            segmentStyle.Line.StartCap = System.Drawing.Drawing2D.LineCap.Round;
            segmentStyle.Line.EndCap = System.Drawing.Drawing2D.LineCap.ArrowAnchor;
            segmentStyle.Line.CustomEndCap = new System.Drawing.Drawing2D.AdjustableArrowCap(33true);
            return segmentStyle;

_specialNames is a List<string> with a few special names. A named segment will be black, when it has a special name it will be blue; unnamed segments will be red; all with an arrow in the drawing direction of the segment, to distinguish the separate segments.

Mar 4, 2013 at 5:36 AM

This may help, here is Linq to SQLite.