POSTGIS - update table from another table

Question

I have created a polygon table (poly) with name and Points (using polygon values)

"Z";"010300000001000000050000008D67A2989451494092D9774AA9990840D757B77796514940469AC495BD99084092772ABA97514940A7A9214E80990840131D769D95514940E65E4E736C9908408D67A2989451494092D9774AA9990840"

"A";"010300000001000000050000006260C20D9D5149402CC94B48B5990840862238899C514940FCF785FCD89908402A183A1B97514940F2295B7FA69908401893A3A597514940CF447D37829908406260C20D9D5149402CC94B48B5990840"

I have another table (latlon) with lat and lot columns for more than million records

 lat            lon 
50.6375524  3.075079145
50.6374046  3.075292678
50.6373605  3.075188391
50.6373628  3.075185017

I have to update a column (zonename) in latlon table with the help of point in "poly" table

The following select statement is giving correct output for one record.

SELECT name FROM zone WHERE ST_Contains(polygon, ST_GeomFromText('POINT(50.6375524  3.075079145)'));

Can someone help me to write an UPDATE query in POSTGIS to do this for more number of lat,lon values?

Patrick Patrick · Accepted Answer · 2016-08-15T16:32:51

UPDATE latlon
SET zonename = zone.name
FROM zone
WHERE ST_Contains(zone.polygon, ST_SetSRID(ST_MakePoint(lon, lat), 4326));

This assumes your coordinates are in WGS84 (i.e. raw GPS coordinates). Your polygons should be in the same coordinate system; if not, change the SRID value of 4326 to whatever your polygons are using.

POSTGIS - update table from another table

1 Answers