<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="PlaceName"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PersonName"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
h2
{margin-top:12.0pt;
margin-right:0cm;
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:0cm;
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;
border:none;
padding:0cm;
font-size:12.0pt;
font-family:Verdana;
font-weight:bold;}
p.titre20, li.titre20, div.titre20
{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;}
span.EmailStyle22
{mso-style-type:personal-reply;
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 */
@list l0
{mso-list-id:1341272332;
mso-list-template-ids:-2004483310;}
@list l0:level1
{mso-level-text:%1;
mso-level-tab-stop:1.0cm;
mso-level-number-position:left;
margin-left:1.0cm;
text-indent:-1.0cm;}
@list l0:level2
{mso-level-style-link:"Titre 2";
mso-level-text:"%1\.%2";
mso-level-tab-stop:70.9pt;
mso-level-number-position:left;
margin-left:70.9pt;
text-indent:-70.9pt;}
@list l0:level3
{mso-level-text:"%1\.%2\.%3";
mso-level-tab-stop:70.9pt;
mso-level-number-position:left;
margin-left:70.9pt;
text-indent:-70.9pt;}
@list l0:level4
{mso-level-text:"%1\.%2\.%3\.%4";
mso-level-tab-stop:70.9pt;
mso-level-number-position:left;
margin-left:70.9pt;
text-indent:-70.9pt;}
@list l0:level5
{mso-level-text:"%1\.%2\.%3\.%4\.%5";
mso-level-tab-stop:247.05pt;
mso-level-number-position:left;
margin-left:247.05pt;
text-indent:-3.0cm;}
@list l0:level6
{mso-level-number-format:none;
mso-level-text:"";
mso-level-tab-stop:3.0cm;
mso-level-number-position:left;
margin-left:3.0cm;
text-indent:-3.0cm;}
@list l0:level7
{mso-level-number-format:none;
mso-level-text:"";
mso-level-tab-stop:3.0cm;
mso-level-number-position:left;
margin-left:3.0cm;
text-indent:-3.0cm;}
@list l0:level8
{mso-level-number-format:none;
mso-level-text:"";
mso-level-tab-stop:3.0cm;
mso-level-number-position:left;
margin-left:3.0cm;
text-indent:-3.0cm;}
@list l0:level9
{mso-level-number-format:none;
mso-level-text:"";
mso-level-tab-stop:3.0cm;
mso-level-number-position:left;
margin-left:3.0cm;
text-indent:-3.0cm;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
-->
</style>
</head>
<body lang=FR link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Robert,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Actually the extent
function is what I needed ! I was focused on that box2d … <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Finally the query “select
extent(transform(the_geom,4326)) from mytable” takes 400 ms to perform on
100 000 records and returns a BBOX(…..). <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>That helped a LOT! Tons of
thanks! <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><strong><b><font size=1 color=black face=Verdana><span
lang=EN-US style='font-size:7.0pt;font-family:Verdana;color:black'>Marc</span></font></b></strong><font
size=2 color=navy face=Arial><span lang=EN-US style='font-size:10.0pt;
font-family:Arial;color:navy'><o:p></o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span lang=EN-US style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span lang=EN-US
style='font-size:10.0pt;font-family:Tahoma;font-weight:bold'>De :</span></font></b><font
size=2 face=Tahoma><span lang=EN-US style='font-size:10.0pt;font-family:Tahoma'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>De la part de</span></b> Burgholzer,Robert<br>
<b><span style='font-weight:bold'>Envoyé :</span></b> lundi 30 juin 2008
17:19<br>
<b><span style='font-weight:bold'>À :</span></b> PostGIS Users Discussion<br>
<b><span style='font-weight:bold'>Objet :</span></b> RE: [postgis-users]
Collecting points for the overall bounding box </span></font><span lang=EN-US><o:p></o:p></span></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
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><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>select
box2d(transform(setsrid(extent(</span></font><span lang=EN-US>geometry</span><font
size=2 color=navy face=Arial><span lang=EN-US style='font-size:10.0pt;
font-family:Arial;color:navy'>),26918),4326)) from </span></font><span
lang=EN-US>mytable<o:p></o:p></span></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'>Where you would put your geometries original
projection is in place of 26918.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'>HTH<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font><span
lang=EN-US><o:p></o:p></span></p>
<div>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Robert W. Burgholzer</span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Surface Water Modeler</span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Office of Water Supply
and Planning</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><st1:place w:st="on"><st1:PlaceName w:st="on"><font size=2
color=navy face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:
Arial;color:navy'>Virginia</span></font></st1:PlaceName><font size=2
color=navy face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:
Arial;color:navy'> <st1:PlaceName w:st="on">Department</st1:PlaceName></span></font></st1:place><font
size=2 color=navy face=Arial><span lang=EN-US style='font-size:10.0pt;
font-family:Arial;color:navy'> of Environmental Quality</span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><a
href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</a></span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>804-698-4405</span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Open Source Modeling
Tools:</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
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><span
lang=EN-US><o:p></o:p></span></p>
</div>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face=Tahoma><span
lang=EN-US 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>Burgholzer,Robert<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> PostGIS Users Discussion<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [postgis-users]
Collecting points for the overall bounding box </span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=3
face="Times New Roman"><span lang=EN-US style='font-size:12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>Marc,</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>Would the following work: box2d(transform( extent(wkb_geometry),
4326) )</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'> </span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>I think that you will achieve an efficiency from the following
points:</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'> </span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:72.0pt;text-indent:-18.0pt'><font size=3
color=navy face="Times New Roman"><span lang=EN-US style='font-size:12.0pt;
color:navy'>1.</span></font><font size=1 color=navy><span lang=EN-US
style='font-size:7.0pt;color:navy'> </span></font><font
size=2 color=navy face=Arial><span lang=EN-US 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><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:72.0pt;text-indent:-18.0pt'><font size=3
color=navy face="Times New Roman"><span lang=EN-US style='font-size:12.0pt;
color:navy'>2.</span></font><font size=1 color=navy><span lang=EN-US
style='font-size:7.0pt;color:navy'> </span></font><font
size=2 color=navy face=Arial><span lang=EN-US 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><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'> </span></font><span lang=EN-US><o:p></o:p></span></p>
<div>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>Robert W. Burgholzer</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>Surface Water Modeler</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>Office of Water Supply and Planning</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><st1:place w:st="on"><st1:PlaceName
w:st="on"><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Virginia</span></font></st1:PlaceName><font
size=2 color=navy face=Arial><span lang=EN-US style='font-size:10.0pt;
font-family:Arial;color:navy'> <st1:PlaceName w:st="on">Department</st1:PlaceName></span></font></st1:place><font
size=2 color=navy face=Arial><span lang=EN-US style='font-size:10.0pt;
font-family:Arial;color:navy'> of Environmental Quality</span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'><a href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</a></span></font><span
lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>804-698-4405</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial;
color:navy'>Open Source Modeling Tools:</span></font><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 color=navy
face=Arial><span lang=EN-US 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><span
lang=EN-US><o:p></o:p></span></p>
</div>
<p class=MsoNormal style='margin-left:72.0pt'><font size=2 face=Tahoma><span
lang=EN-US 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><st1:PersonName
ProductID="Marc Jacquin" w:st="on">Marc Jacquin</st1:PersonName><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><span lang=EN-US><o:p></o:p></span></p>
<p class=MsoNormal style='margin-left:72.0pt'><font size=3
face="Times New Roman"><span lang=EN-US style='font-size:12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'>Hi all,<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'> <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'>The request is quite simple, something
like:<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'> <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'>SELECT box2d(collect(transform(wkb_geometry,4326)))
FROM mytable<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'> <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US 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. <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'> <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'>Any idea would be helpful.<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'> <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'>Thx.<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'> <o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'>Marc<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:72.0pt'><font size=2 face="Courier New"><span
lang=EN-US style='font-size:10.0pt'> <o:p></o:p></span></font></p>
</div>
</body>
</html>