WKT in SQL Server

[Crispin and Naomi]

Hi all,
I mailed some of this before, but trying again a bit clearer I hope...
 
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 , but one problem
is that it drags the whole table out of the database for each request. I
have bounding box fields in the database and I was hoping it would use these
to pull only the features in the view extent. More detail:
 
Spatial indexing.  If I define a OGR VRT virtual data source to use just 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?   (and PS no I cannot just move it all into PostGIS, though
I realise that might be better until SQL 2008 is released with spatial
support !)
 
best wishes
 
Crispin
 

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.15.8/1089 - Release Date: 23/10/2007
19:39
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20071024/d20e828e/attachment.htm>