How to get all the points that the distance between them is less than (some distance) in spatialite

Question

I am working with "SpatiaLite" and i have a table with a geometric column. this column is a BLOB with POINTS. for example to insert a point i do:

"INSERT INTO exampletable(geom) VALUES(GeomFromText('POINT(-101.1 46.6)', 4326))"

I want to get all points in this table that the distance between them is less than some distance (3000m for example).

I have in this table 1800 row with different points.

Thx

Answer 1

You can use the same table twice in your query. And the spatial index is very useful here.

Try with this (it's quite self-explaining):

SELECT a.* FROM point_table AS a, point_table AS b WHERE
    distance(a.geometry, b.geometry) < 3000 AND a.ROWID != b.ROWID AND
    b.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE
    (f_table_name = "point_table" AND search_frame = Buffer(a.geometry, 3000)))