[System.Data.SQLite] Extension datatypes (i.e. Spatialite geometry)

[marku...@googlemail.com]

Hello dear Spatialite and (System.Data.)SQLite users,

I move this question from the sqlite...@sqlite.org mailing list since I was told that this was a more appropriate place for it.
Below you can find the original conversation (2 mails) which I had with Sandro.

The original question was:

I am using System.Data.SQLite in combination with Spatialite which works very well.
One problem I currently have is that when I'm trying to insert several milions of records (namely road segments with just two columns: Id INTEGER, Geometry LINESTRING) it takes a very long time (about an hour or more).
The index on the Geometry column is created after all records are inserted and the whole insertion process is encapsulated in one big transaction.

I was then trying to reuse the same insertion-command (SQLiteCommand) with two parameters for the Id and the Geometry (doing this for non-spatial tables resulted in a significant performance boost).
Now, the problem is that I don't have a fitting DbType for LINESTRING (or other GEOMETRY types).
Using a parameter without specifying the DbType then yields an exception.
I also tryed using DbType.String which also caused an exception.

So my question is:
Is there any possibility to use commands with command parameters to insert spatial data with System.Data.SQLite?


================================== ORIGINAL CONVERSATION ===============================================
 

Hi Sandro,

thanks for your answer - I will post the question (possibly including the solution) on the appropriate mailing list.
Actually, my columns are defined the way you suggested - I will make it more clear in my post to the spatialite mailing-list.

But your answer sounds interesting - so I hope you could answer one further question:
Currently I am converting my C# polygons to a string of the form "LINESTRING(x1 y1, ..., xN yN)" which would be the WKT (Well-Known Text) format.
If I understand your answer correct I could simply convert my C# polygons to the WKB (Well-Known Binary) format and use BLOB as the DbType in my SQLiteParameter.
Is that correct?

Am 29.01.2013 14:48, schrieb a.fu...@lqt.it:

On Tue, 29 Jan 2013 14:22:07 +0100, Markus Dibo wrote:

Hello dear (System.Data.)SQLite users,

I am using System.Data.SQLite in combination with Spatialite which
works very well.
One problem I currently have ... <snip>

Hi Markus,

I suppose that posting such questions into the SpatiaLite own mailing
list [1] would probably be much more appropriate.

[1] https://groups.google.com/forum/?fromgroups#!forum/spatialite-users

just a very quick answer:

trying to insert several milions of records (namely road segments with
just two columns: Id INTEGER, Geometry LINESTRING)

sorry, but this is not the expected way to declare a Geometry column;
the syntax expected by SpatiaLite is:
CREATE xxx (Id INTEGER);
SELECT AddGeometryColumn('xxx', 'Geometry', 4321, 'LINESTRING', 'XY');

Now, the problem is that I don't have a fitting DbType for LINESTRING
(or other GEOMETRY types).

from the SQLite own perspective all Geometries simply are generic BLOBs;
in order to activate any kind of specific support for Geometry, Linestring,
Polygon or Point you absolutely have to explicitly call the "special" Spatial
SQL functions supported by the SpatiaLite extension.

bye Sandro

[a.fu...@lqt.it]

Hi Markus,

> One problem I currently have is that when I'm trying to insert
> several
> milions of records (namely road segments with just two columns: Id
> INTEGER, Geometry LINESTRING) it takes a very long time (about an
> hour
> or more).
>

seems to be a very long time ...
anyway just stating "several millions of records" is rather elusive:
- how complex are your individual Linestrings ?
a very simple Linestring (let say < 10 vertices) is absolutely not
the same of a very complex one containing many thousand vertices.
e.g. during some debugging session I encountered a really huge
polygon
containing about 1 million points ... and simply processing this
single "abnormal" geometry required a very long time ;-)
- wich is the final size of your DB ? several MB ? or GB ?
loading some really huge db-file (> 1 GB) will obviously require
a long time; and in this case the operating system will probably
penalize the overall performance if you don't apply some specific
optimization (e.g. by allocating a wider sqlite cache).

> Now, the problem is that I don't have a fitting DbType for LINESTRING
> (or other GEOMETRY types).
> Using a parameter without specifying the DbType then yields an
> exception.
>

this probably is because you are attempting to insert some invalid
geometry (or one of mismatching type, or may be presenting an invalid
SRID value).

> I also tryed using DbType.String which also caused an exception.
>
> So my question is:
> Is there any possibility to use commands with command parameters to
> insert spatial data with System.Data.SQLite?
>

Certainly yes, but you are always expected to use the appropriate SQL
conversion functions. e.g.

CREATE TABLE tbl (id INTEGER PRIMARY KEY AUTOINCREMENT);
SELECT AddGeometryColumn('tbl', 'geom', 4326, 'POINT', 'XY');

INSERT INTO tbl (id, geom) VALUES (NULL, GeomFromText(?, 4326));

while binding actual values to the expected parameter, these
values will be accepted as valid:
'POINT(1 1)'
'POINT(1.234 5.678)'

... these values will be discarded (mismatching dimensions):
'POINT Z(1 1 1)'
'POINT ZM(1 2 3 4)'

