0

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

Liraz Haim
  • 3
  • 4
  • Do you know how to compute the distance in SpatiaLite? Do you know what a self join is? – CL. Jun 18 '15 at 17:36

1 Answers1

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)))
Sga
  • 3,608
  • 2
  • 36
  • 47
  • HI I can not test it because I working with WGS84 coordinates and the distance is in degrees, how can I make conversion in spatialite? – Liraz Haim Jun 21 '15 at 05:12
  • If your UTM fuse is fixed you can transform the geometry with `Transform(a.geometry, SRID)` (SRID being something like 32632). On a global scale you must transform the distance, see [here](http://stackoverflow.com/questions/25237356/convert-meters-to-decimal-degrees) for some insights – Sga Jun 21 '15 at 06:19
  • I try to run this qury but this throw an exception _no such table: SpatialiteIndex_ but i run this `SELECT CreateSpatialIndex('points', 'geometry');` – Liraz Haim Jun 21 '15 at 14:07
  • Yes, you can either create a spatial index or discard the second part of the query – Sga Jun 22 '15 at 08:12