Re: [gdal-dev] OGR Shapefile SQL with date in where clause
On Mon, Feb 7, 2011 at 4:08 PM, Nicolas Simon nicolas.si...@spw.wallonie.be wrote: Hello, I would like to extract feature from a shapefile layer based on a Date field. But I don't know how to format date value Here is a defect command ogr2ogr -f ESRI Shapefile extrait.shp fulldataset.shp -sql select * from fulldataset where VISTERRAIN = '2010/12/07' Help will be welcome. Thank you Nicolas Simon Hi Nicolas I am using gdal 1.8 and it is working for me, look at the output: ogrinfo -al poi.shp INFO: Open of `poi.shp' using driver `ESRI Shapefile' successful. Layer name: poi Geometry: Point Feature Count: 3 Extent: (-5106701.348438, -1250725.685421) - (-17541.662933, 4118769.067506) Layer SRS WKT: PROJCS[WGS_1984_UTM_Zone_32N, GEOGCS[GCS_WGS_1984, DATUM[WGS_1984, SPHEROID[WGS_1984,6378137.0,298.257223563]], PRIMEM[Greenwich,0.0], UNIT[Degree,0.0174532925199433]], PROJECTION[Transverse_Mercator], PARAMETER[False_Easting,50.0], PARAMETER[False_Northing,0.0], PARAMETER[Central_Meridian,9.0], PARAMETER[Scale_Factor,0.9996], PARAMETER[Latitude_Of_Origin,0.0], UNIT[Meter,1.0]] Id: Integer (6.0) mydate_d: Date (10.0) mydate_s: String (50.0) OGRFeature(poi):0 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-4071618.022572413086891 4118769.067506278399378) OGRFeature(poi):1 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-5106701.348437865264714 668491.314621437806636) OGRFeature(poi):2 Id (Integer) = 0 mydate_d (Date) = 2010/11/11 mydate_s (String) = (null) POINT (-17541.662932725295832 -1250725.685420754598454) ogr2ogr out.shp poi.shp -sql select * from poi where mydate_d = '2010/08/11' ogrinfo -al out.shp INFO: Open of `out.shp' using driver `ESRI Shapefile' successful. Layer name: out Geometry: Point Feature Count: 2 Extent: (-5106701.348438, 668491.314621) - (-4071618.022572, 4118769.067506) Layer SRS WKT: PROJCS[WGS_1984_UTM_Zone_32N, GEOGCS[GCS_WGS_1984, DATUM[WGS_1984, SPHEROID[WGS_1984,6378137.0,298.257223563]], PRIMEM[Greenwich,0.0], UNIT[Degree,0.017453292519943295]], PROJECTION[Transverse_Mercator], PARAMETER[False_Easting,50.0], PARAMETER[False_Northing,0.0], PARAMETER[Central_Meridian,9.0], PARAMETER[Scale_Factor,0.9996], PARAMETER[Latitude_Of_Origin,0.0], UNIT[Meter,1.0]] Id: Integer (6.0) mydate_d: Date (10.0) mydate_s: String (50.0) OGRFeature(out):0 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-4071618.022572413086891 4118769.067506278399378) OGRFeature(out):1 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-5106701.348437865264714 668491.314621437806636) What error do you get? best regards P -- Paolo Corti Geospatial software developer web: http://www.paolocorti.net twitter: @paolo_corti ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
RE: [gdal-dev] OGR Shapefile SQL with date in where clause
Paolo, I have no error on the command line... and an empty result in the destination file. I'm using GDAL 1.6.1, could it be the reason ? Thk Nicolas -Message d'origine- De : Paolo Corti [mailto:pco...@gmail.com] Envoyé : lundi 7 février 2011 16:26 À : Nicolas Simon Cc : gdal-dev@lists.osgeo.org Objet : Re: [gdal-dev] OGR Shapefile SQL with date in where clause On Mon, Feb 7, 2011 at 4:08 PM, Nicolas Simon nicolas.si...@spw.wallonie.be wrote: Hello, I would like to extract feature from a shapefile layer based on a Date field. But I don't know how to format date value Here is a defect command ogr2ogr -f ESRI Shapefile extrait.shp fulldataset.shp -sql select * from fulldataset where VISTERRAIN = '2010/12/07' Help will be welcome. Thank you Nicolas Simon Hi Nicolas I am using gdal 1.8 and it is working for me, look at the output: ogrinfo -al poi.shp INFO: Open of `poi.shp' using driver `ESRI Shapefile' successful. Layer name: poi Geometry: Point Feature Count: 3 Extent: (-5106701.348438, -1250725.685421) - (-17541.662933, 4118769.067506) Layer SRS WKT: PROJCS[WGS_1984_UTM_Zone_32N, GEOGCS[GCS_WGS_1984, DATUM[WGS_1984, SPHEROID[WGS_1984,6378137.0,298.257223563]], PRIMEM[Greenwich,0.0], UNIT[Degree,0.0174532925199433]], PROJECTION[Transverse_Mercator], PARAMETER[False_Easting,50.0], PARAMETER[False_Northing,0.0], PARAMETER[Central_Meridian,9.0], PARAMETER[Scale_Factor,0.9996], PARAMETER[Latitude_Of_Origin,0.0], UNIT[Meter,1.0]] Id: Integer (6.0) mydate_d: Date (10.0) mydate_s: String (50.0) OGRFeature(poi):0 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-4071618.022572413086891 4118769.067506278399378) OGRFeature(poi):1 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-5106701.348437865264714 668491.314621437806636) OGRFeature(poi):2 Id (Integer) = 0 mydate_d (Date) = 2010/11/11 mydate_s (String) = (null) POINT (-17541.662932725295832 -1250725.685420754598454) ogr2ogr out.shp poi.shp -sql select * from poi where mydate_d = '2010/08/11' ogrinfo -al out.shp INFO: Open of `out.shp' using driver `ESRI Shapefile' successful. Layer name: out Geometry: Point Feature Count: 2 Extent: (-5106701.348438, 668491.314621) - (-4071618.022572, 4118769.067506) Layer SRS WKT: PROJCS[WGS_1984_UTM_Zone_32N, GEOGCS[GCS_WGS_1984, DATUM[WGS_1984, SPHEROID[WGS_1984,6378137.0,298.257223563]], PRIMEM[Greenwich,0.0], UNIT[Degree,0.017453292519943295]], PROJECTION[Transverse_Mercator], PARAMETER[False_Easting,50.0], PARAMETER[False_Northing,0.0], PARAMETER[Central_Meridian,9.0], PARAMETER[Scale_Factor,0.9996], PARAMETER[Latitude_Of_Origin,0.0], UNIT[Meter,1.0]] Id: Integer (6.0) mydate_d: Date (10.0) mydate_s: String (50.0) OGRFeature(out):0 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-4071618.022572413086891 4118769.067506278399378) OGRFeature(out):1 Id (Integer) = 0 mydate_d (Date) = 2010/08/11 mydate_s (String) = (null) POINT (-5106701.348437865264714 668491.314621437806636) What error do you get? best regards P -- Paolo Corti Geospatial software developer web: http://www.paolocorti.net twitter: @paolo_corti ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR Shapefile SQL with date in where clause
Le lundi 07 février 2011 16:35:21, Nicolas Simon a écrit : Paolo, I have no error on the command line... and an empty result in the destination file. I'm using GDAL 1.6.1, could it be the reason ? Yes, you likely need to upgrade to latest version as there have been a number of fixes related to SQL and dates. Thk Nicolas ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
