[gdal-dev] OGR PG Driver 1.8 to 1.9 undocumented functionality
change (bug?) with ExecuteSQL/GetSpatialRef
Silas Parker
skyhisi at gmail.com
Fri Dec 16 08:18:16 EST 2011
Hi,
I've updated my GDAL 1.9 build to SVN rev 23580 which is currently the
head revision.
I then ran the new commands and the previous set with the debug flag on.
The standard (not SQL) based command works fine, the SQL with a space
at the beginning still fails, SQL with no space at the beginning works
fine.
On GDAL 1.8:
$ ogrinfo -ro -so --debug on "PG:dbname=geocoder user=postgres
password=postgres" codepoint works
PG: DBName="geocoder"
PG: PostgreSQL version string : 'PostgreSQL 8.4.9 on
x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu
4.4.3-4ubuntu5) 4.4.3, 64-bit'
PG: PostGIS version string : '1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
PG: Layer 'codepoint' geometry type: POINT:Point, Dim=2
OGR: OGROpen(PG:dbname=geocoder user=postgres
password=postgres/0xbaa520) succeeded as PostgreSQL.
INFO: Open of `PG:dbname=geocoder user=postgres password=postgres'
using driver `PostgreSQL' successful.
OGR: GetLayerCount() = 1
PG: Primary key name (FID): ogc_fid
PG: Using column 'ogc_fid' as FID for table 'codepoint'
Layer name: codepoint
Geometry: Point
Feature Count: 1689582
Extent: (63215.000000, 8195.000000) - (655448.000000, 1213660.000000)
Layer SRS WKT:
PROJCS["OSGB 1936 / British National Grid",
GEOGCS["OSGB 1936",
DATUM["OSGB_1936",
SPHEROID["Airy 1830",6377563.396,299.3249646,
AUTHORITY["EPSG","7001"]],
AUTHORITY["EPSG","6277"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4277"]],
UNIT["metre",1,
AUTHORITY["EPSG","9001"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",49],
PARAMETER["central_meridian",-2],
PARAMETER["scale_factor",0.9996012717],
PARAMETER["false_easting",400000],
PARAMETER["false_northing",-100000],
AUTHORITY["EPSG","27700"],
AXIS["Easting",EAST],
AXIS["Northing",NORTH]]
FID Column = ogc_fid
Geometry Column = wkb_geometry
countyc: String (0.0)
wardc: String (0.0)
countryc: String (0.0)
countys: String (0.0)
postcode: String (0.0)
districtc: String (0.0)
wards: String (0.0)
districts: String (0.0)
nhsareas: String (0.0)
countrys: String (0.0)
nhsregions: String (0.0)
nhsareac: String (0.0)
nhsregionc: String (0.0)
ERROR 1: No field definitions found for 'works', is it a table?
FAILURE: Couldn't fetch requested layer works!
GDAL: In GDALDestroy - unloading GDAL shared library.
In GDAL 1.9:
$ ogrinfo -ro -so --debug on "PG:dbname=geocoder user=postgres
password=postgres" codepoint works
PG: DBName="geocoder"
PG: PostgreSQL version string : 'PostgreSQL 8.4.9 on
x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu
4.4.3-4ubuntu5) 4.4.3, 64-bit'
PG: PostGIS version string : '1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
PG: Layer 'codepoint' geometry type: POINT:Point, Dim=2
OGR: OGROpen(PG:dbname=geocoder user=postgres
password=postgres/0x827500) succeeded as PostgreSQL.
INFO: Open of `PG:dbname=geocoder user=postgres password=postgres'
using driver `PostgreSQL' successful.
OGR: GetLayerCount() = 1
PG: Primary key name (FID): ogc_fid
PG: Using column 'ogc_fid' as FID for table 'codepoint'
Layer name: codepoint
Geometry: Point
Feature Count: 1689582
Extent: (63215.000000, 8195.000000) - (655448.000000, 1213660.000000)
Layer SRS WKT:
PROJCS["OSGB 1936 / British National Grid",
GEOGCS["OSGB 1936",
DATUM["OSGB_1936",
SPHEROID["Airy 1830",6377563.396,299.3249646,
AUTHORITY["EPSG","7001"]],
AUTHORITY["EPSG","6277"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4277"]],
UNIT["metre",1,
AUTHORITY["EPSG","9001"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",49],
PARAMETER["central_meridian",-2],
PARAMETER["scale_factor",0.9996012717],
PARAMETER["false_easting",400000],
PARAMETER["false_northing",-100000],
AUTHORITY["EPSG","27700"],
AXIS["Easting",EAST],
AXIS["Northing",NORTH]]
FID Column = ogc_fid
Geometry Column = wkb_geometry
postcode: String (0.0)
countryc: String (0.0)
countyc: String (0.0)
districtc: String (0.0)
wardc: String (0.0)
nhsregionc: String (0.0)
nhsareac: String (0.0)
countrys: String (0.0)
countys: String (0.0)
districts: String (0.0)
wards: String (0.0)
nhsregions: String (0.0)
nhsareas: String (0.0)
ERROR 1: No field definitions found for 'works', is it a table?
FAILURE: Couldn't fetch requested layer works!
GDAL: In GDALDestroy - unloading GDAL shared library.
The original SQL queries with the debug flag:
In GDAL 1.8
$ ogrinfo -ro -so --debug on -sql " SELECT * FROM codepoint"
"PG:dbname=geocoder user=postgres password=postgres"PG:
DBName="geocoder"
PG: PostgreSQL version string : 'PostgreSQL 8.4.9 on
x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu
4.4.3-4ubuntu5) 4.4.3, 64-bit'
PG: PostGIS version string : '1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
PG: Layer 'codepoint' geometry type: POINT:Point, Dim=2
OGR: OGROpen(PG:dbname=geocoder user=postgres
password=postgres/0x1d27520) succeeded as PostgreSQL.
INFO: Open of `PG:dbname=geocoder user=postgres password=postgres'
using driver `PostgreSQL' successful.
PG: Command Results Tuples = 1689582
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1689582
Extent: (63215.000000, 8195.000000) - (655448.000000, 1213660.000000)
Layer SRS WKT:
PROJCS["OSGB 1936 / British National Grid",
GEOGCS["OSGB 1936",
DATUM["OSGB_1936",
SPHEROID["Airy 1830",6377563.396,299.3249646,
AUTHORITY["EPSG","7001"]],
AUTHORITY["EPSG","6277"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4277"]],
UNIT["metre",1,
AUTHORITY["EPSG","9001"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",49],
PARAMETER["central_meridian",-2],
PARAMETER["scale_factor",0.9996012717],
PARAMETER["false_easting",400000],
PARAMETER["false_northing",-100000],
AUTHORITY["EPSG","27700"],
AXIS["Easting",EAST],
AXIS["Northing",NORTH]]
FID Column = ogc_fid
Geometry Column = wkb_geometry
postcode: String (0.0)
countryc: String (0.0)
countyc: String (0.0)
districtc: String (0.0)
wardc: String (0.0)
nhsregionc: String (0.0)
nhsareac: String (0.0)
countrys: String (0.0)
countys: String (0.0)
districts: String (0.0)
wards: String (0.0)
nhsregions: String (0.0)
nhsareas: String (0.0)
OGR: GetLayerCount() = 1
In GDAL 1.9
$ ogrinfo -ro -so --debug on -sql " SELECT * FROM codepoint"
"PG:dbname=geocoder user=postgres password=postgres"
PG: DBName="geocoder"
PG: PostgreSQL version string : 'PostgreSQL 8.4.9 on
x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu
4.4.3-4ubuntu5) 4.4.3, 64-bit'
PG: PostGIS version string : '1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
PG: Layer 'codepoint' geometry type: POINT:Point, Dim=2
OGR: OGROpen(PG:dbname=geocoder user=postgres
password=postgres/0x25724e0) succeeded as PostgreSQL.
INFO: Open of `PG:dbname=geocoder user=postgres password=postgres'
using driver `PostgreSQL' successful.
PG: Command Results Tuples = 1689582
PG: 1689582 features read on layer 'sql_statement'.
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1689582
Extent: (63215.000000, 8195.000000) - (655448.000000, 1213660.000000)
Layer SRS WKT:
(unknown)
postcode: String (0.0)
countryc: String (0.0)
countyc: String (0.0)
districtc: String (0.0)
wardc: String (0.0)
nhsregionc: String (0.0)
nhsareac: String (0.0)
countrys: String (0.0)
countys: String (0.0)
districts: String (0.0)
wards: String (0.0)
nhsregions: String (0.0)
nhsareas: String (0.0)
Mem: 3379164 features read on layer 'sql_statement'.
OGR: GetLayerCount() = 1
Between 1.8 and 1.9 I notice that quite a bit of code has changed in
ogrpgdatasource.cpp OGRPGDataSource::ExecuteSQL starting on line 2289.
I don't know the code well enough to know if this change is
significant.
Thanks,
Silas
On 16 December 2011 12:40, Even Rouault <even.rouault at mines-paris.org> wrote:
> Selon Silas Parker <skyhisi at gmail.com>:
>
>> Hello,
>>
>> I've found that the behaviour of the PG (Postgres/Postgis) driver has
>> changed between 1.8 and 1.9 in ExecuteSQL that isn't listed in the
>> NEWS file.
>>
>> The Spatial reference system of a SQL query can not be obtained (using
>> GetSpatialRef) if the SQL query starts with white-space.
>>
>> This may be a bug, or an undocumented change in functionality!
>
> At first sight, it would look rather to be a bug than a desired change. Could
> you test if the following works with 1.8.0 and svn head ?
>
> ogrinfo -ro -so -sql "PG:dbname=geocoder user=postgres password=postgres"
> codepoint works
>
> It could be also usefull if you could attach the result of those commands with
> "--debug on" added on the command line.
>
>>
>> I am testing with trunk SVN rev 23573, on Ubuntu 10.04.
>>
>> The problem can be reproduced using ogrinfo, although I first observed
>> this in a different application.
>>
>> In version 1.8.0:
>>
>> $ ogrinfo --version
>> GDAL 1.8.0, released 2011/01/12
>>
>> $ ogrinfo -ro -so -sql " SELECT * FROM codepoint" "PG:dbname=geocoder
>> user=postgres password=postgres"
>> INFO: Open of `PG:dbname=geocoder user=postgres password=postgres'
>> using driver `PostgreSQL' successful.
>>
>> Layer name: sql_statement
>> Geometry: Unknown (any)
>> Feature Count: 1689582
>> Extent: (63215.000000, 8195.000000) - (655448.000000, 1213660.000000)
>> Layer SRS WKT:
>> PROJCS["OSGB 1936 / British National Grid",
>> GEOGCS["OSGB 1936",
>> DATUM["OSGB_1936",
>> SPHEROID["Airy 1830",6377563.396,299.3249646,
>> AUTHORITY["EPSG","7001"]],
>> AUTHORITY["EPSG","6277"]],
>> PRIMEM["Greenwich",0,
>> AUTHORITY["EPSG","8901"]],
>> UNIT["degree",0.01745329251994328,
>> AUTHORITY["EPSG","9122"]],
>> AUTHORITY["EPSG","4277"]],
>> UNIT["metre",1,
>> AUTHORITY["EPSG","9001"]],
>> PROJECTION["Transverse_Mercator"],
>> PARAMETER["latitude_of_origin",49],
>> PARAMETER["central_meridian",-2],
>> PARAMETER["scale_factor",0.9996012717],
>> PARAMETER["false_easting",400000],
>> PARAMETER["false_northing",-100000],
>> AUTHORITY["EPSG","27700"],
>> AXIS["Easting",EAST],
>> AXIS["Northing",NORTH]]
>> FID Column = ogc_fid
>> Geometry Column = wkb_geometry
>> postcode: String (0.0)
>> countryc: String (0.0)
>> countyc: String (0.0)
>> districtc: String (0.0)
>> wardc: String (0.0)
>> nhsregionc: String (0.0)
>> nhsareac: String (0.0)
>> countrys: String (0.0)
>> countys: String (0.0)
>> districts: String (0.0)
>> wards: String (0.0)
>> nhsregions: String (0.0)
>> nhsareas: String (0.0)
>>
>>
>> In version 1.9:
>>
>> $ ogrinfo --version
>> GDAL 1.9dev, released 2011/01/18
>>
>> $ ogrinfo -ro -so -sql " SELECT * FROM codepoint" "PG:dbname=geocoder
>> user=postgres password=postgres"
>> INFO: Open of `PG:dbname=geocoder user=postgres password=postgres'
>> using driver `PostgreSQL' successful.
>>
>> Layer name: sql_statement
>> Geometry: Unknown (any)
>> Feature Count: 1689582
>> Extent: (63215.000000, 8195.000000) - (655448.000000, 1213660.000000)
>> Layer SRS WKT:
>> (unknown)
>> postcode: String (0.0)
>> countryc: String (0.0)
>> countyc: String (0.0)
>> districtc: String (0.0)
>> wardc: String (0.0)
>> nhsregionc: String (0.0)
>> nhsareac: String (0.0)
>> countrys: String (0.0)
>> countys: String (0.0)
>> districts: String (0.0)
>> wards: String (0.0)
>> nhsregions: String (0.0)
>> nhsareas: String (0.0)
>>
>> Thanks,
>>
>> Silas
>> _______________________________________________
>> gdal-dev mailing list
>> gdal-dev at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>
>
More information about the gdal-dev
mailing list