1

I'm trying to import a shapefile into a new PostGIS table, selecting specific fields from the input shapefile and adding a new date column to the output table. I'm using ogr2ogr with -sql option to select the fields from the shp that I would like to get imported and I'm using -dialect SQLite in order to be able to add current_date in my SQL request.

See below my ogr2ogr command :

ogr2ogr -f "PostgreSQL" -a_srs "EPSG:4326" PG:"host=<db_host> user=<db_user> password=<db_password> dbname=<db_name>" -nlt MULTIPOLYGON -nln <table_name> -sql "SELECT <shp_field1> AS <pg_field1>,  <shp_field2> AS <pg_field2>,  current_date AS date FROM <shapeFile>" `-dialect SQLite -overwrite <shapeFile>.shp

My problem is that if I don't use the -dialect option, the current_date is not understood in my SQL request, and if I use the -dialect option, I get the current_date working but the geometry is not imported in my output PostGIS table.

Am I missing something ?

Improve this question
4
  • You want to SELECT the geometry column - just add geometry AS geom to the command above.
    – geozelot
    Commented Jul 9, 2021 at 9:51
  • indeed that makes it work just fine ... it was obvious sorry
    – user11681321
    Commented Jul 9, 2021 at 10:03
  • Nothing to be sorry about, and with a decent amount of depth even an 'obvious' issue makes a good question. Let me add my comment as an answer for you to accept, so this post can be marked as solved...
    – geozelot
    Commented Jul 9, 2021 at 11:02
  • 1
    No need to apologize because it is not totally obvious. With SQLite dialect you must explicitly select the geometry column but with the OGR SQL dialect the geometry column is automatically selected. But this difference is documented on corresponding manual pages.
    – user30184
    Commented Jul 9, 2021 at 11:05

1 Answer 1

Reset to default
2

When using the SpatiaLite dialect with the -sql flag, the extracted (and imported) list of fields corresponds exactly to the SELECT list - which also includes the actual geometry field.

Simply add the respective field (wildcard name geometry) to the column list:

ogr2ogr -f "PostgreSQL" -a_srs "EPSG:4326" PG:"host=<db_host> user=<db_user> password=<db_password> dbname=<db_name>" -nlt MULTIPOLYGON -nln <table_name> -sql "SELECT <shp_field1> AS <pg_field1>,  <shp_field2> AS <pg_field2>,  current_date AS date, geometry AS geom FROM <shapeFile>" `-dialect SQLite -overwrite <shapeFile>.shp
Improve this answer
4
  • another point is the current_date field is imported into postgis as varchar, I've tried to use cast(current_date as date) but I get an integer ??
    – user11681321
    Commented Jul 12, 2021 at 7:44
  • 1
    for those interested I've added -lco COLUMN_TYPES="<date_field_name>=date" to force field type
    – user11681321
    Commented Jul 12, 2021 at 11:14
  • is there any option for creating attribute indexing on the output PostGIS table within the ogr2ogr command line, or does it have to be created afterwards in PostGIS ? Thanks
    – user11681321
    Commented Jul 12, 2021 at 14:46
  • 1
    You can't create attribute indexes with ogr2ogr but you can create them with ogrinfo and -sql parameter
    – user30184
    Commented Aug 3, 2021 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.