2

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
3

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;
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.