0

I have many many points and want to calculate the shortest distance from each point to the closest line of a table with many many lines. I think my underneath code is correct, I however never get an answer because pgadmin just keeps 'waiting for query execution to complete'. How can I optimize this underneath script?

SELECT 
    max(br.id), max(to_timestamp(br.time)), max(at.linknummer), 
    min(ST_Distance(st_transform(br.geom,3857), at.geometry))
FROM
    schema1.data1 AS br,
    schema2.data2 AS at 
GROUP BY
    br.id, br.time
geozelot
  • 30,050
  • 4
  • 32
  • 56
Zuenie
  • 585
  • 2
  • 7
  • 19
  • No, your code is not correct. It is a worst-case polynomial expansion with reprojection into a coordinate system which is completely inaccurate for distance. Please [Edit] the question to specify the exact row counts, the srid of the tables, the EXPLAIN plan for your query, and an indication of the worst and average case distances between points and their nearest lines. – Vince Jan 15 '18 at 11:55
  • You need to use something in the WHERE clause to limit the search, such as ST_DWithin, ST_Intersects. There are lots of questions on here with a similar question -- search for K nearest neighbours (knn) or similar. What you have is a Cartesian product. – John Powell Jan 15 '18 at 13:49
  • how´s your queriying going? was I able to help? – geozelot Jan 19 '18 at 14:03

1 Answers1

3

I will give you a possible solution, although I´m sure there are plenty similar questions out there and, as suggested in the comments, you should have come up with some details about your data and software...

Also, listen to @Vince and choose a projection suitable for distance measurements for your area of interest, maybe using geography type even, and maybe update your geometry column (or create an additional geometry/geography column) in advance. I will not provide any reprojection in my solution and use the column identifiers as per your question; alter the commands accordingly if you want to use different geometry/geography columns.

First off, make sure you have your tables' geometry indexed properly, otherwise this will probably run until the end of time:

CREATE INDEX schema1.data1_gix ON schema1.data1 USING GIST (geom);
CREATE INDEX schema2.data2_gix ON schema2.data2 USING GIST (geometry);


To update the internal table statistics for the query plan estimation to take the indexes properly into account, run:

VACUUM ANALYZE schema1.data1;
VACUUM ANALYZE schema2.data2;


Then, to actually find the nearest line to your points, you can use the LATERAL JOIN, executing a subquery for each consecutive row in the main query; per definition this will scan the entire table defined in the subquery once for each row in the table defined in the main query ('eternity' is just a small scale if you do this without indexes...)
To actually use the index efficiently, use the <-> operator instead of ST_Distance() in the ORDER BY statement (the docs on <-> does say it's index search will kick in if also used with a constant only; in this case, I guess, each row of the main query given to the LATERAL JOIN as it´s parameter is considered as a constant, thus it will run an index scan on the table in the subquery):

SELECT br.id,
       to_timestamp(br.time),
       subquery.linknummer,
       subquery.dist
FROM schema1.data1 AS br
JOIN LATERAL (
    SELECT at.linknummer,
           at.geometry,
           St_Distance(at.geometry, br.geom) AS dist
    FROM schema2.data2 AS at
    ORDER BY br.geom <-> at.geometry
    LIMIT 1
) AS subquery
ON true


I used your identifiers as per your question in numerical order; make sure the table in the subquery is the one with the lines.

This query needs around 6 seconds to execute, with 150.000 rows in the main query's table and 75.000 rows is the subquery's table on my mid tech machine.

geozelot
  • 30,050
  • 4
  • 32
  • 56
  • doesn't the syntax have to be cross join lateral? – ziggy Jan 16 '18 at 17:01
  • @ziggy either as above or CROSS JOIN LATERAL without ON true. JOIN ... ON true should be equivalent to CROSS JOIN for all join types AFAIK. I use to get around 10% faster execution using JOIN...ON true within a simple query as above compared to the CROSS JOIN ... ...but I can't say why. – geozelot Jan 16 '18 at 17:34
  • @ziggy according to the docs (see section cross joins), it's equivalent to INNER JOIN ... ON true, which does, of course, make sense... – geozelot Jan 16 '18 at 17:45
  • cool, the on true clause always threw me off. Not sure why there would be the cross join and the inner join... on true – ziggy Jan 17 '18 at 15:17