[gdal-dev] ogr2ogr adding metadata

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] ogr2ogr adding metadata

Lars
Hello folks,

Using ogr2ogr (version 2.2.3) to import s57 into PostgreSQL which works great.

What is the recommended approach for adding metadata to all inserted data?

Say I wanted to add current date to all inserted data. Does ogr2ogr support such operation? Tried using the "-mo" argument but it did not make any inpact (same result with argument as without). Ideally I do not want the client to be adding this information as a post insert operation (if even possible).

Appreciate any insight.

kind regards, Lars

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr adding metadata

Even Rouault-2
On vendredi 22 juin 2018 09:41:02 CEST Lars wrote:

> Hello folks,
>
> Using ogr2ogr (version 2.2.3) to import s57 into PostgreSQL which works
> great.
>
> What is the recommended approach for adding metadata to all inserted data?
>
> Say I wanted to add current date to all inserted data. Does ogr2ogr support
> such operation? Tried using the "-mo" argument but it did not make any
> inpact (same result with argument as without). Ideally I do not want the
> client to be adding this information as a post insert operation (if even
> possible).

Lars,

-mo will work only with very few target drivers (I can only think to
GeoPackage actually). This is per-layer/per-table metadata. There's perhaps
the equivalent in PostgreSQL but this isn't implemented by GDAL

But you can add a column with the current timestamp for example with

ogr2ogr -update PG:xxxxxx poly.shp \
       -sql "SELECT *, CURRENT_TIMESTAMP FROM poly" -dialect SQLITE

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr adding metadata

Lars
Even,

Appreciate the response.

Your example is interesting but there are a couple of issues. Importing a random .000 file using ogr2ogr yields 6 tables in the database. Which tables are created depend on the data and are unknown to me prior to import, so it is difficult to generate the SQL in advance. This approach does not scale very well if data yields a number of tables.

Another issue was when adding the SQL statement for one specific table caused the import to ignore the other 5 tables. Is this the expect result or should all 6 table be imported?

Regards, Lars


Fra: Even Rouault <[hidden email]>
Sendt: fredag 22. juni 2018 23.15
Til: [hidden email]
Kopi: Lars
Emne: Re: [gdal-dev] ogr2ogr adding metadata
 
On vendredi 22 juin 2018 09:41:02 CEST Lars wrote:
> Hello folks,
>
> Using ogr2ogr (version 2.2.3) to import s57 into PostgreSQL which works
> great.
>
> What is the recommended approach for adding metadata to all inserted data?
>
> Say I wanted to add current date to all inserted data. Does ogr2ogr support
> such operation? Tried using the "-mo" argument but it did not make any
> inpact (same result with argument as without). Ideally I do not want the
> client to be adding this information as a post insert operation (if even
> possible).

Lars,

-mo will work only with very few target drivers (I can only think to
GeoPackage actually). This is per-layer/per-table metadata. There's perhaps
the equivalent in PostgreSQL but this isn't implemented by GDAL

But you can add a column with the current timestamp for example with

ogr2ogr -update PG:xxxxxx poly.shp \
       -sql "SELECT *, CURRENT_TIMESTAMP FROM poly" -dialect SQLITE

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr adding metadata

Andreas Oxenstierna
A S57 file with only 6 feature object classes? Normally there should be many more.

I would add triggers to each table in the database for timestamps.
Potential workflow:
1 ogr for initial import into own schema
2 Postgre script, reading all tables in the schema using the information_schema info, to truncate and add triggers
3 ogr for final import


Even,

Appreciate the response.

Your example is interesting but there are a couple of issues. Importing a random .000 file using ogr2ogr yields 6 tables in the database. Which tables are created depend on the data and are unknown to me prior to import, so it is difficult to generate the SQL in advance. This approach does not scale very well if data yields a number of tables.

Another issue was when adding the SQL statement for one specific table caused the import to ignore the other 5 tables. Is this the expect result or should all 6 table be imported?

Regards, Lars


