DBD::SQLite // Prepared statement // BuildMbr // QuotedValue

[Florian Lohoff]

Hi,
i had a strange problem today. I am using some mojolicious REST stuff
with DBD::SQLite to query geojson features from a spatialite.

I had issues with wrong/all features beeing returned. After enabling
the db_trace i found that for example BuildMbr does not return anything
when passed quoted strings.

sqlite> select AsText(BuildMbr(8.482888,52.205703,8.488242,52.202669,4326));
POLYGON((8.482888 52.202669, 8.488242 52.202669, 8.488242 52.205703, 8.482888 52.205703, 8.482888 52.202669))
sqlite> select AsText(BuildMbr('8.482888','52.205703','8.488242','52.202669',4326));

sqlite>

Now - when using prepared statements ALL placeholder variables
will be quoted by DBD::SQLite so i currently see no possible way
to use a prepared statement.

My prepared statement contains something like this:

where ST_Intersects(geom, BuildMbr(?,?,?,?,4326))

I execute like this:

$sql->execute(8.48,52.20,8.49,52.25);

And the trace shows:

select AsGeoJSON(geometry) geojson,
MbrMinX(Buffer(geometry, 0.0005)) xmin,
MbrMaxX(Buffer(geometry, 0.0005)) xmax,
MbrMinY(Buffer(geometry, 0.0005)) ymin,
MbrMaxY(Buffer(geometry, 0.0005)) ymax,
lineway_id,lineway_type,lineway_changeset,areaway_id,areaway_type,areaway_changeset
from wayareaconflict
where ST_Intersects(geometry, BuildMbr('8.482888','52.205703','8.488242','52.202669',4326))
limit 500

Which returns the first 500 rows from the table as BuildMbr is empty.

Does anyone have a solution to this? I studied the DBD::SQLite Documentation and
the DBI documentation.

Flo
--
Florian Lohoff f...@zz.de
UTF-8 Test: The 🐈 ran after a 🐁, but the 🐁 ran away

[br...@frogmouth.net]

I think it is reasonable that BuildMbr() returns null if you pass the wrong data type. The documentation for 4.3 (https://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html) says:
BuildMbr( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision [ , SRID Integer] ) : Geometry

So I'd see this as an issue in the code doing the conversion. I do see this discussed in the Readme (https://github.com/DBD-SQLite/DBD-SQLite/blob/master/README#L105), and it looks like you can use bind_param() to make an explicit type statement.

So instead of
$sql->execute(8.48,52.20,8.49,52.25);

You would do
$sql->bind_param(1, 8.48, SQL_DOUBLE);
$sql->bind_param(2, 52.20, SQL_DOUBLE);
$sql->bind_param(3, 8.49, SQL_DOUBLE);
$sql->bind_param(4, 52.25, SQL_DOUBLE);
$sql->execute();

(I don't do Perl, so I didn't try this, but hope it helps).

Brad

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

On Sat, 31 Mar 2018 09:12:43 +1100, br...@frogmouth.net wrote:
> So instead of
> $sql->execute(8.48,52.20,8.49,52.25);
>
> You would do
> $sql->bind_param(1, 8.48, SQL_DOUBLE);
> $sql->bind_param(2, 52.20, SQL_DOUBLE);
> $sql->bind_param(3, 8.49, SQL_DOUBLE);
> $sql->bind_param(4, 52.25, SQL_DOUBLE);
> $sql->execute();
>
> (I don't do Perl, so I didn't try this, but hope it helps).
>

Alternatively you could set explicit type castings in your
SQL query, something like this:

... ST_Intersects(geom, BuildMbr(CastToDouble(?),
CastToDouble(?), CastToDouble(?), CastToDouble(?), 4326))

after binding all variable values this will translate into:

select AsText(BuildMbr(CastToDouble('8.482888'),
CastToDouble('52.205703'), CastToDouble('8.488242'),
CastToDouble('52.202669'),4326));
======================================

POLYGON((8.482888 52.202669, 8.488242 52.202669,
8.488242 52.205703, 8.482888 52.205703,
8.482888 52.202669))

bye Sandro

[Florian Lohoff]

On Sat, Mar 31, 2018 at 09:12:43AM +1100, br...@frogmouth.net wrote:

> I think it is reasonable that BuildMbr() returns null if you pass the wrong data type. The documentation for 4.3 (https://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html) says:
> BuildMbr( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision [ , SRID Integer] ) : Geometry

I am more used to PostGIS and my expectation was that you either know
how to deal with the input or fail horribly in a user noticeable way:

osm=> select ST_MakePoint('8.1', '51.2');
st_makepoint
--------------------------------------------
010100000033333333333320409A99999999994940
(1 row)

osm=> select ST_MakePoint(8.1, 51.2);
st_makepoint
--------------------------------------------
010100000033333333333320409A99999999994940
(1 row)

osm=> select ST_MakePoint();
ERROR: function st_makepoint() does not exist
LINE 1: select ST_MakePoint();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

So you see my puzzled that this was failing silently. Id expect to
get a error message when passing arguments with wrong types.

[br...@frogmouth.net]

It isn't failing silently, but rather returning null. In the command line tool ".null NULL" or similar is useful.

I think there is a case for verbose user errors, and a case for trying to carry on with the best approximation available.

Brad

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

On Sat, 31 Mar 2018 09:01:12 +0200, Florian Lohoff wrote:
> I am more used to PostGIS and my expectation was that you either know
> how to deal with the input or fail horribly in a user noticeable way:
>

> -------------------- <snip> ------------------------

>
> So you see my puzzled that this was failing silently. Id expect to
> get a error message when passing arguments with wrong types.
>

Florian,

this is a very common misunderstanding causing many unexpected
bad surprises.

yes, it's true that SpatiaLite and PostGIS are very similar
(mainly because both them are based on the same OGC-SFS
specifications), but nonetheless they strongly differ under
many aspects.

lazily assuming that "if it works on PostGIS it should surely
work on SpatiaLite as well" is a very dangerous pitfall,
because many fine-grained implementation details differ, and
blindly overlooking such differences can easily lead to
unexpected failures.

useful suggestion: carefully checking the specific signatures
of SQL functions as supported by SpatiaLite is a suggested
good practice that can effectively help to avoid many trivial
errors. please see:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

bye Sandro