ODBC failing to use bounds when accessing WKT in MDB or SQL Server

[crispin]

As discussed with Frank by email 25thOct07... SQL Server or Access MDB table contains WKT in a "memo" field, plus X/Y fields plus MINX, MINY, MAXX, MAXY fields defining the bounding boxes for each record. MapServer? connects using either of two methods (direct or OGRVRT): For example: 1. no OGRVRT: CONNECTION "ODBC:HBSMRGatewayLoader/@HBSMRGatewayKent" DATA 'viePolygons' (where this is the name of a view, as configured in my GEOMETRY_COLUMNS table)

2. using OGRVRT, for example: CONNECTION "<OGRVRTDataSource> <OGRVRTLayer name='GatewayGeometry?'> <SrcDataSource?>ODBC:username/password@HBSMRGatewayKent,GatewayGeometry</SrcDataSource> <SrcSQL>Select * from GatewayGeometry? where ([Geometry] LIKE '%Polygon%')</SrcSQL> <FID>FID</FID> <GeometryType?>wkbUnknown</GeometryType> <GeometryField? encoding='WKT' field='Geometry'/> </OGRVRTLayer> </OGRVRTDataSource>"

Both methods work fine, but I can see that it is not using the bounds information to make it fast/efficient. Each map draw pulls all data from the table/view. In Access I have proved this by setting the MINX(etc) fields to NULL - the polygons still draw. In SQL Server I have proved this using SQL Profiler, and I get the following sequence: SELECT f_table_name, f_geometry_column, geometry_type FROM geometry_columns

(this works OK)

exec ..sp_pkeys N'GatewayGeometry?', N, N

(this fails)

exec sp_pkeys N' ', N, NULL

(this returns nothing)

exec sp_columns N'GatewayGeometry?', NULL, NULL, NULL

(this lists the columns OK)

and finally SELECT * FROM GatewayGeometry?

This problem makes serving WKT in this way much too slow, and too heavy on processor/network. Any more than about 2000 polygons gets slow in my tests. If it could use the bounds, this would be much better.

Incidentally, if I use the XY with encoding='PointfromColumns?' it does query efficiently with spatial criteria, for example: SELECT * FROM GatewayGeometry? WHERE X > 614184.893276474 AND X < 631578.629575045 AND Y > 159333.447265398 AND Y < 171142.163554331

best wishes Crispin (crispin.flower(at)ntlworld.com)

[warmerdam]

A preliminary review suggests you named the spatial index fields MinX/etc instead of XMIN/etc.

Perhaps there was some misleading documentation somewhere that needs to be fixed?

[warmerdam]

Crispin supplied a new database with XMIN instead of MinX. The command:

ogrinfo -ro --debug on ODBC:bug1934 MonGeometry -spat 0 0 10 10

Resulted in debug output including:

OGR_ODBC: Table MonGeometry has geometry extent fields.
OGR_ODBC: ExecuteSQL(SELECT * FROM MonGeometry WHERE XMAX > 0.00000000 AND XMIN 
< 10.00000000 AND YMAX > 0.00000000 AND YMIN < 10.00000000)

So it appears that spatial filters applied against these extent fields are working (tested with trunk).

Option (2) at the top uses a VRT with a SrcSQL option. This definately will not take advantage of spatial extents. Option (1) looks ok, but it isn't exactly clear how this would map onto the .mdb file I was provided.

Could you provide details of how to demonstrate the problem with the provided .mdb file?

[crispin]

Replying to warmerdam: Hi, I've done more testing. Here is an extract from MapServer? .map file to connect to this MDB: LAYER

NAME "KentSQLpolys" CONNECTIONTYPE OGR CONNECTION "ODBC:@Geometry" # that is my system DSN name TEMPLATE "KentSMRSQLdata.html" DUMP true METADATA

"wms_title" "KentSQLpolys" "wms_srs" "EPSG:27700"

"gml_include_items" "all"

END TYPE POLYGON PROJECTION

"init=epsg:27700"

END STATUS DEFAULT

etc This WMS layer draws fine in MapInfo? Pro, including records where XMIN etc are NULL. For example the record where FID = 34204. This proves that MapServer?/OGR is NOT using the bounds. But I have also done the tests with ogrinfo, which show (as you said) that the bounds are used correctly by ogrinfo -spat. So the problem seems to lie in whatever calls MapServer? sends through OGR to create the WMS image. If I make a VRT connections using <GeometryField? encoding='PointFromColumns?' x='x' y='y'/> then the spatial extents are passed through (see my original post), so this suggests the problem is in OGR. I have tried changing case of the bounds column names, and also varying the data type (long integer, double etc) - no effect. Hope this helps, Crispin

