The ogrtools Python library lets you run complex ogr2ogr operations defined in a configuration file supporting transformations of all OGR vector formats. It uses the OGR Virtual Format (VRT) internally for transformations like renaming tables or columns, calculating values and converting data types. Since version 1.11 of GDAL/OGR, multiple geometry columns per table are fully supported for major data formats.As a pure Python library using OGR Python bindings, it has no additional dependencies and is therefore easy to integrate in other applications like
 Hello, I'm ready to start and talk a little bit about OGR and an extension for OGR and doing ETL with OGR About myself my name is Benjamin Calvader. I work for SOS Paul, a space-based company doing mainly development for QGIS and providing services support for QGIS and We use as almost everyone uses OGR GDL and That's why I'm talking about OGR as well here ETL is Extract Transform Loads everyone doing GAS is working with geo data there are quite a few Applications available for doing ETL there are certain applications on a higher level like hail, geocatel, talent, geo extensions so you have a desktop user interface where you can make models for your data transformations and so if you want to work on that level then I Would suggest to have a look at geocatel. I like that one very much But in my case I need like a library which is embeddable in QGIS for instance, so this is not an option for me. I want to build Lower level libraries GDL OGR is one of them and There are other possibilities you can do a lot of things directly with Poch AS or if you're handling XML files You can do XSLT There are many possibilities But For us it was we wanted to build on OGR so I explained first how you do transform data with OGR there is OGR to OGR the program that executable which many people know there is even a desktop GUI tool for Helping with that and there are plugins and things helping with using OGR to OGR and To understand how it works you have input data and then you have options which apply to the input like applying an SRS or a layer name or a layer type geometry type and then it is read or Then it is the file is read and it is in the OGR data model and Then you have output options the most important one is the format you want to have and you can transform it or apply filters and so on But it is important to understand what this OGR data model supports it is quite a simple model it is Attribute fields so number of fields with types it uses a feature identifier which you can Which is configurable and it had for a long time one geometry field and now since 111 it has multiple geometry fields. This is an important improvement and it was financed by Swiss Agency because of that project and because of support for Swiss format interleaves which need support for multiple geometries per layer Next step next level when using OGR to OGR is using the OGR virtual format this is not so well known and This is good documentation on the OGR site and it was made I think Frank can correct me to create spatial layers from from Text files means like CSV or something or flat tables without Geometry format but with let long coordinates or something similar and then you can Make a configurate file like the XML down there where you can there you can tell which fields or which attributes are forming the geometry so the X and Y attribute and using this you can look at At the non spatial layer with spatial tools and If you look at this graphical representation you see that this VRT is the input for OGR to OGR but it Points to the to the input data and it replaces these input options for reading Data and you can apply the other OGR to OGR options for creating our data or another format This virtual format has grown over the years. It has many possibilities many hidden possibilities or not so well-known possibilities you can Obviously define the geometry fields you can change column names You can change column types or map them You can omit columns and you can apply spatial filters. You can reproject layers and and you can instead of having Fixed input layer you can have as well OGR as well expression which could be native SQL for postures or it can be SQL applied to shapefiles even Which are then executed by OGR and this gives you the poly possibility to have like calculated fields fields with expressions or doing joints and other things so this is quite useful and We build on on that a new stat for For our purpose and now what I want to show you is this Python extension we using the Python API of OGR and and we collect some functionality of OGR info and OGR to OGR and but also Python implementations for which are included in the OGR distribution We provide a single binary for command line usage which makes More uniform the usage and we have a JSON configuration file Which has Similar content as the VRT just shown but with additional parameters for like the OGR OGR to OGR Command line parameters and And in this OGR tools also included is a QGIS plugin and That wanted here is that because OGR is already included in QGIS. There is no additional dependency, so if we use the Python API of OGR, we don't have to run an external Executable, but we have we can use all the functionality of OGR or via Python API. This graphic should explain how it works, so The new part is on the right side is this OGR configuration, which is this JSON file I mentioned and from this temporarily created VRT is used for as an input for OGR which points to the input data and input options and output options are included in this OGR configuration, so the idea is that you Can put your OGR to OGR parameters in one configuration file. That's one idea So this is a Python library and it is installed like Python libraries Usually are installed with its PIP install OGR tools and then you can use the command line I Would like to show it on the bash directly in the console But I have to switch first Because I should see it as well So So I've installed it obviously and I can call this OGR executable which is also a Python file and Here you can see the commands which are available. I can look for a version of OGR the OGR library installed Okay, you don't see the button Yeah, that's good enough, okay What your version or your formats think you can do with regular OGR commands Oh, where is my script? Sorry Okay, just a second Okay Next thing is I want to have something like OGR info I have some test file included So that's similar outward or the same output as you get when you use OGR info as Executable But this one is implemented in in Python which wasn't done by me which is also included into OGR distribution There is a Python implementation of OGR info and I'm using this one Another feature is Applying as OGR SQL expressions on data. This is all the possible with OGR info, but my goal was to have one Point or one entry point for all these kind of commands so I Do an SQL expression on the same on this shapefine Okay, now the SQL query so I Had the info so I know I know which attribute I have So what I do now I do Select operation you can see it. It's bottom I do that first oh, huh railway is that correct I Need type or here I go to the info back here I see the name of the layer and see all the the column names So I made The layer name is correct So did I make a mistake here type was my name? That's I do OGR SQL which is the same as a This as you can do with OGR info. That's what I do and what it should do In my example I've even something Like that Hmm I can't see the mistake right now, but I Hope you believe me that you would get The same as That's when you do OGR info It is select I think a query Country memory is it Yes on the OGR site you have documentation what kind of SQL you can do and the SQL is Interpreted differently when you do it on sources like shapefiles, then you have limited SQL support and we do it on on post keys it is native approaches SQL or on SQLite or It's also native SQL Next thing which is included is helper for generating this VRT files Which is useful is for one to Oh that was So that was also that the SQL problem What's that We know that that's that's the same output as OGR info gives you if you do a nice call query on on a OGR input type and So you can add where close and so on so the problem was this from File name and if we do this OGR VRT we get A valid VRT Which does include the same information as OGR info has which gives you a starting point for changing data types or changing Field names and so on so this is Useful is if you want to work with VRTs And next step is now to have a configuration file for doing this OGR tools transformation. So what I do is another command Generate config which is similar to this VRT configuration generator. I Tell what What the preferred destination format is so this is different to this VRT Because I want also to have the output parameters of OGR to OGR included in the configuration file I don't have to convert to this format, but it's It's optimized that default parameters are optimized for the this default format So the same again I use this shapefile. Okay, so this is how the This configuration file looks like and Some parameters look familiar. So this are OGR to OGR parameters which are applied When I execute the transformation and which can be changed Your source format and destination format we have The same thing as we had in in the VRT the field types and so on and We have geometry type layer name and Geometry field specification Juniper empty but I ignore that for now I write that into a file Or now let's call it configuration And now I can use that for a transformation so next step is OGR transform Ah look strange, yeah But here the executable helps I can always press enter and look what's missing so I Add format I Want I don't have to add because I go to geochasing first At the input file And the output file and it's the same order as OGR to OGR destination and source so the output will be real way to chasing Oops a little mistake. What is it? I Forgot the configuration fine This one is not optional so what I got now is railway.json Which is the geochasing file Made from the shape file What does this mean Good, okay And to play a little bit I can edit the configuration And let's say I saw that this was MIT was Floating point number which makes not so much sense So I do the same again with this new configuration oops Geochasing driver that's originally OGR to OGR does not overwrite existing files So either I had to add the options for overwriting or The easier for now is to remove the file first And what was it railway chasing? Tell me that I should have used something else. Yeah, but I was quite short Integer was correct I Had it capitalized before I Didn't I learn okay Oops I was too lazy And now we have to OSM ID as an integer See there But that's more or less VRT functionality we're using here And What I can do as well is doing the reverse transformation Automatically that's another option of OGR transform Oops So you have this reverse flag Which interprets the the configuration file the reverse way and And help just doing the reverse config transformation so I go back to transform and So this was the input Now I do Another Output file with another name and the input is now the JSON the geochasing I have created and I have to know I don't have even have to give the format, but I could also use another format I only have to Specify that these are worse Whereas config, okay, and now I should have New And 73 features so it looks correct Exams like this are on on the GitHub page of the project I switch back to the last slide So that's the GitHub page all the sources there and there is quite some documentation there in the in the read me but This is an ongoing project and if somebody's interested and Things that's useful. I'm very open I'm open for any help. So what could be done is Doing something similar like Fiona on the good side Having including a more path on a Ogr API Not all Ogr the Ogr functionality is included yet Then it would be nice to make table showings easier star. You can do it with as well, but it's not so easy the Python API itself is Is not documented yet. Yeah, there is no documentation for the payday Python API, but only for the configuration file and executables and as I said, it has a Qtis plugin, but this Qtis plugin is specialized on on the Swiss Intel is format and that was the reason for this project and It would be very interesting to have a more generic plugin for doing Ogr transformations started from Qtis without the need of an executable which is always a little bit difficult It is platform dependent and pass passes must be correct and and the libraries must be found and This would make this easier Could also be interesting to integrate this in the processing functionality of Qtis which would Then you could use the model builder of Qtis for using for doing things like that Okay, time is over. Thank you Maybe one question. Yeah, yeah So I remember reading Gdall roadmap that there's Eventually some consolidation of the Gdall and Ogr sides of that project and some talk about changing the way the binaries function, so is that something that's You know anything about that might be happening sooner that will affect This project here It has already happened for a big part it is implemented But it doesn't influence this very much. It's I mean it's still an Ogr API and a good API I'm not sure but I think that the Python API will will stay the same. I don't expect big changes there so Doesn't influence the project This is this Python part Yeah, maybe this this command line interface has to be even more generic that it even covers roster formats If you Be another step, yeah Thank you
 Hello, I'm ready to start and talk a little bit about OGR and an extension for OGR  and doing ETL with OGR  About myself my name is Benjamin Calvader. I work for SOS Paul, a space-based company  doing mainly development for QGIS and  providing services  support for QGIS and  We use as almost everyone uses OGR GDL and  That's why I'm talking about OGR as well here  ETL is Extract Transform  Loads everyone doing GAS is working with geo data  there are  quite a few  Applications available for doing ETL there are certain applications on a higher level  like hail, geocatel, talent, geo extensions  so you have a  desktop  user interface where you can  make models for your data transformations and  so if you  want to work on that level  then I  Would suggest to have a look at geocatel. I like that one very much  But in my case I need  like a library which is  embeddable in  QGIS for instance, so this is not an option for me. I want to build  Lower level libraries  GDL OGR is one of them and  There are other possibilities you can do a lot of things directly with Poch AS or if you're handling XML files  You can do XSLT  There are many possibilities  But  For us it was we wanted to build on OGR so I explained first how you do  transform data with OGR there is  OGR to OGR the program that executable which many people know there is even a  desktop GUI tool for  Helping with that and there are  plugins and things helping with using OGR to OGR and  To understand how it works you have input data and then you have  options which  apply to the input like applying an SRS or a layer name or a layer type  geometry type and then it is read or  Then it is the file is read and it is in the OGR data model and  Then you have output options the most important one is  the format you want to have and you can transform it or apply filters and so on  But it is important to understand what this OGR data model supports it is  quite a simple model it is  Attribute fields so number of fields with types it uses a feature identifier which you can  Which is configurable and it had for a long time one geometry field and now since  111 it has  multiple geometry fields. This is an important improvement and it was  financed  by Swiss Agency because of that project  and because of support for Swiss format interleaves which  need support for multiple geometries per layer  Next step next level when using OGR to OGR is using the OGR virtual format  this is  not so well known and  This is good documentation on the OGR site  and it was made I think  Frank can correct me to  create spatial layers from  from  Text files means like CSV or something or flat tables without  Geometry format but with let long coordinates or something similar and then you can  Make a configurate file like the XML down there where you can there you can tell  which fields or which attributes are forming the geometry so the X and Y  attribute and  using this you can look at  At the non spatial layer with spatial tools and  If you look at this graphical representation you see that this VRT is the input for OGR to OGR  but it  Points to the to the input data and it replaces these input options  for reading  Data and you can apply the other OGR to OGR options for creating our data  or another format  This  virtual format has grown over the years. It has many possibilities many hidden possibilities or not so  well-known possibilities you can  Obviously define the geometry fields you can change column names  You can change column types or map them  You can omit columns and you can apply spatial filters. You can  reproject layers and  and you can instead of having  Fixed input layer you can have as well OGR as well expression which could be native SQL for  postures or it can be  SQL applied to shapefiles even  Which are then executed by OGR and this gives you the poly possibility to have like  calculated fields  fields with expressions or doing joints and other things so  this is quite useful and  We build on on that a new stat for  For our purpose and  now what I want to show you is this  Python extension we using the Python API of OGR and  and  we collect some functionality of  OGR info and OGR to OGR and but also  Python implementations for which are included in the OGR distribution  We provide a single binary for command line usage  which makes  More uniform the usage and we have a JSON configuration file  Which has  Similar content as the VRT just shown  but with additional parameters for  like the OGR OGR to OGR  Command line parameters and  And in this OGR tools also included is a QGIS plugin  and  That wanted here is that because OGR is already included in QGIS. There is no additional  dependency, so if we use the Python API of OGR, we don't have to run an external  Executable, but we have we can use all the functionality of OGR or via Python API.  This graphic should explain how it works, so  The new part is on the right side is this OGR configuration, which is this JSON file I mentioned and  from this  temporarily  created VRT is used for as an input for OGR  which points to the input data and  input options and output options are  included in this OGR configuration, so the idea is that you  Can put your OGR to OGR  parameters in one configuration file. That's  one idea  So this is a Python library and it is installed like Python libraries  Usually are installed with its PIP install OGR tools and then you can use the command line  I  Would like to show it on the bash directly in the console  But I have to switch first  Because I should see it as well  So  So  I've installed it obviously and I can call this OGR executable which is also a Python file and  Here you can see the commands which are available. I can look for a version of  OGR the OGR library installed  Okay, you don't see the button  Yeah, that's good enough, okay  What your version or your formats think you can do with regular OGR commands  Oh, where is my script?  Sorry  Okay, just a second  Okay  Next thing is I want to have something like OGR info  I have some test file included  So that's similar outward or the same output as you get when you  use OGR info as  Executable  But this one is implemented in in Python which wasn't done by me which is also included into OGR distribution  There is a Python implementation of OGR info and I'm using this one  Another feature is  Applying as OGR SQL expressions on data. This is all the possible with OGR info, but  my  goal was to have one  Point or one entry point for all these kind of commands so I  Do an SQL expression on the same on this shapefine  Okay, now the SQL query so I  Had the info so I know I know which  attribute I have  So what I do now I do  Select operation you can see it. It's bottom  I do that first oh, huh railway is that correct  I  Need type or here  I go to the info back here  I see the name of the layer and see all the the column names  So I made  The layer name is correct  So did I make a mistake here type was my name?  That's  I do OGR SQL which is the same as a  This as you can do with OGR info. That's what I do and what it should do  In my example I've even something  Like that  Hmm I can't see the mistake right now, but I  Hope you believe me that you would get  The same as  That's when you do OGR info  It is select I think a query  Country memory is it  Yes on the OGR site you have documentation what kind of SQL you can do and the SQL is  Interpreted differently when you do it on sources like shapefiles, then you have limited  SQL support and we do it on on post keys it is native approaches SQL or on SQLite or  It's also native SQL  Next thing which is included is  helper for generating this VRT files  Which is useful is for one to  Oh that was  So that was also that the SQL problem  What's that  We know that that's that's the same output as OGR info gives you if you do a nice call query on on a  OGR input type and  So you can add where close and so on so the problem was this from  File name and if we do this OGR VRT we get  A valid VRT  Which does include the same information as OGR info has which gives you a starting point for  changing data types or changing  Field names and so on so this is  Useful is if you want to work with VRTs  And next step is now to have a configuration file for doing this OGR  tools transformation. So what I do is  another command  Generate config which is similar to this VRT configuration generator. I  Tell what  What the preferred destination format is so this is different to this VRT  Because I want also to have the output parameters of OGR to OGR included in the configuration file  I don't have to convert to this format, but it's  It's optimized that default parameters are  optimized for the  this default format  So the same again I use this shapefile. Okay, so this is how the  This configuration file looks like and  Some parameters look familiar. So this are  OGR to OGR parameters which are applied  When I  execute the transformation and which can be changed  Your source format and destination format  we have  The same thing as we had in in the VRT  the field types and so on and  We have geometry type layer name and  Geometry field specification  Juniper empty but I ignore that for now  I write that into a file  Or now let's call it configuration  And now I can use that for a transformation so next step is  OGR transform  Ah look strange, yeah  But here the executable helps  I can always press enter and look what's missing so I  Add format I  Want I don't have to add because I go to geochasing first  At the input file  And the output file and it's the same order as  OGR to OGR destination and source so the output will be real way to chasing  Oops a little mistake. What is it? I  Forgot the configuration fine  This one is not optional so what I got now is railway.json  Which is the geochasing file  Made from the shape file  What does this mean  Good, okay  And to play a little bit I can edit the configuration  And let's say I saw that this was MIT was  Floating point number which makes not so much sense  So I do the same again with this new configuration  oops  Geochasing driver that's originally OGR to OGR does not overwrite existing files  So either I had to add the options for overwriting or  The easier for now is to remove the file first  And what was it railway chasing?  Tell me that I should have used something else. Yeah, but I was quite  short  Integer was correct  I  Had it capitalized before I  Didn't I learn okay  Oops  I was too lazy  And now we have to OSM ID as an integer  See there  But that's more or less VRT functionality we're using here  And  What I can do as well is doing the reverse transformation  Automatically that's another option of OGR transform  Oops  So you have this reverse flag  Which interprets the  the configuration file the reverse way and  And help just doing the reverse config  transformation so I  go back to transform and  So this was the input  Now I do  Another  Output file with another name and the input is now the JSON the geochasing I have created and I have to know  I don't have even have to give the format, but I could also use another format  I only have to  Specify that these are worse  Whereas config, okay, and now I should have  New  And 73 features so it looks correct  Exams like this are on on the GitHub page of the project  I switch back to the last slide  So that's the GitHub page all the sources there and there is  quite some documentation there in the in the read me but  This is an ongoing project and if somebody's interested and  Things that's useful. I'm very open I'm open for any help. So what could be done is  Doing something similar like Fiona on the good side  Having including a more path on a Ogr API  Not all Ogr the Ogr functionality is included yet  Then it would be nice to make table showings easier star. You can do it with as well, but it's  not so easy  the Python API itself is  Is not documented yet. Yeah, there is no documentation for the payday Python API, but only for the configuration file and  executables and  as I said, it has a  Qtis plugin, but this Qtis plugin is  specialized on on the Swiss Intel is format and that was the reason for this project and  It would be very interesting to have a more generic  plugin for doing  Ogr  transformations started from Qtis without the need of an executable which is always a little bit difficult  It is platform dependent and pass passes must be correct and and the libraries must be found and  This would make this easier  Could also be interesting to integrate this in the processing  functionality of Qtis which would  Then you could use the model builder of Qtis for using for doing things like that  Okay, time is over. Thank you  Maybe one question. Yeah, yeah  So I remember reading  Gdall roadmap that there's  Eventually some consolidation of the Gdall and Ogr sides of that project  and some talk about  changing  the way the binaries function, so is that something that's  You know anything about that might be happening sooner that will affect  This project here  It has already happened for a big part it is implemented  But it doesn't influence this very much. It's I mean it's still an Ogr API and a good API  I'm not sure but I think that the Python API will will stay the same. I don't expect big changes there  so  Doesn't influence the project  This is this Python part  Yeah, maybe this this command line interface has to be even more generic that it even covers  roster formats  If you  Be another step, yeah  Thank you
09_Pirmin_Kalberer_720p_3144_14819.png 09_Pirmin_Kalberer_720p_15456_15503.png 09_Pirmin_Kalberer_720p_16692_16811.png 09_Pirmin_Kalberer_720p_17784_18671.png 09_Pirmin_Kalberer_720p_27132_27923.png 09_Pirmin_Kalberer_720p_41316_41483.png