... these values will be discarded (mismatching type):
'LINESTRING(1 1, 2 2)'
'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'

bye Sandro




--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[Markus Dibo]

Hello again,

In the following I will show my first two solutions (one which
re-creates the SQLiteCommand for every record and one throwing
exceptions) and the working solution and I will explain, what I think I
did wrong.
Also I will show the performance differences.

Problem summary:
My dataset consists of about 10.000.000 records and each record has < 10
vertices.

Solution 1:
===============================================================
// ... code ...

foreach(var polyline in Polylines)
{
using(var cmd = _connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Polylines (Id, Geometry)
VALUES(" + polyline.Id + ", ST_GeomFromText('" + Polyline2WKT(polyline)
+ "', 3857))";
cmd.ExecuteNonQuery();
}
}

/*
Converts a polyline to the Well-Known Text format, e.g. "LINESTRING(1 1,
2 2, 3 3)"
*/
private static string Polyline2WKT(Polyline Polyline){ /* ... code ... */}

// ... code ...
===============================================================
This solution takes about 45 minutes for the dataset.

Solution 2 (not working):
===============================================================
// ... code ...

using(var cmd = _connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Polylines (Id, Geometry)
VALUES(@IdParam, @GeometryParam)"; // 0
var idParam = new SQLiteParameter("@IdParam");
cmd.Parameters.Add(idParam);
var geometryParam = new SQLiteParameter("@GeometryParam");
cmd.Parameters.Add(geometryParam);
foreach(var polyline in Polylines)
{
idParam.Value = polyline.Id;
geometryParam.Value = "ST_GeomFromText('" +
Polyline2WKT(polyline) + "', 3857)"; // 1
cmd.ExecuteNonQuery();
}
}

/*
Converts a polyline to the Well-Known Text format, e.g. "LINESTRING(1 1,
2 2, 3 3)"
*/
private static string Polyline2WKT(Polyline Polyline){ /* ... code ... */}

// ... code ...
===============================================================
This "solution" throws exceptions. This is (at least I think so) because
I used the spatialite function "ST_GeomFromText" in the command
parameter and not in the CommandText (see comments 0 and 1).
Initially i thought the error appears because I did not set the
appropriate DbType's for the SQLiteParameter (I simply overlooked the
spatialite function).
That was the reason I asked about Extension datatypes for spatialite.
Thanks to the conversation with Sandro I noticed the mistake and came up
with a workin solution which is presented in the following:

Solution 3:
===============================================================
// ... code ...

using(var cmd = _connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Polylines (Id, Geometry)
VALUES(@IdParam, ST_GeomFromText(@GeometryParam, 3857))"; // 0
var idParam = new SQLiteParameter("@IdParam");
cmd.Parameters.Add(idParam);
var geometryParam = new SQLiteParameter("@GeometryParam");
cmd.Parameters.Add(geometryParam);
foreach(var polyline in Polylines)
{
idParam.Value = polyline.Id;
geometryParam.Value = Polyline2WKT(polyline); // 1
cmd.ExecuteNonQuery();
}
}

/*
Converts a polyline to the Well-Known Text format, e.g. "LINESTRING(1 1,
2 2, 3 3)"
*/
private static string Polyline2WKT(Polyline Polyline){ /* ... code ... */}

// ... code ...
===============================================================
See the differences to Solution 2 at the comments 0 and 1.
This solution takes about 15 minutes for the dataset.

[Felix Obermaier]

Try this:

using(var cmd = _connection.CreateCommand())
{
cmd.CommandText =

@"INSERT INTO Polylines (Id, Geometry)
VALUES(@IdParam, ST_GeomFromText('@GeometryParam', 3857)"; // 0

var idParam = new SQLiteParameter("@IdParam");
cmd.Parameters.Add(idParam);
var geometryParam = new SQLiteParameter("@GeometryParam");
cmd.Parameters.Add(geometryParam);
foreach(var polyline in Polylines)
{
idParam.Value = polyline.Id;
geometryParam.Value = Polyline2WKT(polyline); // 1
cmd.ExecuteNonQuery();
}
}

WKT parsing is probably not a good choice, If you have some
PolylineToWkb function, alter your query accordingly

Note: code has not seen a compiler, may need adjustments
Hth FObermaier

[Markus Dibo]

Sorry, i don't get it.
This is the same code as in my "Solution 3" except for the single quotes
around @GeometryParam and an @ at the beginning of the string constant
which makes it a verbatim string.
The @ symbol does not make a difference since the string constant does
not contain
I tested the version with the single quotes which resulted in NULL
values for each geometry.

Like i said in the previous mail the "Solution 3" works and is three
times as fast compared to my "Solution 1".

Am i missing something here?

For the WKT vs. WKB part:
yes, i will definitely try this - thx for the hint.
After looking into the WKB format description i think that converting my
polyline into WKB will be much faster.

No, the code did not see a compiler - i just extracted the important
parts out of my project and wrote it in my mail client :)
But the CommandText string should be correct and for the rest of the
code there should only be minor typos - if any.

Regards Markus