Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
Thanks to all that offered ideas on this one. As it turns out the addition of the MSSQLSpatial driver makes this a mute point... –  Dave Lowther Sep 13 '10 at 15:51
add comment

4 Answers

up vote 0 down vote accepted

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)
share|improve this answer
add comment

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)
share|improve this answer
add comment
<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" ?

share|improve this answer
    
I've just got the one layer, but I tried it anyway. No difference, unfortunately. –  Dave Lowther Aug 26 '10 at 11:09
add comment

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.

share|improve this answer
    
that's way faster, but still I get OGR_ODBC: Table PointData has no identified FID column. –  Dave Lowther Aug 20 '10 at 0:28
    
could you also try: ogrinfo test.ovf --debug on --config ODBC_OGR_FID 'PKey'? If that doesn't work, I'd hope that Frank responds to your gdal-dev post ;-) –  scw Aug 20 '10 at 3:35
    
weird... that gets me the same result. i'll post back with anything from the list that helps. thanks for your help. –  Dave Lowther Aug 20 '10 at 10:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.