new KNN2, too many lines in output

[Totò Fiandaca]

I created a new db and imported two tables both in 32632,

in this db I launch two queries:

the first with the new KNN2:

SELECT a.fid as fid, a.distance_crs as distance, zz.chiave_uni as chiave_uni,zz.geometry

    FROM  scuole as zz 

    JOIN knn2 as a

    WHERE f_table_name = 'route'

    AND f_geometry_column = 'geometry'

    AND ref_geometry = zz.geometry

    AND radius = 100.0 and max_items = 1

the second using only the classic spatialIndex

SELECT a.ogc_fid as id_route, Min(ST_Distance(a.geometry, zz.geometry)) AS distance,

    zz.chiave_uni as chiave_uni, zz.geometry

FROM route as a, scuole as zz

WHERE ST_Distance(a.geometry, zz.geometry) <= 100.0 AND a.ogc_fid IN (

        SELECT rowid

        FROM SpatialIndex

        WHERE f_table_name = 'route'

           AND search_frame = BuildCircleMbr(ST_X(zz.geometry), ST_Y(zz.geometry), 100.0))

GROUP by zz.chiave_uni;

result:

KNN2 returns 1911 rows

spatialIndex 1937

what did I not understand about the new KNN2?

grazie Totò

[Maurizio Trevisani]

What about the results with the old knn?

Thanks,

Maurizio

--
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-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/ac3a07cc-a30a-45e5-8f34-c329cdb36b4an%40googlegroups.com.

[Totò Fiandaca]

with the old KNN I have to use this query:

SELECT d.chiave_uni, d.fid as id_route, d.distance as distance,

     ST_shortestline(d.geometry, s.geometry) as geom 

FROM

(SELECT a.fid as fid, a.distance as distance, zz.chiave_uni as chiave_uni,zz.geometry

    FROM knn as a 

    JOIN

    scuole as zz 

    WHERE f_table_name = 'route'

    AND f_geometry_column = 'geometry'

    AND ref_geometry = zz.geometry 

    AND max_items = 1) d,

    route s  

WHERE ST_Distance(s.geometry, d.geometry) <= 100 and id_route = s.ogc_fid;

the execution time is enormous, over 100 sec against 2 of the new KNN2, the result is 1937, equal to the classic spatialindex

grazie Totò

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

On Fri, 25 Jun 2021 08:32:56 -0700 (PDT), Totò Fiandaca wrote:
> result:
> KNN2 returns 1911 rows
> spatialIndex 1937
>
> what did I not understand about the new KNN2?
>

Ciao Toto'

this one is an *experimental* testing version;
your SQL seems to be correct, so the most probable
cause is some undetected bug.

I'll be really happy to set up a debugging session,
but for doing so I absolutely need:

a) a copy of your DB
b) a SQL script I could use so to reproduce the problem

bye Sandro

p.s. you can send it as a private mail to my personal
mailbox.

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

On Fri, 25 Jun 2021 08:32:56 -0700 (PDT), Totò Fiandaca wrote:

> result:
> KNN2 returns 1911 rows
> spatialIndex 1937
>
> what did I not understand about the new KNN2?
>

Hi Toto',

there is a subtle problem of undestanding well the intended
scope of KNN/KNN2

as the name itself says, any KNN algorithm is intended to
identify the "nearest neighbors" to a given point.

the old KNN searched all the brances of the tree until
the requested number of nearest points was found.

KNN2 has introduced a "distance radius" in order to speed
up the execution time, but this is just a dirty trick for
passing to R*Tree queries a bounding box (aka MBR) where
the nearest points maust be searched.
this doesn't implies at all that points exceeding the
distance radius will be discarded; if they fall within
the given BBOX they'll be included into the returned
resultset.

if your specific logic requires to filter a maximum distance
this is not a KNN problem, it's a problem of the SQL query
that you are writing around the KNN core.

countercheck:

SELECT a.fid as fid, a.distance_crs as distance,
zz.chiave_uni as chiave_uni,zz.geometry
FROM scuole as zz
JOIN knn2 as a
WHERE f_table_name = 'route'
AND f_geometry_column = 'geometry'
AND ref_geometry = zz.geometry
AND radius = 100.0 and max_items = 1

ORDER BY a.distance_crs DESC;

this is exactly your initial query (the one returning
1911 rows), but this time we've requested to order
the rows by decrearing distance.
as you can easily check there ara many rows
exceeding the 100.0 metres radius.

SELECT a.fid as fid, a.distance_crs as distance,
zz.chiave_uni as chiave_uni,zz.geometry
FROM scuole as zz
JOIN knn2 as a
WHERE f_table_name = 'route'
AND f_geometry_column = 'geometry'
AND ref_geometry = zz.geometry
AND radius = 100.0 and max_items = 1

GROUP by zz.chiave_uni
HAVING distance <= 100.0;

this further rewrite of your initial query
finally returns 1,737 rows.
exactly as the one you've written by directly+
accessing the Spatial Index.

quick conclusione: there is nothing wrong in KNN2,
that exactely does what is expected to do.
your assumption that the KNN2 module will automatically
discard any match exceeding the distance radius was
not justified.

bye Sandro

[Totò Fiandaca]

Good morning Sandro,
your explanation is very clear, now I understand everything.

thank you very much.

Totò

--
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-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/d586433c54bbcad8344bf97a50f1d0a6%40lqt.it.

--

Ing. Salvatore Fiandaca
mobile.:+39 327.493.8955 
m: pigrecoin...@gmail.com

C.F.: FNDSVT71E29Z103G

P.IVA: 06597870820

membro QGIS Italia - http://qgis.it/
socio GFOSS.it - http://gfoss.it/

blog: https://pigrecoinfinito.com/ 
FB: Co-admin - https://www.facebook.com/qgis.it/  

TW: https://twitter.com/totofiandaca

43°51'0.54"N  10°34'27.62"E - EPSG:4326

“Se la conoscenza deve essere aperta a tutti,

perchè mai limitarne l’accesso?” 

R. Stallman

Questo documento, allegati inclusi, contiene informazioni di proprietà di FIANDACA SALVATORE e deve essere utilizzato esclusivamente dal destinatario in relazione alle finalità per le quali è stato ricevuto. E' vietata qualsiasi forma di riproduzione o divulgazione senza l'esplicito consenso di FIANDACA SALVATORE. Qualora fosse stato ricevuto per errore si prega di informare tempestivamente il mittente e distruggere la copia in proprio possesso.