2

This is related to this question, but different because it's about PostGis, not Django.

I'm using Postgres 9.4 with PostGIS extensions. I have a KML file, and I want to use it to update the geographic field of an existing table. This is my table:

               Table "public.frontend_pct"
      Column       │          Type           │ Modifiers
───────────────────┼─────────────────────────┼───────────
 code              │ character varying(3)    │ not null
 name              │ character varying(200)  │
 boundary          │ geometry(Geometry,4326) │
 org_type          │ character varying(9)    │ not null
 managing_group_id │ character varying(3)    │
 address           │ character varying(400)  │
 close_date        │ date                    │
 open_date         │ date                    │
 postcode          │ character varying(10)   │

Is it possible to use ogr2ogr (or any other utility) to update my existing table's boundary field, but leave everything else intact?

My KML file has boundary and ID fields. I would like to join the ID field in the KML with the code field in the file. I can edit the names of these fields if needed.

ogr2ogr does seem to have an update option. I've been trying this:

gr2ogr -update -nln frontend_pct -f PostgreSQL PG:"host=127.0.0.1 dbname='$DB_NAME' user='$DB_USER' password='$DB_NAME'" myfile.KML

But I get this error:

FAILED: Layer frontend_pct already exists, and -append not specified.
        Consider using -append, or -overwrite.
ERROR 1: Terminating translation prematurely after failed
translation of layer Layer #0 (use -skipfailures to skip errors)

Firstly, how do I update an existing table? Secondly, how do I specify the fields to join on, or do I just need to rename my KML fields?

  • Did you try adding -append? Check the example over here: gdal.org/drv_pg.html – tilt Feb 16 '16 at 20:17
  • Why didn't you use the -overwrite flag if what you want to do is update the table? – raphael Feb 16 '16 at 22:34
  • @raphael tried -overwrite, but it nukes the existing table rather than updating it! I want to leave rows that aren't in the KML in place, and just update any rows that have matches in the KML. – Richard Feb 17 '16 at 10:17
  • Sorry about that. Worth a try. It might be helpful to explain as clearly and explicitly what you're trying to do here. How is the new data different from the old data? Are you trying to automate this "update " process? – raphael Feb 17 '16 at 15:35
  • @raphael Yes, I want to update regularly. The new data has fields called 'geom' and 'id' - I want to match my 'code' field with the 'id' field in the data, and then replace the content of my 'boundary' field with the value of the 'geom' field in the data. – Richard Feb 18 '16 at 11:36
2

So I think the ideal PostgreSQL solution would be to separate the operation into two stages.

  1. Use ogr2ogr to convert the new data into a PostgreSQL table called stage_table.
  2. Call a PostgreSQL function to update your frontend_pct table like:

    UPDATE frontend_pct
    SET boundary = geom
    WHERE code = id
    FROM stage_table;
    TRUNCATE stage_table;

1

New to the boards and have just begun planning my 1st spatial project in years, so i was looking into your original question yesterday, which was a great refresher for me on: the OGC standards; the GDAL/OGR, GEOS, and PostGIS geospatial libraries; and the Google Developers docs.

I was surprised to see that GIS folks are still hacking away to extract/convert KML but (given the nature of spatially-enabled XML which allows for geometries of different type & extent to spread out across multiple folders, tucked in among representation parameters and Earth viewer settings) it was clear after an hour of digging that data tools have matured nicely since I was last in the game.

I think the you can update via ogr2ogr but may have to wrap it in the "-sql" option if you opt not to use "-update -append". About the -append -overwrite -update options: I have confused these SOFTWARE TOOL NAMES with their every day meanings for years (yikes) until grasping their dependent/independent functionality...today. Per the docs:

-append: Append to existing layer instead of creating new

-overwrite: Delete the output layer and recreate it empty

-update: Open existing output datasource in update mode rather than trying to create a new one

I scratched my head looking at website sample use cases until the answer appeared (where else?) on the Boston GIS OGR2OGR Cheatsheet, which says:

Note in the above we needed to put the -update -append option because OGR2OGR will try to create a folder if given a file with no extension, which translates to creating a new database.

I knew it wasn't possible to create a new PostgreSQL DB with ogr2ogr. Going back to the definitions, it's super clear that -append and -overwrite refer to layers while -update refers to the data source. That's why we can see the table-level options with the db-level, and we never see -update on its own.

Whew. Back to the update PG DB from KML issue. It appears there are several different combinations of tools (-SELECT with a join, -SELECT with -WHERE, -sql, -fieldmap, etc.) that will succeed but i'll have to edit later with some samples that account for the xml structure of the data were shredding for the update.

EDIT: There may be two steps required after all, given that the -update option points to the data source and not the table itself. So something like:

ogr2ogr -update -append frontend_pct -sql "SELECT boundary FROM <path to>myfile.kml LEFT JOIN OUTER JOIN frontend_pct ON frontend_pct.code = myfile.kml.ID"  PG:"host=127.0.0.1 dbname='$DB_NAME' user='$DB_USER' password='$DB_NAME'" myfile.kml

will probably either fail or create new records, blank except for geometries. One very surprising possibility, however, invokes ogrinfo instead of ogr2ogr:

ogrinfo frontend_pct -dialect SQLite -sql "SELECT boundary FROM <path to>myfile.kml LEFT JOIN OUTER JOIN frontend_pct ON frontend_pct.code = myfile.kml.ID"  PG:"host=127.0.0.1 dbname='$DB_NAME' user='$DB_USER' password='$DB_NAME'" -dialect SQLite -sql "UPDATE frontend_pct SET boundary = <work out path to/parsing of kml> (SELECT boundary FROM <path to>myfile.kml) WHERE frontend_pct.code = myfile.kml.ID

Never worked in SQLite so you'll have to work out the query, but I think you might actually get a one-step update using ogrinfo instead of ogr2ogr. Surprising.

0

Apparently there is a SQLite SQL dialect. "Because UPDATE is not supported in OGR SQL ... you should update the table using the SQLite SQL dialect available in GDAL >= 1.10 with SQLite and SpatiaLite support:

ogrinfo $myfile -sql "ALTER TABLE $name ADD COLUMN code_num integer(3)"

ogrinfo $myfile -dialect SQLite -sql "UPDATE $name SET code_num = CAST(code_06 AS integer(3))"

If this does not work, then you may have to load the data into a temporary table and update your target table from the load table. An interesting idea to research is the federated approach. "SQL/MED" is a way to use a table over an external file. PostGIS supports using ogr2ogr in a table via "Foreign Data Wrappers".

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.