Two questions about OGR Virtual spatial data with WKT in SQL Server

[Crispin and Naomi]

Hi all
First post for me so hope it is not too dumb - have searched archives but
not found answers...
 
I have SQL Server tables containing WKT in a "memo" field, plus X/Y fields
plus MINX, MINY, MAXX, MAXY fields defining the bounding boxes for each
record.
 
I'm dishing this up as a WMS just fine through MapServer, and I love this,
it's so nice compared to shapefiles/MapInfo tables etc.  BUT two problems...
 
1. When I access the layer in MapInfo Pro, my "identify" tool
(GetFeatureInfo) works fine on MapServer POINT layersgenerated from the WKT,
but does not find anything for POLYGON or LINE layers. The layers are set up
identically (DUMP, META etc) - I can give more info if required.  Is
GetFeatureInfo not supported for WKT polys/lines?   Will it be in future?
 
 
2. Spatial indexing.  If I define a OGR VRT to use the X/Y fields, then the
query sent to SQL Server is intelligent, e.g.
SELECT * FROM GatewayGeometry WHERE X > 614184.893276474 AND X <
631578.629575045 AND Y > 159333.447265398 AND Y < 171142.163554331
which is nice and quick as these fields are indexed.
 
but if I use the WKT with this 
<GeometryField encoding='WKT' field='Geometry'/>
 
then MapServer calls the whole table:
SELECT * FROM GatewayGeometry    
(and then obviously does the spatial filtering in MapServer).
 
I've also tried the other method where I just connect with 
   CONNECTIONTYPE OGR
   CONNECTION "ODBC:myusername/mypassword at myDSN" 
   DATA "mytable"
and have a table in the database called Geometry_columns etc.
This works fine, but again it fails to send through any querying of the
bounding box and just queries the whole table every time.
 
I have seen conflicting messages on this in MapServer and OGR help. One bit
says it wants to use MINX etc as a spatial index, but another bit says that
WKT never uses a spatial index.
Seems to me there's no reason not to use the bounding boxes to speed things
up, but some dodgy stored procedure calls seems to come through to SQL
Server, and perhaps MapServer cannot work out whether the fields exist...
These commands hit the SQL server:
SELECT f_table_name, f_geometry_column, geometry_type FROM geometry_columns
   (OK)
exec ..sp_pkeys N'GatewayGeometry', N'', N''
    (fails)
exec sp_pkeys N' ', N'', NULL
    (returns nothing)
exec sp_columns N'GatewayGeometry', NULL, NULL, NULL
    (OK)
and finally
SELECT * FROM GatewayGeometry
 
Can I do better?   
 
best wishes
 
Crispin
 

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.488 / Virus Database: 269.15.3/1082 - Release Date: 20/10/2007
14:59
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20071021/b2c02fad/attachment.html