KNN2 with attached temporary geometries

[David Novak]

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]

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]

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