4

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'"
Taras
  • 32,823
  • 4
  • 66
  • 137
Tamas Kosa
  • 1,507
  • 3
  • 15
  • 30
  • 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 '18 at 14:36
  • 1
    mh...my answer came in second...still I win for not answering in comments ,) – geozelot Apr 23 '18 at 14:53

1 Answers1

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"
geozelot
  • 30,050
  • 4
  • 32
  • 56
  • 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 07: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 '18 at 08:58
  • btw: check this answer and the related bash script in the question to batch import you files – geozelot Apr 24 '18 at 09: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 09:29
  • @TamasKosa nice! (you might want to remove the plain text password from the comment!) – geozelot Apr 24 '18 at 09:31
  • could you help me how I can put batch file variable into the sql statement? 'kolbasz' should be '%filename%' and AEY17525 should be also %filename% – Tamas Kosa Apr 24 '18 at 10:15