3

I try to load my KMLs into an existing table (the geometry field is geometry type). One KML file can contain multiple geometries and I would like to add these to an existing table (each feature is a new record) with an "ID" which should be the KML file's name. I have a partly working code, but I want to add the filename to the "ID" field as well

ogr2ogr -append -f "PostgreSQL" PG:"host='0.0.0.1' port='5433' user='user' dbname='dbname' password='password'" h:\Users\gpapp\kmls\AEY17525.kml -nln cat_projects_geom

this one generates error:

ogr2ogr -append -f "PostgreSQL" PG:"host='0.0.0.1' port='5433' user='user' dbname='dbname' password='password'" h:\Users\gpapp\kmls\AEY17525.kml -nln cat_projects_geom -sql "UPDATEcat_projects_geom SET nameref = 'AEY17525 or filename'"
Improve this question
2
  • 3
    You can run SQL update only with ogrinfo. You must either use ogr2ogr with SQL that does everything or insert data first with ogr2ogr and update is with ogrinfo afterwards. You ogr2ogr command could have something like -sql "SELECT geometry, attr_1, attr_2, attr_3, 'AEY17525' AS nameref from my_kml_layer".
    – user30184
    Apr 23, 2018 at 14:36
  • 1
    mh...my answer came in second...still I win for not answering in comments ,)
    – geozelot
    Apr 23, 2018 at 14:53

1 Answer 1

Reset to default
4

Yes, try

ogr2ogr -append -f "PostgreSQL" PG:"host='0.0.0.1' port='5433' user='user' dbname='dbname' password='password'" h:\Users\gpapp\kmls\AEY17525.kml -nln cat_projects_geom -sql "SELECT <col_a>, <col_b>, ..., 'AEY17525' AS nameref FROM AEY17525"
Improve this answer
6
  • I am not sure how this is working? Are we selecting from our existing table? Or we using sql query on the kml? How do we call my_kml_layer in the from section? I am getting error: Unable to open secondary datasource 'AEY17525' required by JOIN
    – Tamas Kosa
    Apr 24, 2018 at 7:26
  • it's supposed to be the infile name to select from. did it work (I updated the answer right after posting to clarify by replacing 'my_kml_layer' with 'AEY17525')?
    – geozelot
    Apr 24, 2018 at 8:58
  • btw: check this answer and the related bash script in the question to batch import you files
    – geozelot
    Apr 24, 2018 at 9:18
  • ogr2ogr -append -f "PostgreSQL" PG:"host='0000' port='5433' user='postgres' dbname='catsurveys_hun' password='ooo'" h:\Users\gpapp\kmls\AEY17525.kml -nln cat_projects_geom -sql "SELECT 'kolbasz' AS nameref from AEY17525" This one worked (there was an active trigger which caused problem.
    – Tamas Kosa
    Apr 24, 2018 at 9:29
  • @TamasKosa nice! (you might want to remove the plain text password from the comment!)
    – geozelot
    Apr 24, 2018 at 9:31

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.