PS Incidentally, while testing I came upon three other quirks:

1. if using a VRT connection to MDB, the field name of the geometry column must be lower-case or it does not work: CONNECTIONTYPE OGR CONNECTION "<OGRVRTDataSource>

<OGRVRTLayer name='MonGeometry?'>

<SrcDataSource?>ODBC:@Geometry</SrcDataSource>

<SrcLayer?>MonGeometry?</SrcLayer> <FID>FID</FID>

<GeometryType?>wkbPoint</GeometryType> <GeometryField? encoding='WKT' field='geometry'/>

</OGRVRTLayer>

</OGRVRTDataSource>"

2. I noticed that a VRT PointFromColumns? does not work if the X/Y fields are "long integer" data type in an MDB. When I changed them to Double data type, they drew fine. Not sure if that counts as a bug?

3. ogrinfo fails to find the FID column in this MDB. It reports "Table MonGeometry? has no identified FID column". I've tried upper and lower case name of "FID" field, but no luck.

[warmerdam]

Crispin,

Ah, somehow I had lost track of the fact you were using a VRT for MapServer?, but we were not using that in local testing. The way the ODBC database is setup (with a GEOMETRY_COLUMNS table) means the ODBC datasource can be directly used as a datasource instead of going through a VRT.

The VRT driver does not utilize xmin/ymin/xmax/ymax values on the underlying non-spatial datasource. So if you go through a VRT there is no way to utilize the spatial bounds info.

Hmm, rereading the original description, I see you do list direct access to the ODBC database as option(1). Are you sure that is the option you have been testing for spatial indexing after the change to XMIN instead of MinX?

Regarding your other points, I'll reply to them by email to avoid complicating this report.

[crispin]

Yes, I am definitely using a direct connection, as shown in the MapServer? .map extract in my previous post. I see that wrapped a bit and looked confusing, so here again: NAME "KentSQLpolys" CONNECTIONTYPE OGR CONNECTION "ODBC:@Geometry" TEMPLATE "KentSMRSQLdata.html" DUMP true METADATA

"wms_title" "KentSQLpolys" "wms_srs" "EPSG:27700" "gml_include_items" "all"

END TYPE POLYGON PROJECTION

"init=epsg:27700"

END STATUS DEFAULT etc. And it definitely draws polygons that have empty XMIN etc. thanks Crispin

[warmerdam]

Crispin,

OK, I think the next step is for you to boil down a mapfile and a mapserv commandline request I can issue to reproduce the problem (using the last .mdb file you provided).

[crispin]

MapServer? .map file

[crispin]

I've attached a .map file. This is used to serve this WMS:  http://www.norfolkheritageexplorer.no-ip.org/scripts/mapserv.exe?map=d:\websites\wms\map\wmskentsql.map This is accessing a bigger version of the MDB I sent (which I had cut down to make it small enough to send). The data is in British National Grid. I'm using MapServer? 4.61 I think. I'm viewing the data in MapInfo? Pro or ArcGIS clients. Do you need anything else? Crispin

[warmerdam]

Crispin,

I also need the exact request to be used with this map to demonstrate the problem (and a way for me to know if the problem is manifesting - though my own review of debug output should help me here).

I don't think the map you attached is really boiled down to the minimum complexity to demonstrate the problem, and I notice it also has dependencies on other external files - a symbol file at least.

I would appreciate your verifying the bug with mapserv run at the commandline (using the QUERY_STRING="request" syntax) before I go forward so I know I'm starting from the same point.

[crispin]

Frank Good news - I realised I was using MapServer? 4.6, so upgraded to later version (4.9). And now it DOES use the bounds when accessing the ODBC data source. It is now great - really fast and now looking like a viable solution. Sorry for wasting your time with this. Actually there may be a different bug, as I cannot get the GetFeatureInfo? calls to bring back attributes (this worked fine with MS 4.6 with the same settings!). But I'll not log this until more testing. yours Crispin

[warmerdam]

Crispin,

Please file that issue as a distinct bug (presumably against MapServer?) if needed. I'll close this one.