KNN2 with attached temporary geometries

43 views
Skip to first unread message

David Novak

unread,
Sep 24, 2021, 4:06:07 PM9/24/21
to SpatiaLite Users
I have been playing around with the spatialite 5.1 version and tried to use the new knn2 module with temporary geometries. However, setting the  f_table_name as a temporary geometry, returns no values, while it does it just fine, when the table is an ordinary table. The query goes as follows:

WITH g(id, pt) AS (SELECT id, PointN(geom_linestring, 1) FROM memory_database.segment)
SELECT KNN2.fid, KNN2.distance_m, AsText(pt), segment.id, segment.name FROM KNN2, g, memory_database.segment
WHERE f_table_name = 'memory_database.activity' AND ref_geometry = pt AND radius = 0.1 AND g.id = segment.id

'memory_database' is a temporary database, attached to the current database, using:
ATTACH DATABASE ':memory:' AS memory_database; and adding the corresponding temporary geometry_columns and spatial indices.

Interestingly, this query works, if I replace the value in f_table_name with a non-temporary table and leave the rest as-is.

Is there a restriction in knn2 on temporary geometries? This has been tested using Python.


a.fu...@lqt.it

unread,
Sep 25, 2021, 3:17:37 AM9/25/21
to spatiali...@googlegroups.com
On Fri, 24 Sep 2021 13:06:07 -0700 (PDT), David Novak wrote:
> I have been playing around with the spatialite 5.1 version and tried
> to use the new knn2 module with temporary geometries. However,
> setting
> the F_TABLE_NAME as a temporary geometry, returns no values, while it
> does it just fine, when the table is an ordinary table. The query
> goes
> as follows:
>
> --------------------------- <snip>
> -----------------------------------
>
> Is there a restriction in knn2 on temporary geometries? This has been
> tested using Python.
>

Hi David,

the answer to your question is clearly explained in this Wiki page:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=TemporaryGeometries

"Behind the scenes 1.
SpatiaLite will silently create both geometry_columns and
spatial_ref_sys
metatables into the ATTACHED DATABASE when required."

short conclusion: KNN can only support geometry columns defined in
MAIN.geometry_columns

but temporary geometries are not in the MAIN database, they live in
the TEMP database, and consequently they are registered into
TEMP.geometry_columns, so they are invisible to KNN

bye Sandro

David Novak

unread,
Sep 25, 2021, 4:17:34 PM9/25/21
to SpatiaLite Users
Hello Sando,

Thank you for your clarification and excuse my confusion. I somehow missed the optional 'db_prefix'-parameter. Changing the query like this, makes it work from an attached database:

"...
WHERE db_prefix = 'memory_database' AND f_table_name = 'activity' AND ref_geometry = pt AND radius = 0.1 AND g.id = segment.id) "

Best wishes,
David
Reply all
Reply to author
Forward
0 new messages