<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6487.1">
<TITLE>very Poor Performance for Contains</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=2 FACE="Verdana">Hello Ng,</FONT>
<BR><FONT SIZE=2 FACE="Verdana">we are testing the great PostGIS Extension with our own WebMapserver "iwan". </FONT>
<BR><FONT SIZE=2 FACE="Verdana">To connect to the Postgres Server we are using ODBC.</FONT>
<BR><FONT SIZE=2 FACE="Verdana">The PG 7.4.2 Server is running on SusE Linux, PostGis Version is 0.8, with GEOS,PROJ an STATS.</FONT>
</P>
<P><FONT SIZE=2 FACE="Verdana">we have two Problems:</FONT>
<BR><FONT SIZE=2 FACE="Verdana">~~~~~~~~~~~~~~~~~~~~~~</FONT>
<BR><FONT SIZE=2 FACE="Verdana"> 1. Spatial Operators are very slow</FONT>
<BR><FONT SIZE=2 FACE="Verdana">~~~~~~~~~~~~~~~~~~~~~~</FONT>
<BR><FONT SIZE=2 FACE="Verdana">The following Query will return exact one record, the Dataset contains 90.000 (big) Polygon records</FONT>
<BR><FONT SIZE=2 FACE="Verdana">-> Querying for BoundingBox compare only is very good ~47ms, </FONT>
</P>
<P><FONT SIZE=2 FACE="Verdana">SELECT gid,area,perimeter,s9_d_,s9_d_id,objart,bytea(AsBinary(the_geom)) FROM sn_f109_dis WHERE (THE_GEOM && GeometryFromText('POLYGON((4555478.6159 5704338.0931,4555478.6159 5704456.4274,4555587.0890 5704456.4274,4555587.0890 5704338.0931,4555478.6159 5704338.0931))',-1))</FONT></P>
<P><FONT SIZE=2 FACE="Verdana">-> the same Query with contains: ~4s (!)</FONT>
</P>
<P><FONT SIZE=2 FACE="Verdana">SELECT gid,area,perimeter,s9_d_,s9_d_id,objart,bytea(AsBinary(the_geom)) </FONT>
<BR><FONT SIZE=2 FACE="Verdana">FROM </FONT>
<BR><FONT SIZE=2 FACE="Verdana">sn_f109_dis WHERE </FONT>
<BR> <FONT SIZE=2 FACE="Verdana">(THE_GEOM && GeometryFromText('POLYGON((4555478.6159 5704338.0931,4555478.6159 5704456.4274,4555587.0890 5704456.4274,4555587.0890 5704338.0931,4555478.6159 5704338.0931))',-1))</FONT></P>
<P><FONT SIZE=2 FACE="Verdana">AND contains(THE_GEOM,GeometryFromText('POLYGON((4555478.6159 5704338.0931,4555478.6159 5704456.4274,4555587.0890 5704456.4274,4555587.0890 5704338.0931,4555478.6159 5704338.0931))',-1))</FONT></P>
<P><FONT SIZE=2 FACE="Verdana">Index,Vaccuum is present. As workaround for all spatial selections we filter by box only and progress using our own functions on the map server.</FONT></P>
<P><FONT SIZE=2 FACE="Verdana">~~~~~~~~~~~~~~~~~~~~~~</FONT>
<BR><FONT SIZE=2 FACE="Verdana"> 2. slow Performance for Big Resultset (eg. for Drawing)</FONT>
<BR><FONT SIZE=2 FACE="Verdana">~~~~~~~~~~~~~~~~~~~~~~</FONT>
<BR><FONT SIZE=2 FACE="Verdana">"Big" doesn't mean many rows, but large Datasetsize (huge number of verts). </FONT>
<BR><FONT SIZE=2 FACE="Verdana">The Statement:</FONT>
<BR><FONT SIZE=2 FACE="Verdana">SELECT bytea(AsBinary(THE_GEOM)),ObjArt FROM sn_f109_dis WHERE (THE_GEOM && GeometryFromText('POLYGON((4549872.5409 5697050.6780,4549872.5409 5707868.3971,4563796.5166 5707868.3971,4563796.5166 5697050.6780,4549872.5409 5697050.6780))',-1)) </FONT></P>
<P><FONT SIZE=2 FACE="Verdana">... has duration 1400ms (502 Records).</FONT>
</P>
<P><FONT SIZE=2 FACE="Verdana">without geometry (..SELECT Objart ...) the Query returns in 60ms.</FONT>
<BR><FONT SIZE=2 FACE="Verdana">(100MBit Network).</FONT>
</P>
<P><FONT SIZE=2 FACE="Verdana">The sum(mem_size(geom)) ist 3350356 </FONT>
<BR><FONT SIZE=2 FACE="Verdana">and sum(length(bytea(asbinary(the_geom)))) = 220778</FONT>
</P>
<P><FONT SIZE=2 FACE="Verdana">Greetings,</FONT>
<BR><FONT SIZE=2 FACE="Verdana">Rene Neidt</FONT>
</P>
<BR>
<P><FONT COLOR="#000080" SIZE=1 FACE="Verdana">__________________________________________________________________</FONT>
</P>
<P><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Rene Neidt</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Projektleiter Software</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">IDU Ingenieurgesellschaft für Datenverarbeitung und Umweltschutz mbH</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Theodor-Körner-Allee 16</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">02763 Zittau</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Germany</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Tel ++49 3583 61-15-37</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Fax ++49 3583 61-12-10</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Internet </FONT><U> <FONT COLOR="#0000FF" SIZE=1 FACE="Verdana"><A HREF="http://www.idu.de/">http://www.idu.de/</A></FONT></U><FONT COLOR="#000080" SIZE=1 FACE="Verdana">,</FONT><U> <FONT COLOR="#0000FF" SIZE=1 FACE="Verdana"><A HREF="http://www.webmapserver.de/">http://www.webmapserver.de/</A></FONT></U>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Verdana">Anfahrt:</FONT><U> <FONT COLOR="#0000FF" SIZE=1 FACE="Verdana"><A HREF="http://www.stadtplan-zittau.de/?alias=idu">http://www.stadtplan-zittau.de/?alias=idu</A></FONT></U>
</P>
<P><FONT FACE="Arial" SIZE=2 COLOR="#000000"> <<Rene Neidt.vcf>> </FONT>
</BODY>
</HTML>