I have the following query, and am wondering how it could be optimised to run faster:
WITH gps_nodes_1 AS ( SELECT "NODE_ID", "geog" FROM gps_nodes WHERE ST_Distance("geog",ST_GeogFromText('POINT( -1.54790 53.79424 )')) < 40000 ), gps_nodes_2 AS ( SELECT "NODE_ID", "geog" FROM gps_nodes WHERE ST_Distance("geog",ST_GeogFromText('POINT( -1.54790 53.79424 )')) < 40000 ) SELECT row_number() over() as LINK_ID, gps_nodes_1."NODE_ID" as source, gps_nodes_2."NODE_ID" as target, ST_Distance(gps_nodes_1."geog",gps_nodes_2."geog")::numeric(7,1) as length INTO gps_node_links_neighbouring FROM gps_nodes_1 INNER JOIN gps_nodes_2 ON ( ST_Distance(gps_nodes_1."geog",gps_nodes_2."geog")::numeric(7,1) BETWEEN 0 AND 20 )
A description of what it does:
Table "gps_nodes" consists of ~2 million GPS positions. It has two columns, "NODE_ID" (an auto incrementing id) and "geog" which is a PostGIS geography point.
For each GPS node, I want to create a link to all other GPS nodes within 20 metres, and store that link (with start id, end id, and length) in a new table.
To start with, rather than try doing this for all GPS nodes, I want to limit it to those within 40 kilometres of a set point - which is around 90,000 records in the gps_nodes table.
So I started by creating two CTEs of the GPS points within 40km of required position. Then creating an inner join between the two CTEs, where the join condition is that the points are between 0 and 20 metres apart (need to be greater than zero distance, otherwise you always get a point linked to itself).
I freely admit that I'm an SQL novice and there may be far better ways of doing this!
Can anyone suggest a more optimised version of this query ?