1

I'd like to get the nearest feature to a given point in Spatialite using a spatial SQL query. I'd like to speed it up using index table. Spatial index boundary should be calculated from defined point and a given tolerance, all features that are totally/partly within rtree bounding box should be used in the query.

I have tried several way, but I had always problem with spatial index boundary.

Like this:

SELECT *, Distance(GeomFromText('POINT(19.02658 47.51574)'),mo_utak_polyline.Geometry) as distance FROM mo_utak_polyline WHERE ROWID IN (SELECT pkid FROM idx_mo_utak_polyline_Geometry WHERE xmin > 19.01408 AND xmax < 19.03908 AND ymin > 47.50324 AND ymax < 47.52824) AND distance <=0.0025) ORDER by distance

Or this:

SELECT *,Intersects(GeomFromText('POINT(19.02658 47.51574)'), megyehatar_region.Geometry) as intersects FROM megyehatar_region WHERE ROWID IN (SELECT pkid FROM idx_megyehatar_region_Geometry WHERE xmin > 14.02658 AND xmax < 24.02658 AND ymin > 42.51574 AND ymax < 52.51574) AND intersects=1

I can always get features that are totally contained by my index defined bounding box. It causes a huge problem for me, e.g. when I try to query line features, when their length is totally different, it can be 1 cm or even 1000 km, so it is hard to set the size of spatial index bounding box.

Which do you think the best way to do it?

How to change this part of the query

SELECT pkid FROM idx_mo_utak_polyline_Geometry WHERE xmin > 19.01408 AND xmax < 19.03908 AND ymin > 47.50324 AND ymax < 47.52824)

to return not only the features that are contained by the bounding box, but also those intersects with it?

Thanks in advance!

Tom
  • 3,899
  • 22
  • 78
  • 137

4 Answers4

0

Make first query to index, than you can do intersects, and, even better, distance == 0 to the geometries you've got from indexed query:

Your point coordinates - X,Y 

    SELECT * FROM table 
        WHERE pk_uid IN (SELECT pkid FROM idx_table_geometry 
                         WHERE xmin < X AND ymin < Y AND xmax > X AND ymax > Y) 
              AND distance( makepoint(X,Y), geometry ) == 0
Oleg Shanyuk
  • 1,296
  • 2
  • 15
  • 26
0

This thread is a bit old (in the meantime, handling of spatial index seems to have changed a bit in spatialite), but here's what I have come up with in my project just now (year 2015). The query is supposed to take each point from a pointlayer and find the closest line from a line layer.

I'm not sure how well structured this code is and how fast it will compute against a large dataset (my test-set is small).

I'm highly interested in getting feedback, if you see improvement in this query (my sql is very rusty – it's been a while...)

select * 
from pointlayer as p
left join linelayer as l on
    Distance(p.geometry, l.geometry) in (
       select MIN(Distance(p.geometry, geometry)) 
       from linelayer
       where pk in (
           select rowid
           from SpatialIndex
           where f_table_name = 'linelayer'
           and search_frame = BuildCircleMbr(X(b.geometry), Y(b.geometry),25))
)
KaeptnHaddock
  • 1
0

In SpatiaLite version 4.4 or greater there is now a KNN (K-Nearest Neighbors) index that works quite well. I recently wrote a query that for each of about 500 points looked for the nearest linestring from 5 million line records. Using the newer VirtualSpatialIndex method mentioned by KaeptnHaddock the query ran for about 3 minutes. With the new KNN index the query took less than 20 seconds. Here is my KNN query:

select k.* from knn k, points p
WHERE f_table_name = 'linestrings' 
AND ref_geometry = p.geometry
AND max_items = 1;
Greg Krakow
  • 1
0

How about the following:

SELECT count(*) FROM idx_mo_utak_polyline_Geometry WHERE 
MBRContains(BuildMBR('19.01408','47.50324' , '19.03908', '47.52824'), BuildMBR(xmin,ymin , xmax, ymax)) OR
MBRIntersects(BuildMBR('19.01408','47.50324' , '19.03908', '47.52824'), BuildMBR(xmin,ymin , xmax, ymax))
eeerahul
  • 1,629
  • 4
  • 27
  • 38
jared
  • 141
  • 2
  • 8