[gdal-dev] OGR OVF has no identified FID column

Brent Fraser bfraser at geoanalytic.com
Fri Aug 20 10:16:27 EDT 2010


David,

   GDAL:       POINT (0.0 2.000000000021871)
   SQL Server: POINT (150 150)

   That IS strange!  There could be a problem with GDAL's or SQL Server's 
encoding/decoding of WKB, or it could be related to data type (geometry? 
geography?) or the lack of a Spatial Reference System?

Best Regards,
Brent Fraser

David Lowther wrote:
> D:\Utility\ms4w\tools\gdal-ogr>ogrinfo test.ovf  PointData --debug on
> ERROR 4: Update access not supported for VRT datasources.
> OGR_ODBC: EstablishSession(DSN:"CLO", userid:"un", password:"pw")
> ODBC: SQLConnect(CLO)
> OGR_ODBC: Table PointData has no identified FID column.
> OGR: OGROpen(ODBC:un/pw at CLO,PointData/00FB9BB0) succeeded as ODBC.
> OGR: OGROpen(test.ovf/010870B8) succeeded as VRT.
> Had to open data source read-only.
> INFO: Open of `test.ovf'
>       using driver `VRT' successful.
> OGR: GetLayerCount() = 1
> 
> 
> Layer name: PointData
> Geometry: Unknown (any)
> OGR_ODBC: ExecuteSQL(SELECT * FROM PointData)
> Feature Count: 1
> OGR_ODBC: ExecuteSQL(SELECT * FROM PointData)
> Extent: (0.000000, 2.000000) - (0.000000, 2.000000)
> Layer SRS WKT:
> (unknown)
> PKey: Integer (10.0)
> Feature: Binary (0.0)
> OGR_ODBC: ExecuteSQL(SELECT * FROM PointData)
> OGRFeature(PointData):1
>   PKey (Integer) = 1
>   Feature (Binary) = 00000000010C0000000000C062400000000000C06240
>   POINT (0.0 2.000000000021871)
> 
> VRT: 2 features read on layer 'PointData'.
> OGR_ODBC: 3 features read on layer 'PointData'.
> ODBC: SQLDisconnect()
> 
> 
> 
> 
> And, why do you think it is saying 0.0 2.000000... for the point when the
> SQL query says?
> 
> 1	0x00000000010C0000000000C062400000000000C06240	POINT (150 150)
> 
> 
> 
> David Lowther
>  
> Coordinate Solutions, Inc.
>  
> 
> -----Original Message-----
> From: Brent Fraser [mailto:bfraser at geoanalytic.com] 
> Sent: Thursday, August 19, 2010 5:21 PM
> To: David Lowther
> Cc: gdal-dev at lists.osgeo.org
> Subject: Re: [gdal-dev] OGR OVF has no identified FID column
> 
> I think it's faster since it doesn't have to enumerate all the tables,views,
> etc.
> 
> so how do you get for attributes names when you do a
> 
> ogrinfo test.ovf  PointData --debug on
> 
> Brent
> 
> David Lowther wrote:
>> Still doesn't see the FID but is way faster! 
>>
>> <OGRVRTDataSource>
>>      <OGRVRTLayer name="PointData">
>>           <SrcDataSource>ODBC:un/pw at CLO,PointData</SrcDataSource>
>>           <GeometryField encoding="WKB" field="Feature"/>
>>           <FID>PKey</FID>
>>      </OGRVRTLayer>
>> </OGRVRTDataSource>
>>
>> ogrinfo test.ovf --debug on
>>
>> ERROR 4: Update access not supported for VRT datasources.
>> OGR_ODBC: EstablishSession(DSN:"CLO", userid:"un", password:"pw")
>> ODBC: SQLConnect(CLO)
>> OGR_ODBC: Table PointData has no identified FID column.
>> OGR: OGROpen(ODBC:un/pw at CLO,PointData/02799B88) succeeded as ODBC.
>> OGR: OGROpen(test.ovf/010270B8) succeeded as VRT.
>> Had to open data source read-only.
>> INFO: Open of `test.ovf'
>>       using driver `VRT' successful.
>> OGR: GetLayerCount() = 1
>>
>> 1: PointData
>> ODBC: SQLDisconnect()
>>
>>
>>
>> Does the datatype of the FID column matter? I have it as an int, does it
>> need to be guid?
>>
>> David Lowther
>>  
>> Coordinate Solutions, Inc.
>>  
>>
>> -----Original Message-----
>> From: Brent Fraser [mailto:bfraser at geoanalytic.com] 
>> Sent: Thursday, August 19, 2010 3:49 PM
>> To: David Lowther
>> Cc: gdal-dev at lists.osgeo.org
>> Subject: Re: [gdal-dev] OGR OVF has no identified FID column
>>
>> Yeah I saw that after I sent the message.  Take out the  <SrcSQL>, it's
>> really 
>> only useful if want to do "special" things, and it can kill spatial
> indexing
>> on 
>> ODBC datasources.  Does this work any better:
>>
>> <OGRVRTDataSource>
>>      <OGRVRTLayer name="UserPoint">
>>           <SrcDataSource>ODBC:un/pw at CLO,PointData</SrcDataSource>
>>           <GeometryField encoding="WKB" field="Feature"/>
>>           <FID>PKey</FID>
>>      </OGRVRTLayer>
>> </OGRVRTDataSource>
>>
>>
>> Brent
>>
>> David Lowther wrote:
>>> Brent,
>>>
>>> Thanks for the reply. On initial read I thought you had found my issue -
>>> that I was trying so many things I finally messed it all up. But on
> closer
>>> inspection I am aliasing the PKey column to FID in the SrcSQL (select
> PKey
>>> as FID, Feature from PointData).
>>>
>>> When <FID>FID</FID> ogrinfo returns:
>>>
>>> ODBC: ExecuteSQL(select PKey as FID, Feature from PointData) called.
>>> OGR_ODBC: Table SELECT has no identified FID column.
>>> OGR: OGROpen(test.ovf/00ED7090) succeeded as VRT.
>>> Had to open data source read-only.
>>> INFO: Open of `test.ovf'
>>>       using driver `VRT' successful.
>>> OGR: GetLayerCount() = 1
>>>
>>> 1: UserPoint
>>> OGR: ReleaseDataSource(ODBC:un/pw at CLO/02719D10) dereferenced and now
>>> destroying.
>>> ODBC: SQLDisconnect()
>>>
>>> When <FID>PKEY</FID> ogrinfo returns:
>>>
>>> ODBC: ExecuteSQL(select PKey as FID, Feature from PointData) called.
>>> OGR_ODBC: Table SELECT has no identified FID column.
>>> ERROR 1: Unable to identify FID field 'PKey'.
>>> OGR: ReleaseDataSource(ODBC:un/pw at CLO/02C39D18) dereferenced and now
>>> destroying.
>>> ODBC: SQLDisconnect()
>>> FAILURE:
>>> Unable to open datasource `test.ovf' with the following drivers.
>>>   -> ESRI Shapefile
>>>
>>>
>>> David Lowther
>>>  
>>> Coordinate Solutions, Inc.
>>>  
>>>
>>> -----Original Message-----
>>> From: Brent Fraser [mailto:bfraser at geoanalytic.com] 
>>> Sent: Thursday, August 19, 2010 3:15 PM
>>> To: David Lowther
>>> Cc: gdal-dev at lists.osgeo.org
>>> Subject: Re: [gdal-dev] OGR OVF has no identified FID column
>>>
>>> How about using:
>>> <FID>PKey</FID>
>>>
>>> but that doesn't explain why it takes a long time...
>>>
>>> Best Regards,
>>> Brent Fraser
>>>
>>>
>>> David Lowther wrote:
>>>> List,
>>>> 	
>>>> I have an OVF layer defined as follows:
>>>>
>>>> <OGRVRTDataSource>
>>>>         <OGRVRTLayer name="UserPoint">
>>>>          <SrcDataSource>ODBC:un/pw at CLO</SrcDataSource>
>>>>          <SrcSQL>select PKey as FID, Feature from PointData</SrcSQL>
>>>>          <GeometryField encoding="WKB" field="Feature"/>
>>>>   <FID>FID</FID>
>>>>         </OGRVRTLayer>
>>>> </OGRVRTDataSource>
>>>>
>>>> The ODBC connection is to a SQL Express 2008 database. The PointData
>> table
>>>> is defined as follows:
>>>>
>>>> CREATE TABLE [dbo].[PointData](
>>>>  [PKey] [int] IDENTITY(1,1) NOT NULL,
>>>>  [Feature] [geometry] NULL,
>>>>  CONSTRAINT [PK_PointData] PRIMARY KEY CLUSTERED 
>>>> (
>>>>  [PKey] ASC
>>>> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
>>>> OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
>>>> ) ON [PRIMARY]
>>>>
>>>> This query
>>>>
>>>> select *, Feature.STAsText() from pointdata
>>>>
>>>> yields
>>>>
>>>> 1 0x00000000010C0000000000C062400000000000C06240 POINT (150 150)
>>>>
>>>> "ogrinfo test.ovf --debug on" runs for a very long time returning many
>>> lines
>>>> of this sort of stuff:
>>>>
>>>> ERROR 4: Update access not supported for VRT datasources.
>>>> OGR_ODBC: EstablishSession(DSN:"CLO", userid:"un", password:"pw")
>>>> ODBC: SQLConnect(CLO)
>>>> ODBC: CatalogNameL: (null)
>>>> Schema name: (null)
>>>>
>>>> OGR_ODBC: Table PointData has no identified FID column.
>>>> OGR_ODBC: Table UserMapData has no identified FID column.
>>>> OGR_ODBC: Table CHECK_CONSTRAINTS has no identified FID column.
>>>>
>>>> before finally saying:
>>>>
>>>> OGR: OGROpen(ODBC:un/pw at CLO/02C79D10) succeeded as ODBC.
>>>> ODBC: ExecuteSQL(select PKey as FID, Feature from PointData) called.
>>>> OGR_ODBC: Table SELECT has no identified FID column.
>>>> OGR: OGROpen(test.ovf/00E17090) succeeded as VRT.
>>>> Had to open data source read-only.
>>>> INFO: Open of `test.ovf'
>>>>       using driver `VRT' successful.
>>>> OGR: GetLayerCount() = 1
>>>>
>>>> 1: UserPoint
>>>> OGR: ReleaseDataSource(ODBC:un/pw at CLO/02C79D10) dereferenced and now
>>>> destroying.
>>>> ODBC: SQLDisconnect()
>>>>
>>>> GDAL version is 1.6.0.
>>>>
>>>> I have tried using no FID tag and using SrcLayer instead of SrcSQL.
>>> Anybody
>>>> know what I could do to make it see the FID? I'm guessing it would be a
>>> lot
>>>> faster if it didn't check every table in the database for an FID...
>>>>
>>>> Thanks in advance for your assistance,
>>>>
>>>> Dave Lowther
>>>>
>>>
>>
>>
> 
> 
> 



More information about the gdal-dev mailing list