2

I have a table called sites of polygons, and a table called postcodes of points. For each row in sites I need to find the nearest point in postcodes, then update the sites column postcode from column pc in postcodes. Can anyone please help me write this in PostGIS?

This gets all the correct values, I just don't know how to do an update that can read these straight into the postcodes column:

SELECT DISTINCT ON(g1.key_column)  g1.key_column As key_column, g2.pc As 
postcode
FROM sites As g1, postcodes As g2   
WHERE g1.key_column <> g2.key_column AND ST_DWithin(g1.geom, g2.geom, 
1000)   
ORDER BY g1.key_column, ST_Distance(g1.geom, g2.geom)
2
  • Please help us to help you by providing the SQL you have written so far.
    – Vince
    Aug 8, 2017 at 20:05
  • apologies, done Aug 8, 2017 at 22:23

1 Answer 1

1

Try something like this:

UPDATE tableA tA1 SET pc = 
(SELECT near_point.pc
FROM tableA tA2, 
LATERAL (SELECT pc 
         FROM tableB 
         ORDER BY tableB.geom <-> tA2.geom 
         LIMIT 1) near_point
WHERE tA2.id = tA1.id)
4
  • thanks, although this is the error I get: ERROR: more than one row returned by a subquery used as an expression Aug 8, 2017 at 21:51
  • Encapsulate the whole subquery in brackets and include tableA.id in the first SELECT statement. Then the last line would need to be WHERE tableA.id = subquery.id.
    – Liam G
    Aug 9, 2017 at 11:00
  • this query does not look right where is poly.geom coming from?
    – ziggy
    Aug 9, 2017 at 13:04
  • Code edited and verified... Aug 9, 2017 at 21:55

Your Answer

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

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