Inserting spatial data into existing PostGIS geometry column from shapefile based on ID

Question

I have an existing PostgreSQL database table "city_data" with Non-spatial data in the following format:

City_ID as varchar, City_name as varchar, pop as int,....

I have enabled the PostGIS extension to this existing database and added a geometry column "the_geom" to "city_data" table with SRID and other essential details same as my shapefile.

Now I have a shapefile with city boundary as polygon and it also has city_id which is exactly same as PostgreSQL table.

I would like to load the polygon data from shapefile and insert/update into corresponding rows in PostGIS table based on city_id.

Is it possible to do directly or is there some workaround for doing this task?

Answer 1

I propose to split this task in three steps:

1. Import the shapefile in a temporary table.
2. Update the coulumn geom.
3. Delete the temporary table.

To import the shapefile in a temporary table (e.g. you call it temp) you can use a tool like shp2pgsql. It is included in the PostGIS installation. A tutorial is here avaible.

The following statement will update the geom column of your existing table city_data with the geom from the table temp.

UPDATE city_data a
SET a.geom = b.geom
FROM temp b
WHERE a.city_id = b.city_id;

At the end delete the table temp.

DROP TABLE temp;