Geometry constraint [geom-type or SRID not allowed]

[olivier leprêtre]

Hi,

I get an error when trying to insert a wkt point into a spatialite table with vb .net

The strange thing is that Insert works when I build an sql query and execute it but not when I use sqlite.sqlitecommand cmd.Parameters.AddWithValue.

In this case, a trigger starts and I get an error "geom-type or SRID not allowed"

Point description is as follow ;

GeomFromText ('POINT (848678.075879837 6408754.56379608)',2154)

I checked the table which is POINT type, 2154 srid and anyway, the command is ok when wkt is inserted in a sql query. 

It seems to me that Parameters.AddWithValue modify wkt in a way that is not recognized by spatialite.

Any suggestion would be appreciated,

Olivier

Here is the trigger description.

CREATE TRIGGER "ggu_t_adresse_geom" BEFORE UPDATE OF "geom" ON "my_table"

FOR EACH ROW BEGIN

SELECT RAISE(ROLLBACK, 'my-table.the_geom violates Geometry constraint [geom-type or SRID not allowed]')

WHERE (SELECT geometry_type FROM geometry_columns

WHERE Lower(f_table_name) = Lower('my_table') AND Lower(f_geometry_column) = Lower('the_geom')

AND GeometryConstraints(NEW."the_geom", geometry_type, srid) = 1) IS NULL;

END;

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

On Sun, 13 Nov 2016 08:58:37 -0800 (PST), olivier leprêtre wrote:
> Hi,
>
> I get an error when trying to insert a wkt point into a spatialite
> table with vb .net
>
> The strange thing is that Insert works when I build an sql query and
> execute it but not when I use sqlite.sqlitecommand
> cmd.Parameters.AddWithValue.
>
> In this case, a trigger starts and I get an error "geom-type or SRID
> not allowed"
>
> Point description is as follow ;
>
> GeomFromText ('POINT (848678.075879837 6408754.56379608)',2154)
>
> I checked the table which is POINT type, 2154 srid and anyway, the
> command is ok when wkt is inserted in a sql query. 
> It seems to me that Parameters.AddWithValue modify wkt in a way that
> is not recognized by spatialite.
>
> Any suggestion would be appreciated,
>

Hi Olivier,

there is nothing odd in your SQL code: and as yourself
are reporting it nicely works using plain SQL methods.

so your problem definitely seems to be caused by the
programming language you are using (vb .net) and/or by
the methods supplied by the data connector.

I've never used VB in my life, anyway the first debugging
action I can suggest is to create a table containing a
TEXT column, then inserting into it your WKT expressions
by calling cmd.Parameters.AddWithValue.

After this you simply have to query that TEXT column so
to check if any unexpected data transformation has been
introduced by the language or by the data connector.

bye Sandro

[Mark Johnson]

Also adding a 'ST_Srid(GeomFromText ('POINT (848678.075879837 6408754.56379608)',2154)) AS result_srid' and checking that it returns the correct result (2154) under those conditions would be useful.

Mark

bye Sandro

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[olivier leprêtre]

That was a good idea Sandro. So I changed the type to Varchar, but the problem is I get exactly the same value in the field. Incomprehensible !

[Brad Hards]

Can you make a minimal, self contained, example that will compile (say a
console project) that shows the problem?

Brad

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

Hi Olivier,

correctly writing a prepared SQL statement requires some precautions,
and you must always carefully evaluate all consequences of binding
values at run time.

just a quick example; we'll start first using spatialite_gui, so to
check our basic SQL statements:

CREATE TABLE olivier (
id INTEGER PRIMARY KEY AUTOINCREMENT);
SELECT AddGeometryColumn('olivier', 'geom', 2154, 'POINT', 'XY');
INSERT INTO olivier VALUES (NULL,
ST_GeomFromText('POINT(848678.075879837 6408754.56379608)',2154));

all right, anything works as expected, so we can now pass to
test few prepared statements (I used a simple C program, but
you can easily replicate under VB):


test #1:
==============================================
sql = "INSERT INTO olivier (id, geom) VALUES (?, ?)";
sqlite3_bind_null(stmt, 1);
sqlite3_bind_text(stmt, 2,
"GeomFromText ('POINT (848678.075879837 6408754.56379608)',2154)",
-1, SQLITE_STATIC);
$ error: olivier.geom violates Geometry constraint [geom-type or SRID
not allowed]


test #2:
==============================================
sql = "INSERT INTO olivier (id, geom) VALUES (?, GeomFromText (?,
2154))";
sqlite3_bind_null(stmt, 1);
sqlite3_bind_text(stmt, 2,
"POINT (848678.075879837 6408754.56379608)",
-1, SQLITE_STATIC);
$ success


test #3:
==============================================
sql = "INSERT INTO olivier (id, geom) VALUES (?, GeomFromText ('POINT(?
?)', 2154))";
sqlite3_bind_null(stmt, 1);
sqlite3_bind_double(stmt, 2, 848678.075879837);
sqlite3_bind_double(stmt, 3, 6408754.56379608);
$ success


comments:
=========================
both test#2 and test#3 nicely work because we are correctly informing
the SQL engine that we intend to use an SQL function named
ST_GeomFromText(),
and this function must accept dynamic arguments.

test#1 will fail because in this case the SQL engine is unaware that
we intend using a SQL function, and consequently it will handle the
corresponding argument just as a plain text string.
in other words, from a pure SQL perspective, the following statement
will be effectively executed:

INSERT INTO olivier VALUES (NULL,
'ST_GeomFromText(''POINT(848678.075879837
6408754.56379608)'',2154)');

as you can easily check using spatialite_gui, such a malformed
statement
will cause the same violation of Geometry constraints.

bye Sandro

[olivier leprêtre]

Hi,

At first, thanks Sandro to take some time to create examples and comment them. I appreciate very much your help.

Thanks also to Brad to offer to study an app, I'll do it if I don't manage to solve the problem with Sandro's explanation.

I tried to implement Sandro's idea but problem is that .Net offers Sql preparation command with sqlitecommand.parameters.Add or AddWithValue, starting with an sql insert commant "Insert into mytable (id,geom) values (@id,@geom)

Add is then used to prepare and pass parameters into the sql command

.Parameters.AddWithValue("id",1)

or

.Parameters.Add("@geom", DbType.Object).Value = "GeomFromText ('POINT (841565.456371231 6453440.51271859)',2154)"

Unfortunately, I tried several ways to Add the geom parameter  with splitted part but I didn't find any way to force sqlitecommand not to interpret value as a string.

Olivier

[Pac]

Hi Olivier,

You must reread Sandro's first comment and then remember again that ST_GeomFromText is a SQL function, so it can't be a value. Don't let confuse you that in SQL you can use a function's result as a value for an insert. So your real values (that is, parameters for your SQL command) are id, geometry's WKT and SRID.

Keeping always that in mind, your SQL command should be (given 2154 as your fixed SRID):

Insert into mytable (id,geom) values (@id, GeomFromText(@geom, 2154))

With that command, you could prepare it and then add its id and geom parameters:

.Parameters.AddWithValue("@geom", "POINT (841565.456371231 6453440.51271859)")

Hope it helps,
Paco

[olivier leprêtre]

Thanks very much Paco, it works I tried to modify the insert command but not in the way you suggest so I left it there for later. 

and you kindly solved it.  Thanks  to you ! Thanks again to Sandro and Brads, not sure I would have find that by myself.