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?

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

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.

| improve this answer | |
  • 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? – Senthil S Nov 5 '15 at 18:06
  • @yxcv you should update the answer to use INSERT .. ON CONFLICT UPDATE. The question calls for update/insert, this is an ideal case. – Evan Carroll Jan 12 '17 at 19:06

Your Answer

