VirtualKNN2

CREATE VIRTUAL TABLE knn2 USING VirtualKNN2();

SELECT CreateMissingSystemTables(1);

A first basically simple KNN2 query

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

• a VirtualKNN2 query closely resembles a VirtualKNN query. This is not surprising, but pay close attention because there are some relevant differences.
• Any valid VirtualKNN2 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:
  • db_prefix (optional)
    prefix identifying the attached-DB containing the SpatialTable (or SpatialView) to be queried.
    If missing or set as NULL the MAIN will be always implicitly intended.
  • f_table_name (mandatory)
    name of the SpatialTable (or SpatialView) 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, to avoid an ambiguous definition of the 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)
    some POINT Geometry representing the origin of the KNN2 search.
    This Geometry will always be assumed to be in the same SRID as the target Geometries to be searched.
  • radius (mandatory)
    the distance radius to be searched. The unit of measure of radius will always be assumed to be the natural one corresponding to the SRID of the SpatialTable (or SpatialView) being 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 results will be returned.
  • expand (optional)
    a boolean flag intended to enable / disable the expand option of KNN2. By default it will be always kept disabled, you must explicitily enable it when required.
• 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 SpatialTable (or SpatialView).
  • distance_crs (DOUBLE)
    the minimum distance between the corresponding feature into the searched SpatialTable (or SpatialView) and the origin point defined by ref_geometry.
    1. When the dataset's 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 SRID (longitude and latitude degrees), all distances will always be measured in angles.
  • distance_m (DOUBLE)
    a second measure of the minimum distance, may be expressed in different units of measure.
    1. When using a planar SRID it will be exactly the same as distance_crs.
    2. When using a geographic SRID all distances will always be measured in meters, with the most precise geodetic formulas being automatically applied.

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

SELECT * FROM knn2
WHERE db_prefix = 'MAIN' AND f_table_name = 'airports' AND f_geometry_column = 'geom' AND ref_geometry = MakePoint(10, 43) AND radius = 1.0;

SELECT * FROM knn2
WHERE db_prefix = 'MAIN' AND f_table_name = 'airports' AND f_geometry_column = 'geom' AND ref_geometry = MakePoint(10, 43) AND radius = 1.0 AND max_items = 3 AND expand = 0;

SELECT * FROM knn2
WHERE f_geometry_column = 'geom' AND f_table_name = 'airports' AND radius = 1.0 AND ref_geometry = MakePoint(10, 43);

SELECT * FROM knn2
WHERE max_items = 10 AND f_table_name = 'airports' AND ref_geometry = MakePoint(10, 43) AND radius = 1.0;

A second sample of a more sophisticated KNN2 query

SELECT a.pos AS rank, b.geonameid, b.name, b.country, a.distance_crs AS dist_deg, a.distance_m / 1000.0 AS dist_km
FROM knn2 AS a
JOIN airports AS b ON (b.geonameid = a.fid)
WHERE f_table_name = 'airports' AND ref_geometry = MakePoint(10, 43) AND radius = 0.5 AND max_items = 10;

SELECT a.pos AS rank, b.geonameid, b.name, b.country, a.radius, a.expand, a.distance_crs AS dist_deg, a.distance_m / 1000.0 AS dist_km
FROM knn2 AS a
JOIN airports AS b ON (b.geonameid = a.fid)
WHERE f_table_name = 'airports' AND ref_geometry = MakePoint(10, 43) AND radius = 0.5 AND max_items = 10 AND expand = 1;

Final considerations and a comparative benchmark

The problem to be solved

#1 - SQL Query for testing old KNN

CREATE TABLE old_knn AS
SELECT a.geonameid AS departure_id, a.name AS departure_name, a.country AS departure_country,
       b.geonameid AS arrival_id, b.name AS arrival_name, b.country AS arrival_country,
       k.distance / 1000.0 AS dist_km
FROM knn AS k, airports AS a
JOIN airports AS b ON (b.geonameid = k.fid AND a.geonameid <> b.geonameid)
WHERE f_table_name = 'airports' AND ref_geometry = a.geom AND max_items = 11;

#2 - SQL Query for testing KNN2 (expand disabled)

CREATE TABLE knn2_no_expand AS
SELECT a.geonameid AS departure_id, a.name AS departure_name, a.country AS departure_country,
       b.geonameid AS arrival_id, b.name AS arrival_name, b.country AS arrival_country,
       k.distance_m / 1000.0 AS dist_km
FROM knn2 AS k, airports AS a
JOIN airports AS b ON (b.geonameid = k.fid AND a.geonameid <> b.geonameid)
WHERE f_table_name = 'airports' AND ref_geometry = a.geom AND radius = 0.5 AND max_items = 11;

#3 - SQL Query for testing KNN2 (expand enabled)

CREATE TABLE knn2_expand AS
SELECT a.geonameid AS departure_id, a.name AS departure_name, a.country AS departure_country,
       b.geonameid AS arrival_id, b.name AS arrival_name, b.country AS arrival_country,
       k.distance_m / 1000.0 AS dist_km
FROM knn2 AS k, airports AS a
JOIN airports AS b ON (b.geonameid = k.fid AND a.geonameid <> b.geonameid)
WHERE f_table_name = 'airports' AND ref_geometry = a.geom AND radius = 0.5 AND max_items = 11 AND expand = 1;

Conclusions

• The new approach adopted by KNN2 is impressively faster than the previous one. There is an astonishing performance improvement.
  Requiring a predefined distance radius known in advance forces to adopt some arbitrary assumption, but it ensures an impressive speed increase.
• Looking at the number of generated rows, it's rather obvious that a too small distance radius value could easily lead to many missing matches when expand is disabled.
• However it should be noted that after enabling expand the complete solution has been successfully found, and this introduces only a marginal and well acceptable slow down.