Dates are set to 30/12/1899 when creating a ShapeFile from PostGIS via CopyLayer

[PaulFMichell]

We call OGR_DS_CopyLayer in gdal19.dll (16/5/2012 SVN) to copy tables from a PostGIS 1.5 database to ESRI shapefiles. All of the date values are set to 30/12/1899.

We can replicate the issue with the following code:

procedure TMyApplication.DoRun;

var

InputOGRDataSource: TOGRDataSource; OutputOGRDriver: TOGRSFDriver; OutputOGRDataSource: TOGRDataSource; OutputOptions: Array [0..1] Of PChar; OGRLayer: TOGRLayer; OutputFolder: String;

begin

OGRRegisterAll; InputOGRDataSource := OGROpen(PChar('PG:dbname=''HCA host=TEPData port=5432 user=user password=password), 0, Nil);

If InputOGRDataSource<>Nil Then

Begin

OGRLayer := OGR_DS_ExecuteSQL(InputOGRDataSource, PChar('SELECT * FROM "SiteAccess"."NewSigns";'), Nil, Nil); OutputOGRDriver := OGRGetDriverByName(PChar('ESRI Shapefile')); OutputOptions[0] := Nil; OutputOptions[1] := Nil; OutputFolder := ExcludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))); OutputOGRDataSource := OGR_Dr_CreateDataSource(OutputOGRDriver, PChar(OutputFolder), OutputOptions); OGR_DS_CopyLayer(OutputOGRDataSource, OGRLayer, PChar('Test'), OutputOptions);

End;

OGR_DS_ReleaseResultSet(InputOGRDataSource, OGRLayer); OGR_DS_Destroy(InputOGRDataSource); OGR_DS_Destroy(OutputOGRDataSource); OGRCleanupAll;

end;

[Even Rouault]

I've done a quick testing with ogr2ogr and it gives correct results, so we need more information. Could you give the output of ogrinfo on your Postgres table ?

[PaulFMichell]

Signs table ogrinfo output

[PaulFMichell]

Thanks for looking at this. I have generated the text output from ogrinfo (attached) with:

ogrinfo "PG:dbname=HCA host=TEPData port=5432 user=user password=password" "SiteAccess.Signs" >"SignsInfo.txt"

I also tried ogr2ogr myself with this command:

ogr2ogr -f "ESRI Shapefile" ogrtest.shp "PG:dbname=HCA host=TEPData port=5432 user=user password=password" -sql "SELECT * FROM ""SiteAccess"".""Signs"""

This worked perfectly! So I can refine my question to: what is the difference between using the -sql parameter of ogr2ogr and calling OGR_DS_ExecuteSQL followed by OGR_DS_CopyLayer?

Is there something else I need to setup to define local date formats or something like that I have overlooked?

Thanks again for your assistance.

[Even Rouault]

OK, after seeing SignsInfo.txt, I understand now what happens. The issue is that your column names are over 10 characters, which is the maximum limit supported by the DBF files of shapefiles. The shapefile driver will truncate the column names then. ogr2ogr does not use OGR_DS_CopyLayer() internally and does smarter things to track the remapping of column names, but OGR_DS_CopyLayer() is not smart enough for that. With the following test script, I somehow replicate your issue except I guess null values and not 30/12/1899 (but that might be an artifact on how your interprete null dates. Does ogrinfo actually shows 30/12/1899 that on the resulting shapefile ?)

OGR_DS_CopyLayer() would need some improvements. In the meantime you can workaround that by using a SQL statement that does the renaming of columns to be below 10 characters, like "SELECT Geometry, Reference, Type, DecisionDate as DecisiDate, InstallationDate as InstallDate, ... FROM SiteAccess.Signs"

testcopylayer.py :

import os
from osgeo import ogr

src_ds = ogr.Open('pg:dbname=autotest')
lyr = src_ds.CreateLayer('dt', options = ['OVERWRITE=YES'])
lyr.CreateField(ogr.FieldDefn('foo', ogr.OFTString))
lyr.CreateField(ogr.FieldDefn('DecisionDate', ogr.OFTDateTime))
lyr.CreateField(ogr.FieldDefn('AnotherDate', ogr.OFTDateTime))
lyr.CreateField(ogr.FieldDefn('bar', ogr.OFTString))
feat = ogr.Feature(lyr.GetLayerDefn())
feat.SetField('foo', 'bar')
feat.SetField('DecisionDate', '2012/05/21 12:34:56')
feat.SetField('AnotherDate', '2012/05/21 23:45:51')
feat.SetField('bar', 'baz')
feat.SetGeometry(ogr.CreateGeometryFromWkt('POINT(0 1)'))
lyr.CreateFeature(feat)

sql_lyr = src_ds.ExecuteSQL('SELECT * FROM dt')
feat = sql_lyr.GetNextFeature()
feat.DumpReadable()

try:
    os.unlink('dt.shp')
except:
    pass
out_ds = ogr.GetDriverByName('ESRI Shapefile').CreateDataSource('dt.shp')
out_lyr = out_ds.CopyLayer(sql_lyr, 'dt', [])
src_ds.ReleaseResultSet(sql_lyr)
out_ds = None

dbf_ds = ogr.Open('dt.shp')
dbf_lyr = dbf_ds.GetLayer(0)
dbf_feat = dbf_lyr.GetNextFeature()
dbf_feat.DumpReadable()

$ python testcopylayer.py 

OGRFeature(sql_statement):1
  foo (String) = bar
  decisiondate (DateTime) = 2012/05/21 12:34:56+02
  anotherdate (DateTime) = 2012/05/21 23:45:51+02
  bar (String) = baz
  POINT (0 1)

Warning 6: Normalized/laundered field name: 'decisiondate' to 'decisionda'
Warning 6: Field decisionda create as date field, though DateTime requested.
Warning 6: Normalized/laundered field name: 'anotherdate' to 'anotherdat'
Warning 6: Field anotherdat create as date field, though DateTime requested.
OGRFeature(dt):0
  foo (String) = bar
  decisionda (Date) = (null)
  anotherdat (Date) = (null)
  bar (String) = baz
  POINT (0 1)

[Even Rouault]

Code directly adapted from ogr2ogr.cpp --> Fixed in trunk (r24492) and branches/1.9 (r24493)

Waiting for 1.9.1 to be released before marking as closed and tagging it as 1.9.2