0

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 ?

Many thanks.

Improve this question

2 Answers 2

Reset to default
3

You have a couple of problems within your query.

  1. The usage of CTEs you completely prevent using your geometry index, which I hope you have on gps_nodes.geog.
  2. The usage of ST_DISTANCE does not use your geometry index as well. Use something like ST_DWITHIN. By the way ST_DWITHIN with your CTE construct does not use the geometry index.
  3. (not performance) I hope you are aware of that your row_numbers will change for one concrete relation (more points, different work order)
  4. (not performance) A point could be in relation with itself.

Therefore you should try something like:

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 gps_nodes_1
INNER JOIN
    gps_nodes gps_nodes_2
ON st_dwithin(gps_nodes_1.geog,gps_nodes_1.geog,20)

If you want to limit the points to a specific area you should use a where in this SQL and not a CTE.

Improve this answer
2
  • Thanks, will give this a try. I notice there is nothing here to limit the GPS points to within 40km of the specified point, so I guess I could add an "AND ST_Distance(within..40km)" to the "ON" join ?
    – fatbadger
    Commented Mar 7, 2014 at 12:05
  • Awesome - it just completed in 51 seconds, and that's on a slow VM. Previously it had been running for over an hour before I stopped it! Thank you.
    – fatbadger
    Commented Mar 7, 2014 at 12:06
1

Add AND ST_DWithin("geog",ST_GeogFromText('POINT( -1.54790 53.79424 )'), 40000) to Select where distance is smaller than 40km , so it doesn not calculate distance to all points just for those it needs

And you probably can use just one gps_nodes_1 "table", far as i can see it is same query as _2

Improve this answer
3
  • Is that necessary, if the CTEs both already limit the data to points within 40km ? (Not saying you're wrong, just trying to understand). Also where were you proposing to add your condition in the query ? Thanks!
    – fatbadger
    Commented Mar 7, 2014 at 11:57
  • 1
    yes. ST_DWithin calculates distance faster than ST_Distance. When using ST_Distance it first calculates distance and then compares values. ST_DWithin uses index and bounding boxes. It is not accurate but is faster than really calculating distance.
    – simpleuser001
    Commented Mar 7, 2014 at 12:01
  • 1
    Look at my answer. Using of ctes prevents index usage.
    – wumpz
    Commented Mar 7, 2014 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.