0

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)
  • Please help us to help you by providing the SQL you have written so far. – Vince Aug 8 '17 at 20:05
  • apologies, done – user2581350 Aug 8 '17 at 22:23
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)
  • thanks, although this is the error I get: ERROR: more than one row returned by a subquery used as an expression – user2581350 Aug 8 '17 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 '17 at 11:00
  • this query does not look right where is poly.geom coming from? – ziggy Aug 9 '17 at 13:04
  • Code edited and verified... – Pierre Racine Aug 9 '17 at 21:55

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.