1

I try to load my kml-s into an existing table (the geometry field is geometry type). One kml file can contains multiple geometry 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'"
  • 2
    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 '18 at 14:36
  • mh...my answer came in second...still I win for not answering in comments ,) – ThingumaBob Apr 23 '18 at 14:53
3

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"
  • 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 '18 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')? – ThingumaBob Apr 24 '18 at 8:58
  • btw: check this answer and the related bash script in the question to batch import you files – ThingumaBob Apr 24 '18 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 '18 at 9:29
  • @TamasKosa nice! (you might want to remove the plain text password from the comment!) – ThingumaBob Apr 24 '18 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.