0

For example, I have this query to find the minimum distance between two geometries (stored in 2 tables) with a PostGIS function called ST_Distance.

Having thousands of geometries (in both tables) it takes to much time without using ST_DWithin. ST_DWithin returns true if the geometries are within the specified distance of one another (here 2000m).

SELECT DISTINCT ON
    (id)
    table1.id,
    table2.id
    min(ST_Distance(a.geom, b.geom)) AS distance
FROM table1 a, table2 b
    WHERE ST_DWithin(a.geom, b.geom, 2000.0)
        GROUP BY table1.id, table2.id
        ORDER BY table1.id, distance

But you have to estimate the distance value to fetch all geometries (e.g. stored in table1). Therefore you have to look at your data in some way in a GIS, or you have to calculate the maximum distance for all (and that takes a lot of time).

In the moment I do it in that way that I approximate the distance value until all features are queried from table1, for example.

Would it be efficient that my query automatically increases (with a reasonable value) the distance value until the count of all geometries (e.g. for table1) is reached? How can I put this in execution? Would it be slow down everything because the query needs maybe a lot of approaches to find the distance value?

Do I have to use a recursive query for this purpose?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Stefan
  • 1,383
  • 2
  • 15
  • 25
  • I have read your question twice and still have no idea, which problem you are trying to solve exactly. Just some things you tried - but to achieve ... what? If you want to "select all geometry features from one table", just select the whole table ... You may be looking for a **[nearest neighbour search](http://dba.stackexchange.com/questions/60619/order-by-distance/60711#60711)**. – Erwin Brandstetter Mar 26 '14 at 18:27
  • @JohnBarça: It's the third parameter of the ST_DWithin function. – Stefan Mar 26 '14 at 20:48
  • Stefan, sorry, yes, corrected the typo -- I was not allowed to edit, so I recreated. @Erwin, he is asking about a way to gradually increase the 3rd parameter to the ST_Distance function until all the points' nearest neighbours have been found. This is a common problem as if you set ST_DWithin too high, execution time will be horrible, but if you set it too low, you might not find any neighbours at all. – John Powell Mar 26 '14 at 20:52

1 Answers1

0

See this post here: K-Nearest Neighbor Query in PostGIS Basically, the <-> operator is a bit unusual in that it works in the order by clause, but it avoids having to make a guess as to how far you want to search in ST_DWithin. There is a major gotcha with this operator though, which is that the geometry in the order by clause must be a constant that is you CAN NOT write:

select a.id, b.id from table a, table b order by geom.a <-> geom.b limit 1;

Instead you would have to create a loop, substituting in a value above for geom.b

More information can be found here: http://boundlessgeo.com/2011/09/indexed-nearest-neighbour-search-in-postgis/

Community
  • 1
  • 1
John Powell
  • 12,253
  • 6
  • 59
  • 67
  • (at)John:I've occupied myself with the Indexed Nearest Neighbor Search in the first place. The query in the example (link) uses only one point and this works properly. But when I have more points and execute an NN search with linestrings, id doesn't work for more features. ... you interchange geom.a (a.geom)! – Stefan Mar 26 '14 at 21:13
  • Indeed. You have to write a loop, and substitute the geometry to the right of the operator in each time. It would be nice if the example above worked, but it is not possible to do it that way, as I said. – John Powell Mar 26 '14 at 21:20
  • I wish it is all that easy. I'm afraid that I can't implement this. I was thinking about a function by which you can loop this. – Stefan Mar 26 '14 at 21:35
  • @Stefan, Ok, this is something I need to play with too. I will try out some test datasets and try and come up with some comparative metrics for the case you outline with expanding ST_DWithin and a loop using the <-> operator. Will let you know when I have something, unless someone else answers sooner. – John Powell Mar 26 '14 at 22:17