<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1250">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.6000.16544" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=882490907-24102007>Hi
all,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=882490907-24102007>I
mailed some of this before, but trying again a bit clearer I
hope...</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=882490907-24102007></SPAN></FONT> </DIV>
<DIV><FONT><SPAN class=882490907-24102007>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=775083821-21102007>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.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=775083821-21102007></SPAN></FONT> </DIV>
<DIV><SPAN class=775083821-21102007><FONT face=Arial><FONT color=#0000ff><FONT
size=2>I'm dishing this up as a WMS just fine through MapServer<SPAN
class=882490907-24102007> , 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:</SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=775083821-21102007><FONT face=Arial><FONT color=#0000ff><FONT
size=2><SPAN
class=882490907-24102007></SPAN></FONT></FONT></FONT></SPAN> </DIV>
<DIV><SPAN class=775083821-21102007><FONT><FONT color=#0000ff><FONT size=2><SPAN
class=882490907-24102007><FONT face=Arial color=#000000>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.</FONT>
<DIV><SPAN class=639401110-21102007><FONT face=Arial color=#0000ff size=2>SELECT
* FROM GatewayGeometry WHERE X > 614184.893276474 AND X < 631578.629575045
AND Y > 159333.447265398 AND Y < 171142.163554331</FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial color=#0000ff size=2><SPAN
class=775083821-21102007>which is nice and quick as these fields are
indexed.</SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial color=#0000ff size=2><SPAN
class=775083821-21102007></SPAN></FONT></SPAN> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=775083821-21102007>but if I use the WKT
with this </SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=775083821-21102007><FONT face=Arial
color=#0000ff><GeometryField encoding='WKT'
field='Geometry'/></FONT></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=775083821-21102007></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=775083821-21102007>then MapServer calls
the whole table:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT color=#0000ff size=2><SPAN
class=639401110-21102007><FONT face=Arial color=#0000ff size=2>SELECT * FROM
GatewayGeometry
</FONT></SPAN></FONT></SPAN></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT size=2><SPAN class=639401110-21102007><FONT
face=Arial size=2>(and then obviously does the spatial filtering in
MapServer).</FONT></SPAN></FONT></SPAN></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007></SPAN></FONT></SPAN></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff size=2><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT color=#0000ff size=2><SPAN
class=639401110-21102007><FONT face=Arial><FONT color=#000000>I've also tried
the other method where I just connect with</FONT>
</FONT></SPAN></FONT></SPAN></SPAN></FONT></DIV>
<DIV><FONT size=+0><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><SPAN class=639401110-21102007><FONT face=Arial><FONT
color=#0000ff size=2> CONNECTIONTYPE OGR<BR> CONNECTION
"ODBC:myusername/mypassword@myDSN"</FONT> </FONT>
<DIV><SPAN class=639401110-21102007><FONT face=Arial color=#0000ff size=2><SPAN
class=639401110-21102007> DATA "<SPAN
class=775083821-21102007>mytable</SPAN>"</SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007><SPAN class=775083821-21102007>and have a table in the
database called Geometry_columns etc.</SPAN></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007><SPAN class=775083821-21102007>This works fine, but
again it fails to send through any querying of the bounding box and just queries
the whole table every time.</SPAN></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007><SPAN
class=775083821-21102007></SPAN></SPAN></FONT></SPAN> </DIV>
<DIV><SPAN class=639401110-21102007><FONT size=2><SPAN
class=639401110-21102007><SPAN class=775083821-21102007><FONT face=Arial>I have
seen <SPAN class=639401110-21102007><FONT size=2>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.</FONT></SPAN></FONT></SPAN></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007>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...</SPAN></SPAN></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007>These commands hit the SQL
server:</SPAN></SPAN></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT size=+0><SPAN
class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007>
<DIV><SPAN class=639401110-21102007><FONT face=Arial color=#0000ff size=2>SELECT
f_table_name, f_geometry_column, geometry_type FROM
geometry_columns</FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial size=2><SPAN
class=775083821-21102007> (OK)</SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial color=#0000ff size=2>exec
..sp_pkeys N'GatewayGeometry', N'', N''</FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT color=#0000ff><FONT size=2><FONT
face=Arial> <SPAN class=775083821-21102007><FONT
color=#000000>(fails)</FONT></SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT color=#0000ff size=2><SPAN
class=775083821-21102007><SPAN class=639401110-21102007><FONT face=Arial
color=#0000ff size=2>exec sp_pkeys N' ', N'',
NULL</FONT></SPAN></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT size=+0><SPAN
class=775083821-21102007><SPAN
class=639401110-21102007></SPAN></SPAN></FONT><SPAN
class=775083821-21102007></SPAN><FONT color=#0000ff><FONT size=2><FONT
face=Arial> <SPAN class=775083821-21102007><FONT color=#000000>
(returns nothing)</FONT></SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><FONT face=Arial color=#0000ff size=2>exec
sp_columns N'GatewayGeometry', NULL, NULL, NULL</FONT></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><FONT
color=#0000ff size=2><FONT face=Arial> <FONT
color=#000000>(OK)</FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><FONT
face=Arial size=2>and finally</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT size=2><SPAN class=639401110-21102007><FONT
face=Arial size=2>SELECT * FROM
GatewayGeometry</FONT></SPAN></FONT></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007></SPAN></FONT></SPAN></SPAN></SPAN> </DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><SPAN class=639401110-21102007><FONT face=Arial><FONT
size=2>Can I do better? <SPAN class=882490907-24102007>(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 !)</SPAN></FONT></FONT></SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007></SPAN></FONT></SPAN></SPAN></SPAN> </DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007>best wishes</SPAN></FONT></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007></SPAN></FONT></SPAN></SPAN></SPAN> </DIV>
<DIV><SPAN class=639401110-21102007><SPAN class=775083821-21102007><SPAN
class=639401110-21102007><FONT face=Arial size=2><SPAN
class=639401110-21102007>Crispin</SPAN></FONT></SPAN></SPAN></SPAN></DIV></SPAN></SPAN></SPAN></FONT></SPAN></DIV></SPAN></SPAN></SPAN></FONT></DIV></SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=775083821-21102007><FONT face=Arial><FONT color=#0000ff><FONT
size=2><SPAN
class=882490907-24102007> </SPAN></FONT></FONT></FONT></SPAN></SPAN></FONT></DIV></DIV></BODY></HTML>
<BR>
<P><FONT SIZE=2>No virus found in this outgoing message.<BR>
Checked by AVG Free Edition.<BR>
Version: 7.5.503 / Virus Database: 269.15.8/1089 - Release Date: 23/10/2007 19:39<BR>
</FONT> </P>