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 ?

4
  • You want to SELECT the geometry column - just add geometry AS geom to the command above.
    – geozelot
    Jul 9 at 9:51
  • indeed that makes it work just fine ... it was obvious sorry Jul 9 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
    Jul 9 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
    Jul 9 at 11:05
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
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 ?? Jul 12 at 7:44
  • 1
    for those interested I've added -lco COLUMN_TYPES="<date_field_name>=date" to force field type Jul 12 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 Jul 12 at 14:46
  • You can't create attribute indexes with ogr2ogr but you can create them with ogrinfo and -sql parameter
    – user30184
    Aug 3 at 22:43

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.