Ticket #1934 (closed defect: worksforme)
ODBC failing to use bounds when accessing WKT in MDB or SQL Server
|Reported by:||crispin||Owned by:||warmerdam|
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
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)