Fra: Even Rouault [hidden email]
Sendt: fredag 22. juni 2018 23.15
Til: [hidden email]
Kopi: Lars
Emne: Re: [gdal-dev] ogr2ogr adding metadata
 
On vendredi 22 juin 2018 09:41:02 CEST Lars wrote:
> Hello folks,
>
> Using ogr2ogr (version 2.2.3) to import s57 into PostgreSQL which works
> great.
>
> What is the recommended approach for adding metadata to all inserted data?
>
> Say I wanted to add current date to all inserted data. Does ogr2ogr support
> such operation? Tried using the "-mo" argument but it did not make any
> inpact (same result with argument as without). Ideally I do not want the
> client to be adding this information as a post insert operation (if even
> possible).

Lars,

-mo will work only with very few target drivers (I can only think to
GeoPackage actually). This is per-layer/per-table metadata. There's perhaps
the equivalent in PostgreSQL but this isn't implemented by GDAL

But you can add a column with the current timestamp for example with

ogr2ogr -update PG:xxxxxx poly.shp \
       -sql "SELECT *, CURRENT_TIMESTAMP FROM poly" -dialect SQLITE

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


-- 
Hälsningar

Andreas Oxenstierna
T-Kartor Geospatial AB
mobile: +46 733 206831
mailto: [hidden email]
http://www.t-kartor.com

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr adding metadata

Lars
Andreas;

Thank you for your suggestion.

How would this workflow handle
  • multiple import operations spread over time into the same database and schema? The first import will yield a number of tables depending on data. The second import will need to determine which tables already contain a trigger and skip truncating it.
  • adding (one or more) user specified metadata string. For example I would like to add description information or name of organization that produced data.

Another suggestion, add optional foreign key and value parameter to ogr2ogr. Using this option would add a extra colum to each created table which references a user define "metadata" table. The workflow could be
1. Create custom metadata table
2. Insert record into metadata table
3. Run ogr2ogr with reference to record created in step 2.

In this scenario ogr2ogr does not need to know much about any additional information and it reduces duplicated information.

kind regards, Lars


Fra: gdal-dev <[hidden email]> på vegne av Andreas Oxenstierna <[hidden email]>
Sendt: onsdag 27. juni 2018 09.16
Til: [hidden email]
Emne: Re: [gdal-dev] ogr2ogr adding metadata
 
A S57 file with only 6 feature object classes? Normally there should be many more.

I would add triggers to each table in the database for timestamps.
Potential workflow:
1 ogr for initial import into own schema
2 Postgre script, reading all tables in the schema using the information_schema info, to truncate and add triggers
3 ogr for final import


Even,

Appreciate the response.

Your example is interesting but there are a couple of issues. Importing a random .000 file using ogr2ogr yields 6 tables in the database. Which tables are created depend on the data and are unknown to me prior to import, so it is difficult to generate the SQL in advance. This approach does not scale very well if data yields a number of tables.

Another issue was when adding the SQL statement for one specific table caused the import to ignore the other 5 tables. Is this the expect result or should all 6 table be imported?

Regards, Lars


Fra: Even Rouault [hidden email]
Sendt: fredag 22. juni 2018 23.15
Til: [hidden email]
Kopi: Lars
Emne: Re: [gdal-dev] ogr2ogr adding metadata
 
On vendredi 22 juin 2018 09:41:02 CEST Lars wrote:
> Hello folks,
>
> Using ogr2ogr (version 2.2.3) to import s57 into PostgreSQL which works
> great.
>
> What is the recommended approach for adding metadata to all inserted data?
>
> Say I wanted to add current date to all inserted data. Does ogr2ogr support
> such operation? Tried using the "-mo" argument but it did not make any
> inpact (same result with argument as without). Ideally I do not want the
> client to be adding this information as a post insert operation (if even
> possible).

Lars,

-mo will work only with very few target drivers (I can only think to
GeoPackage actually). This is per-layer/per-table metadata. There's perhaps
the equivalent in PostgreSQL but this isn't implemented by GDAL

