Trying to run a KNN2 Query

wakumaku

unread,

Dec 13, 2021, 4:20:17 PM12/13/21

to SpatiaLite Users

Hi everyone!

I'm building a small application that given a point (latitude, longitude) returns the 10 nearest cities and I thought KNN2 is a good solution for my use case but I'm not able to make it work.

The application must work in a docker container, so all the following commands has been executed in a docker Alpine 3.15.0 container.

Phase 1, install Spatialite and SQLite:

/ # apk add libspatialite
fetch https://dl-cdn.alpinelinux.org/alpine/v3.15/main/x86_64/APKINDEX.tar.gz
fetch https://dl-cdn.alpinelinux.org/alpine/v3.15/community/x86_64/APKINDEX.tar.gz
(1/18) Installing libgcc (10.3.1_git20211027-r0)
(2/18) Installing libstdc++ (10.3.1_git20211027-r0)
(3/18) Installing geos (3.10.1-r0)
(4/18) Installing minizip (1.2.11-r2)
(5/18) Installing ca-certificates (20191127-r7)
(6/18) Installing brotli-libs (1.0.9-r5)
(7/18) Installing nghttp2-libs (1.46.0-r0)
(8/18) Installing libcurl (7.80.0-r0)
(9/18) Installing sqlite-libs (3.36.0-r0)
(10/18) Installing libjpeg-turbo (2.1.2-r0)
(11/18) Installing xz-libs (5.2.5-r0)
(12/18) Installing libwebp (1.2.1-r0)
(13/18) Installing zstd-libs (1.5.0-r0)
(14/18) Installing tiff (4.3.0-r0)
(15/18) Installing proj (8.2.0-r0)
(16/18) Installing librttopo (1.1.0-r1)
(17/18) Installing libxml2 (2.9.12-r2)
(18/18) Installing libspatialite (5.0.1-r3)
Executing busybox-1.34.1-r3.trigger
Executing ca-certificates-20191127-r7.trigger
OK: 43 MiB in 32 packages

/ # apk add sqlite
(1/4) Installing ncurses-terminfo-base (6.3_p20211120-r0)
(2/4) Installing ncurses-libs (6.3_p20211120-r0)
(3/4) Installing readline (8.1.1-r0)
(4/4) Installing sqlite (3.36.0-r0)
Executing busybox-1.34.1-r3.trigger
OK: 45 MiB in 36 packages

Phase 2:

Load worldcities database. (https://simplemaps.com/data/world-cities)

/ # apk add curl
(1/1) Installing curl (7.80.0-r0)
Executing busybox-1.34.1-r3.trigger
OK: 45 MiB in 37 packages
/ # curl -o worldcities.zip https://simplemaps.com/static/data/world-cities/basic/simplemaps_worldcities_basicv1
.74.zip

/ # unzip worldcities.zip
Archive: worldcities.zip
inflating: license.txt
inflating: worldcities.csv
inflating: worldcities.xlsx

/ # head worldcities.csv
"city","city_ascii","lat","lng","country","iso2","iso3","admin_name","capital","population","id"

/ # sqlite3 worldcities.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import worldcities.csv worldcities
sqlite> SELECT * FROM worldcities LIMIT 1;
Tokyo,Tokyo,35.6897,139.6922,Japan,JP,JPN,"Tōkyō",primary,37977000,1392685764

sqlite> .schema worldcities
CREATE TABLE IF NOT EXISTS "worldcities"(
"city" TEXT,
"city_ascii" TEXT,
"lat" TEXT,
"lng" TEXT,
"country" TEXT,
"iso2" TEXT,
"iso3" TEXT,
"admin_name" TEXT,
"capital" TEXT,
"population" TEXT,
"id" TEXT
, "pinpoint" POINT);
CREATE TRIGGER "ggi_worldcities_pinpoint" BEFORE INSERT ON "worldcities"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'worldcities.pinpoint violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT geometry_type FROM geometry_columns
WHERE Lower(f_table_name) = Lower('worldcities') AND Lower(f_geometry_column) = Lower('pinpoint')
AND GeometryConstraints(NEW."pinpoint", geometry_type, srid) = 1) IS NULL;
END;
CREATE TRIGGER "ggu_worldcities_pinpoint" BEFORE UPDATE OF "pinpoint" ON "worldcities"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'worldcities.pinpoint violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT geometry_type FROM geometry_columns
WHERE Lower(f_table_name) = Lower('worldcities') AND Lower(f_geometry_column) = Lower('pinpoint')
AND GeometryConstraints(NEW."pinpoint", geometry_type, srid) = 1) IS NULL;
END;
CREATE TRIGGER "tmu_worldcities_pinpoint" AFTER UPDATE ON "worldcities"
FOR EACH ROW BEGIN
UPDATE geometry_columns_time SET last_update = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE Lower(f_table_name) = Lower('worldcities') AND Lower(f_geometry_column) = Lower('pinpoint');
END;
CREATE TRIGGER "tmi_worldcities_pinpoint" AFTER INSERT ON "worldcities"
FOR EACH ROW BEGIN
UPDATE geometry_columns_time SET last_insert = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE Lower(f_table_name) = Lower('worldcities') AND Lower(f_geometry_column) = Lower('pinpoint');
END;
CREATE TRIGGER "tmd_worldcities_pinpoint" AFTER DELETE ON "worldcities"
FOR EACH ROW BEGIN
UPDATE geometry_columns_time SET last_delete = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE Lower(f_table_name) = Lower('worldcities') AND Lower(f_geometry_column) = Lower('pinpoint');
END;

sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT InitSpatialMetaData(1);
1
sqlite> SELECT CreateMissingSystemTables(1);
1
sqlite> SELECT AddGeometryColumn('worldcities', 'pinpoint', 4326, 'POINT', 'XY');
1
sqlite> UPDATE worldcities SET pinpoint = GeomFromText('POINT(' || lat || ' ' || lng || ')', 4326);

sqlite> SELECT CreateSpatialIndex("worldcities", "pinpoint");
1

So far, so good (if I'm not missing any step)

I try my first KNN2 query ...

sqlite> SELECT * FROM knn2 WHERE f_table_name = 'worldcities' AND f_geometry_column = 'pinpoint' AND ref_geometry = MakePoint(41, 36) AND radius = 1.0 AND max_items = 10 AND expand = 0;
Error: no such table: knn2

sqlite> .tables
ElementaryGeometries raster_coverages_srid
ISO_metadata rl2map_configurations
ISO_metadata_reference rl2map_configurations_view
ISO_metadata_view spatial_ref_sys
KNN spatial_ref_sys_all
SE_external_graphics spatial_ref_sys_aux
SE_external_graphics_view spatialite_history
SE_fonts sql_statements_log
SE_fonts_view stored_procedures
SE_raster_styled_layers stored_variables
SE_raster_styled_layers_view topologies
SE_raster_styles vector_coverages
SE_raster_styles_view vector_coverages_keyword
SE_vector_styled_layers vector_coverages_ref_sys
SE_vector_styled_layers_view vector_coverages_srid
SE_vector_styles vector_layers
SE_vector_styles_view vector_layers_auth
SpatialIndex vector_layers_field_infos
data_licenses vector_layers_statistics
geom_cols_ref_sys views_geometry_columns
geometry_columns views_geometry_columns_auth
geometry_columns_auth views_geometry_columns_field_infos
geometry_columns_field_infos views_geometry_columns_statistics
geometry_columns_statistics virts_geometry_columns
geometry_columns_time virts_geometry_columns_auth
idx_ISO_metadata_geometry virts_geometry_columns_field_infos
idx_ISO_metadata_geometry_node virts_geometry_columns_statistics
idx_ISO_metadata_geometry_parent wms_getcapabilities
idx_ISO_metadata_geometry_rowid wms_getmap
networks wms_ref_sys
raster_coverages wms_settings
raster_coverages_keyword worldcities
raster_coverages_ref_sys

My guess is that it should be a KNN2 table .. so I try to create a new one:

sqlite> CREATE VIRTUAL TABLE knn2 USING VirtualKNN2();
Error: no such module: VirtualKNN2

There is a note in the documentation about KNN2: Note: VirtualKNN2 necessarily requires a corresponding library support, so any attempt to open a db-file including a VirtualKNN2 table by using some previous version of the SpatiaLite will surely raise an error condition.

But which library? Probably I'm missing something here ...

Running the same Query but changing the table to knn, it works:

sqlite> sqlite> SELECT * FROM knn WHERE f_table_name = 'worldcities' AND f_geometry_column = 'pinpoint' AND ref_geometry = MakePoint(40.6500,35.8333);
worldcities|pinpoint||3|1|3572|0.0
worldcities|pinpoint||3|2|23964|60234.0327314011
worldcities|pinpoint||3|3|543|79537.9719636182

And here is where I´m stuck .. I don't really know what else I can do.

I also tried to compile from source SQLite (3.37) and Spatialite and same result.

Any help enabling KNN2 will be really appreciated, thanks!

Wade Z

unread,

Feb 11, 2022, 7:07:49 PM2/11/22

to SpatiaLite Users

I just figured this out after searching through the code. The latest downloadable version 5.0.1 does not include the KNN2 yet (from June of last year). You have to use the fossil SCM to get the latest source code and compile it.

http://www.gaia-gis.it/gaia-sins/about-fossil.html

Pedro Camargo

unread,

Mar 20, 2022, 6:43:18 PM3/20/22

to spatialite-users, wadeziegler

Hi Sandro,

Is there a timeline to releasing the next version of Spatialite (and it that will include the KNN2 capability)?

Thanks,

Pedro

---- On Sat, 12 Feb 2022 10:07:49 +1000 Wade Z <wadez...@gmail.com> wrote ----

--
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/8b24e760-16bc-423b-90e6-72b51b53339cn%40googlegroups.com.

Pieter Roggemans

unread,

Apr 6, 2022, 7:22:27 AM4/6/22

to SpatiaLite Users

I also wonder... will knn2 also work on files in geopackage format? For the current knn implementation this didn't work based on my experience...

Op zondag 20 maart 2022 om 23:43:18 UTC+1 schreef c...@margo.co:

wakumaku

unread,

Apr 19, 2022, 6:42:40 PM4/19/22

to SpatiaLite Users

Hi all again. Today I had some time to compile all from scratch and I can say it worked.

I did all the build and test in a docker container.

DOCKERFILE

------------------------

FROM alpine:3.15.4 AS builder

WORKDIR /workspace

ADD https://www.sqlite.org/2022/sqlite-autoconf-3380200.tar.gz sqlite-autoconf-3380200.tar.gz

# Install SQLite dependencies
RUN apk update && apk add --update --no-cache \
build-base \
readline-dev \
fossil

RUN tar xvf sqlite-autoconf-3380200.tar.gz \
&& cd sqlite-autoconf-3380200 \
&& ./configure --enable-math --enable-fts --enable-json1 --enable-rtree \
&& make -j8 \
&& make install

RUN cd sqlite-autoconf-3380200 \
&& gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS4 \
-DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 \
-DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS \
-DHAVE_USLEEP -DHAVE_READLINE \
shell.c sqlite3.c -ldl -lm -lreadline -lncurses -o /usr/local/bin/sqlite3

# Clone SpatiaLite source
RUN fossil clone https://www.gaia-gis.it/fossil/libspatialite libspatialite.fossil --user anonymous \
&& fossil clone https://www.gaia-gis.it/fossil/freexl freexl.fossil --user anonymous

RUN mkdir \
freexl \
libspatialite

# Install SpatiaLite dependencies
RUN apk update && apk add --update --no-cache \
minizip-dev \
libxml2-dev \
librttopo-dev \
proj-dev \
expat-dev \
geos-dev \
zlib-dev

RUN cd freexl \
&& fossil open ../freexl.fossil \
&& ./configure \
&& make -j8 \
&& make install

RUN cd libspatialite \
&& fossil open ../libspatialite.fossil \
&& ./configure \
&& make -j8 \
&& make install

FROM alpine:3.15.4 AS image

RUN apk update && apk add --update --no-cache \
libxml2 \
minizip \
proj \
librttopo

COPY --from=builder /usr/lib /usr/lib
COPY --from=builder /usr/local/lib /usr/local/lib
COPY --from=builder /usr/local/bin/sqlite3 /usr/local/bin/sqlite3

------------------------

$ docker build --tag spatialite:latest .

$ docker run --rm -it spatialite:latest sh

/ # apk add curl p7zip

/ # curl -o airports.7z https://www.gaia-gis.it/gaia-sins/knn/airports.7z

/ # 7za x airports.7z

/ # sqlite3 airports.sqlite

sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT CreateMissingSystemTables(1);

sqlite> SELECT * FROM knn2

...> WHERE f_table_name = 'airports' AND ref_geometry = MakePoint(10, 43) AND radius = 1.0;
MAIN|airports|geom||1.0|3|0|1|6299623|0.338817343121628|33043.3195204468
MAIN|airports|geom||1.0|3|0|2|6299392|0.683116233521059|65226.5731488238
MAIN|airports|geom||1.0|3|0|3|6299628|0.788669213865991|82387.0140281855