CODEWITHSUNDEEP
postgresqlpostgis
Boppa
Boppa

Reputation: 23

PostgreSQL: Add cities to points by ST_Contains()

I have a table with 2 columns:

-geom : (Lat/lang pairs in this from) ST_SetSRID(ST_MakePoint(longg,lat),4326)

-city : an empty column

I have an imported shape file from a country(as a table with polygons), and i would like to add each geom point an exact city in to the city column with ST_Contains().

For each geom point, check every city polygon and if it is an inside point add the city name to the city column.

Q: How can i make this structure(the nested for cycle)?

Upvotes: 0

Views: 876

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

A normal UPDATE FROM should work. Assuming the tables shp contains columns city_geom and city_name.

UPDATE tbl
SET city=s.city_name
FROM shp AS s
WHERE
ST_CONTAINS(s.city_geom,geom);

Upvotes: 1

Related Questions