2

I have a table 'mytable' with columns 'Geometry', 'featurecode', 'height' and 'pkey'. There is a spatial index and im using PG9.4

For most of the table the height column is null. Only where featurecode=2 is there a height value.

I am trying to find an efficient way to update records where featurecode=10 with the height of the closest feature of type 2. This is a very large table so im thinking I may need to limit the search to items within distance of X meters

Has anyone tried doing something similar?

3

Essentially what you're wanting to solve is a nearest neighbour problem, and the main part of your query should look like the following:

SELECT DISTINCT ON (t1.gid) t1.gid, t2.height FROM mytable AS t1, mytable AS t2 WHERE t1.featurecode = 10 AND t2.featurecode = 2 ORDER BY t1.gid, ST_Distance(t1.geom, t2.geom);

Basically what is happening here is a self join with some filtering done to limit the fields that are being updated, and to limit the fields that are used to do the updating. Additionally, the "DISTINCT" argument insures you're only getting one height record back per gid (i.e. the pkey), and ordering by distance at the end ensures you're getting the closest record. If you have a large table and want to speed things up, and you know that your records will be within a certain distance of each other, then you can limit the distance used when searching for the nearest neighbour. That would look like the following, assuming a distance of 500 metres:

SELECT DISTINCT ON (t1.gid) t1.gid, t2.height FROM mytable AS t1, mytable AS t2 WHERE t1.featurecode = 10 AND t2.featurecode = 2 AND ST_DWithin(t1.geom, t2.geom, 500) ORDER BY t1.gid, ST_Distance(t1.geom, t2.geom);

Then do use this temporary table to update your table, put this in an UPDATE script, like this:

UPDATE mytable AS main SET height = temp.height FROM (SELECT DISTINCT ON (t1.gid) t1.gid, t2.height FROM mytable AS t1, mytable AS t2 WHERE t1.featurecode = 10 AND t2.featurecode = 2 AND ST_DWithin(t1.geom, t2.geom, 500) ORDER BY t1.gid, ST_Distance(t1.geom, t2.geom)) AS temp WHERE main.gid = temp.gid;

Try that out. It should do the trick.

  • Spot on answer. If only the <#> operator was more flexible. – John Powell Mar 15 '16 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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