But you can add a column with the current timestamp for example with

ogr2ogr -update PG:xxxxxx poly.shp \
       -sql "SELECT *, CURRENT_TIMESTAMP FROM poly" -dialect SQLITE

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


-- 
Hälsningar

Andreas Oxenstierna
T-Kartor Geospatial AB
mobile: +46 733 206831
mailto: [hidden email]
http://www.t-kartor.com

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr adding metadata

Andreas Oxenstierna

Andreas;

Thank you for your suggestion.

How would this workflow handle
  • multiple import operations spread over time into the same database and schema? The first import will yield a number of tables depending on data. The second import will need to determine which tables already contain a trigger and skip truncating it.
Initial import into specific empty schema. Then compare and add missing tables in the "real" schema. Note that the S57 object schema is something like 300 feature object classes.


  • adding (one or more) user specified metadata string. For example I would like to add description information or name of organization that produced data.
Can be done with triggers etc. as well for each import. Ask if you need practical examples - but this is clearly outside of the GDAL list.

Another suggestion, add optional foreign key and value parameter to ogr2ogr. Using this option would add a extra colum to each created table which references a user define "metadata" table. The workflow could be
1. Create custom metadata table
2. Insert record into metadata table
3. Run ogr2ogr with reference to record created in step 2.

In this scenario ogr2ogr does not need to know much about any additional information and it reduces duplicated information.

kind regards, Lars


Fra: gdal-dev [hidden email] på vegne av Andreas Oxenstierna [hidden email]
Sendt: onsdag 27. juni 2018 09.16
Til: [hidden email]
Emne: Re: [gdal-dev] ogr2ogr adding metadata
 
A S57 file with only 6 feature object classes? Normally there should be many more.

I would add triggers to each table in the database for timestamps.
Potential workflow:
1 ogr for initial import into own schema
2 Postgre script, reading all tables in the schema using the information_schema info, to truncate and add triggers
3 ogr for final import


Even,

Appreciate the response.

Your example is interesting but there are a couple of issues. Importing a random .000 file using ogr2ogr yields 6 tables in the database. Which tables are created depend on the data and are unknown to me prior to import, so it is difficult to generate the SQL in advance. This approach does not scale very well if data yields a number of tables.

Another issue was when adding the SQL statement for one specific table caused the import to ignore the other 5 tables. Is this the expect result or should all 6 table be imported?

Regards, Lars


Fra: Even Rouault [hidden email]
Sendt: fredag 22. juni 2018 23.15
Til: [hidden email]
Kopi: Lars
Emne: Re: [gdal-dev] ogr2ogr adding metadata
 
On vendredi 22 juin 2018 09:41:02 CEST Lars wrote:
> Hello folks,
>
> Using ogr2ogr (version 2.2.3) to import s57 into PostgreSQL which works
> great.
>
> What is the recommended approach for adding metadata to all inserted data?
>
> Say I wanted to add current date to all inserted data. Does ogr2ogr support
> such operation? Tried using the "-mo" argument but it did not make any
> inpact (same result with argument as without). Ideally I do not want the
> client to be adding this information as a post insert operation (if even
> possible).

Lars,

-mo will work only with very few target drivers (I can only think to
GeoPackage actually). This is per-layer/per-table metadata. There's perhaps
the equivalent in PostgreSQL but this isn't implemented by GDAL

But you can add a column with the current timestamp for example with

ogr2ogr -update PG:xxxxxx poly.shp \
       -sql "SELECT *, CURRENT_TIMESTAMP FROM poly" -dialect SQLITE

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


-- 
Hälsningar

Andreas Oxenstierna
T-Kartor Geospatial AB
mobile: +46 733 206831
mailto: [hidden email]
http://www.t-kartor.com


-- 
Hälsningar

Andreas Oxenstierna
T-Kartor Geospatial AB
mobile: +46 733 206831
mailto: [hidden email]
http://www.t-kartor.com

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev