VirtualKNN: a quick intro

You can download a sample db-file exactly corresponding to the practical examples we'll use in this first tutorial. This dataset corresponds to a collection of about 30.000 airports scattered all over the world; the original input shapefile was downloaded from here The reference system is 4326 WGS84 (based on longitude and latitude angles). The map on the right shows the geographic context of this first KNN example.

A first, simple and very inefficient, approach

SELECT geonameid, name, country, 
    Min(ST_Distance(MakePoint(10, 43), geom, 1)) / 1000.0 AS dist_km
FROM airports;
--------------------------------
6299623	| Marina Di Campo | IT | 33.043320

SELECT geonameid, name, country, 
    Min(ST_Distance(MakePoint(10, 43), geom, 1)) / 1000.0 AS dist_km
FROM airports
WHERE geonameid NOT IN (6299623);
---------------------------------
6299392 | Bastia-Poretta | FR | 65.226573

SELECT geonameid, name, country,
    Min(ST_Distance(MakePoint(10, 43), geom, 1)) / 1000.0 AS dist_km
FROM airports
WHERE geonameid NOT IN (6299623, 6299392);
---------------------------------
6299628 | Pisa / S. Giusto | IT	| 82.387014

SELECT geonameid, name, country, 
    Min(ST_Distance(MakePoint(10, 43), geom, 1)) / 1000.0 AS dist_km
FROM airports
WHERE geonameid NOT IN (6299623, 6299392, 6299628);
---------------------------------
6299630 | Grosseto Airport | IT | 91.549773

SELECT geonameid, name, country, 
    Min(ST_Distance(MakePoint(10, 43), geom, 1)) / 1000.0 AS dist_km
FROM airports
WHERE geonameid NOT IN (6299623, 6299392, 6299628, 6299630);
---------------------------------
6694495 | Corte | FR | 102.819778

VirtualKNN

CREATE VIRTUAL TABLE knn USING VirtualKNN();

A first basically simple KNN query

SELECT * FROM knn
WHERE f_table_name = 'airports' AND ref_geometry = MakePoint(10, 43);

• a VirtualKNN query closely resembles a VirtualSpatialIndex query. This is not surprising, since both of them are intended to be wrapped around an underlying R*Tree Spatial Index.
• Any valid VirtualKNN query must use a form such as: WHERE knn-column = value AND knn-column = value ...
• the input columns (the ones you can reference into a WHERE clause) are:
  • f_table_name (mandatory)
    name of the GeoTable containing the Geometries to be searched.
  • f_geometry_column (optional)
    name of the column of the above table containing the Geometries to be searched.
    1. If the table identified by f_table_name just contains a single Geometry column you can safely omit the f_geometry_column argument (it will be automatically set).
    2. If, however, the table identified by f_table_name contains two (or more) Geometry columns explicitly specifying the f_geometry_column argument is strictly required, so to get an unambiguous definition of the intended search context.
    3. In both cases f_table_name and f_geometry_column must exactly match a properly defined Geometry column supported by a corresponding Spatial Index.
  • ref_geometry (mandatory)
    any arbitrary Geometry (POINT, LINESTRING, POLYGON or whatever else) intended to represent the origin of the KNN search.
    Should necessarily be in the same SRID of the target Geometries to be searched.
  • max_items (optional)
    maximum number of rows to be returned into the resultset.
    The valid range is from 1 to 1024 (higher values will require a longer time to be processed).
    By default only the first 3 nearest Geometries will be identified.
• the output columns (the ones containing values retrieved by the KNN search) are:
  • pos (INTEGER)
    relative rank (sorted by distance): the closest item will be #1, the second closest item will be #2 and so on.
  • fid (INTEGER)
    the unique ROWID value of the corresponding feature into the searched GeoTable.
  • distance (DOUBLE)
    the minimum distance between the corresponding feature into the searched GeoTable and the origin defined by ref_geometry.
    1. When the defined SRID uses a planar (aka projected) reference system, all distances will be returned in the units defined by the projection (meters, feet, chains etc.).
    2. When using a geographic type (longitude and latitude degrees), all distances will always be measured in meters, and the most precise geodetic formulas will be automatically applied.

A second example of a more sophisticated KNN query

SELECT a.pos, b.name, b.country, a.distance / 1000.0 AS dist_km
FROM knn AS a
JOIN airports AS b ON (b.geonameid = a.fid)
WHERE f_table_name = 'airports' AND ref_geometry = MakePoint(10, 43) AND max_items = 5;

A third sample of KNN query

SELECT a.pos AS rank, b.geonameid, b.name, b.country, a.distance / 1000.0 AS dist_km
FROM knn AS a
JOIN airports AS b ON (b.geonameid = a.fid)
WHERE f_table_name = 'airports' AND ref_geometry = MakePoint(-17.3, -44) AND max_items = 5;

Advanced tutorial

SELECT a.pos AS rank, a.fid AS fid, a.distance AS dist_m, d.prov AS province,
       d.nome AS municipality, c.toponimo AS street_name, b.civico AS house_num,
       b.geom AS geom
FROM knn AS a
JOIN civici AS b ON (b.rowid = a.fid)
JOIN toponimi AS c ON (c.fid = b.id_toponimo)
JOIN comuni AS d ON (d.fid = c.id_comune)
WHERE a.f_table_name = 'civici' AND a.ref_geometry = MakePoint(1733003, 4816332, 3003) AND a.max_items = 10;

SELECT a.pos AS rank, a.fid AS fid, a.distance AS dist_m, b.prov AS province,
       b.comune AS municipality, b.toponimo AS street_name, b.civico AS house_num, 
       b.geom AS geom
FROM knn AS a
JOIN vw_civici AS b ON (b.rowid = a.fid)
WHERE a.f_table_name = 'vw_civici' AND a.ref_geometry = MakePoint(1733003, 4816332, 3003) AND a.max_items = 10;

SELECT a.pos AS rank, a.fid AS fid, a.distance AS dist_m, b.prov AS province,
       b.comune AS municipality, b.toponimo AS street_name, b.civico AS house_num, 
       b.geom AS geom
FROM knn AS a
JOIN vw_civici AS b ON (b.rowid = a.fid) 
WHERE a.f_table_name = 'civici' AND a.ref_geometry = MakePoint(1595625, 4767420, 3003) AND max_items = 20;