OGR OVF has no identified FID column

Question

I have an OVF layer defined as follows:

<OGRVRTDataSource>
        <OGRVRTLayer name="UserPoint">
         <SrcDataSource>ODBC:un/pw@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@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@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.

Answer 1

A direct MSSQLSpatial driver was recently added to GDAL/OGR. The following command returns the expected (desired result):

D:\Utility\release-1600-x64-gdal-mapserver\bin\gdal\apps>ogrinfo -al "MSSQL:server=...;database=...;tables=pointdata;uid=...;pwd=..."


INFO: Open of `MSSQL:server=...;database=...;tables=...;uid=...;pwd=...'

      using driver `MSSQLSpatial' successful.

Layer name: pointdata
Geometry: Unknown (any)
Feature Count: 1
Extent: (100.000000, 100.000000) - (100.000000, 100.000000)
Layer SRS WKT:
(unknown)
FID Column = PKey
Geometry Column = Feature
WKT: String (0.0)
OGRFeature(pointdata):3
  WKT (String) = POINT (100 100)
  POINT (100 100)

Answer 2

This is not an answer to the question, but is related to the same topic and might help someone.

I upgraded my GDAL to version 1.7.1. The only thing I notice that is different is that I get:

OGR: OGRGeometryFactory::createFromWkb() - got corrupt data.

That gave me a hint. I added a WKT column to the MS SQL Server table and calculated it to the STAsText of the Feature column. Then I modified the ovf file to look for WKT in the WKT column. Still no FID, but the point data ogr sees is what I expect:

Layer name: PointData
Geometry: Unknown (any)
OGR_ODBC: ExecuteSQL(SELECT * FROM PointData)
Feature Count: 1
OGR_ODBC: ExecuteSQL(SELECT * FROM PointData)
Layer SRS WKT:
(unknown)
PKey: Integer (10.0)
Feature: Binary (0.0)
WKT: String (0.0)
OGR_ODBC: ExecuteSQL(SELECT * FROM PointData)
OGRFeature(PointData):3
  PKey (Integer) = 3
  Feature (Binary) = BC080000010C00000000000059400000000000005940
  WKT (String) = POINT (100 100)
  POINT (100 100)

Answer 3

<OGRVRTDataSource>
        <OGRVRTLayer name="UserPoint">
         <SrcDataSource shared="OFF">ODBC:un/pw@CLO</SrcDataSource>
         <SrcSQL>select * from PointData</SrcSQL>
         <SrcLayer>PointData</SrcData>
         <FID>PKey</FID>
         <GeometryField encoding="WKB" field="Feature"/>
        </OGRVRTLayer>
</OGRVRTDataSource>

I found if I have 1 layer it works fine however if i had many I had to assign this shared="OFF" ?

Answer 4

Could you perform something like this?

<OGRVRTDataSource>
        <OGRVRTLayer name="UserPoint">
         <SrcDataSource>ODBC:un/pw@CLO,PointData</SrcDataSource>
         <SrcLayer>PointData</SrcData>
         <FID>PKey</FID>
         <GeometryField encoding="WKB" field="Feature"/>
        </OGRVRTLayer>
</OGRVRTDataSource>

Explicitly state the FID, and just use the GeometryField to grab the spatial object itself.