<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
h2
{margin-top:12.0pt;
margin-right:0in;
margin-bottom:3.0pt;
margin-left:70.9pt;
text-indent:-70.9pt;
page-break-after:avoid;
font-size:14.0pt;
font-family:Arial;
font-style:italic;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
p.titre2, li.titre2, div.titre2
{margin-top:32.0pt;
margin-right:1.3pt;
margin-bottom:12.0pt;
margin-left:70.9pt;
text-indent:-70.9pt;
page-break-after:avoid;
font-size:12.0pt;
font-family:Verdana;
font-weight:bold;}
span.emailstyle19
{font-family:Arial;
color:navy;}
span.EmailStyle20
{font-family:Arial;
color:navy;}
@page Section1
{size:595.3pt 841.9pt;
margin:70.85pt 69.6pt 70.85pt 69.6pt;}
div.Section1
{page:Section1;}
/* List Definitions */
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Just to clarify, I didn’t test out
the query before I sent it. If you use the syntax that I gave, you will
need to set the SRID on the geometry returned from extent(), as follows:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>select box2d(transform(setsrid(extent(</span></font>geometry<font
size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'>),26918),4326)) from </span></font>mytable</p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Where you would put your geometries original projection is in place of
26918.</span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>HTH</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<div>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Robert W. Burgholzer</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Surface Water Modeler</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Office of Water Supply and Planning</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Virginia Department of Environmental
Quality</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</a></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>804-698-4405</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Open Source Modeling Tools:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</a></span></font></p>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b></span></font><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>Burgholzer,Robert</span></font><font
size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'><br>
<b><span style='font-weight:bold'>Sent:</span></b> Monday, June 30, 2008 10:08
AM<br>
<b><span style='font-weight:bold'>To:</span></b> </span></font><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>PostGIS Users
Discussion</span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'><br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [postgis-users]
Collecting points for the overall bounding box </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Marc,</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Would the following work:
box2d(transform( extent(wkb_geometry), 4326) )</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>I think that you will
achieve an efficiency from the following points:</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:1.0in;text-indent:-.25in'><font size=3
color=navy face="Times New Roman"><span style='font-size:12.0pt;color:navy'>1.<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></font><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>you should not need to
collect, if all you want is the bounding box, since extent() should give you
that</span></font></p>
<p class=MsoNormal style='margin-left:1.0in;text-indent:-.25in'><font size=3
color=navy face="Times New Roman"><span style='font-size:12.0pt;color:navy'>2.<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></font><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Even if you DO have to
use collect, you will save lots of computation time by doing the transform on
the final aggregate (1 transform), rather than for every record (100,000
transforms).</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Robert W. Burgholzer</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Surface Water Modeler</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Office of Water Supply
and Planning</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Virginia Department of
Environmental Quality</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'><a
href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</a></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>804-698-4405</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Open Source Modeling
Tools:</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'><a
href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</a></span></font></p>
</div>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Marc Jacquin<br>
<b><span style='font-weight:bold'>Sent:</span></b> Monday, June 30, 2008 9:23
AM<br>
<b><span style='font-weight:bold'>To:</span></b> 'PostGIS Users Discussion'<br>
<b><span style='font-weight:bold'>Subject:</span></b> [postgis-users]
Collecting points for the overall bounding box </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>Hi all,</span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>The request is quite simple, something like:</span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>SELECT box2d(collect(transform(wkb_geometry,4326))) FROM
mytable</span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>Mytable contains about 100000 records of a point type
geometry. A GIST index is available for the table but it takes <b><span
style='font-weight:bold'>several minutes</span></b> on a respectable recent
dual-core with 4 Gb of RAM. </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>Any idea would be helpful.</span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>Thx.</span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> </span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>Marc</span></font></p>
<p class=MsoPlainText style='margin-left:1.0in'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> </span></font></p>
</div>
</body>
</html>