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?

• artwork21: Do you have a particular GIS desktop app in mind to do this in, knowing this will help focus answers?
• Senthil S: I prefer first only using postgresql and postgis functions. If not possible then next solution I prefer with QGIS plugins.
• yxcv: I guess FME meets your requirements. Or you can write a little python script.

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;

• Senthil S: Ya I was thinking of solution similar to this. Is there any other way apart from using shp2pgsql function? Like reading directly from shape files using wrapper class and updating using st_geomfromtext functions or so?
• Evan Carroll: @yxcv you should update the answer to use INSERT .. ON CONFLICT UPDATE. The question calls for update/insert, this is an ideal case.