<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR>
<STYLE>@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal-compose
}
P.trebuchet {
FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
LI.trebuchet {
FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
DIV.trebuchet {
FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007>James,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007>Its a bit strange you would post this since I
complained about this too very recently. But I think you want to post this
to Postgis-users instead of dev group.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007>Anyrate the short-answer is that BOXES are not really
geometries so you do have to expand it out to a Polygon.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007>Here is a function I posted that I use (not sure if
this is the best, but the best I have come up with as a workaround). Your
above is really just a BOX2D since you have no Z or m dimension so this should
work fine for you.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007>First you create a function like this - just run this
script in Postgresql</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=132211511-26102007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT><SPAN class=132211511-26102007>
<P><FONT face=Arial color=#0000ff size=2>CREATE FUNCTION pgis_makeboxgeom(xmin
float, ymin float, xmax float,</FONT></P>
<P><FONT face=Arial color=#0000ff size=2>ymax float, SRID int) </FONT></P>
<P><FONT face=Arial color=#0000ff size=2>RETURNS geometry As</FONT></P>
<P><FONT face=Arial color=#0000ff size=2>$$</FONT></P>
<P><FONT face=Arial color=#0000ff size=2>SELECT ST_GeomFromText('POLYGON((' ||
$1 || ' ' || $2 || ',' || $1 || '</FONT></P>
<P><FONT face=Arial color=#0000ff size=2>' || $4 || ',' || $3 || ' ' || $4 || ',
' || $3 || ' ' || $2 || ',' ||</FONT></P>
<P><FONT face=Arial color=#0000ff size=2>$1 || ' ' || $2 || '))', $5)</FONT></P>
<P><FONT face=Arial color=#0000ff size=2>$$</FONT></P>
<P><FONT face=Arial color=#0000ff size=2>language 'sql' IMMUTABLE
STRICT;</FONT></P>
<P><FONT face=Arial color=#0000ff size=2></FONT> </P>
<P><SPAN class=132211511-26102007><FONT face=Arial color=#0000ff
size=2>--</FONT></SPAN></P>
<P><SPAN class=132211511-26102007><FONT face=Arial color=#0000ff size=2>Then to
use - change your query to</FONT></SPAN></P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">select
pgis_makeboxgeom(m.mkt_bdry_min_long,
m.mkt_bdry_min_lat,m.mkt_bdry_max_long,m.mkt_bdry_max_lat,4326)::box3d</SPAN></SPAN></P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"></SPAN></SPAN><SPAN
class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">from markets m where
m.market_id = 1;</SPAN></SPAN></P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"></SPAN></SPAN> </P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">If you don't need it to be a
real box and are fine with geometry (you can simply leave out the CAST so
its)</SPAN></SPAN></P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">select
pgis_makeboxgeom(m.mkt_bdry_min_long,
m.mkt_bdry_min_lat,m.mkt_bdry_max_long,m.mkt_bdry_max_lat,4326)
</SPAN></SPAN></P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"></SPAN></SPAN><SPAN
class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">from markets m where
m.market_id = 1;</SPAN></SPAN></P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"></SPAN></SPAN> </P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><FONT face=Arial
color=#0000ff>Hope that helps,</FONT></SPAN></SPAN></P>
<P><SPAN class=132211511-26102007><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><FONT face=Arial
color=#0000ff>Regina</FONT></SPAN></SPAN></P></SPAN></SPAN></SPAN></FONT></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-devel-bounces@postgis.refractions.net
[mailto:postgis-devel-bounces@postgis.refractions.net] <B>On Behalf Of </B>James
DeMichele<BR><B>Sent:</B> Thursday, October 25, 2007 7:57 PM<BR><B>To:</B>
postgis-devel@postgis.refractions.net<BR><B>Subject:</B> [postgis-devel] BOX3D
creation issue (concat)<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Hi,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">I am having an issue
creating a BOX3D and using concat to create the BOX3D. Here is the table I have
that I am using to get my bounding points from:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">postgres=# select * from
markets;<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">market_id
| 1<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">name
| sample<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">days_to_old
| 45<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">display_name
| Sample Area<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">mkt_bdry_min_lat |
46.7285<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">mkt_bdry_min_long |
-123.038<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">mkt_bdry_max_lat |
48.2984<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">mkt_bdry_max_long |
-120.906<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">When I attempt to construct
a BOX3D using a query that concats these items together such
as:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">select 'BOX3D(' ||
m.mkt_bdry_min_long || ' ' || m.mkt_bdry_min_lat || ', ' || m.mkt_bdry_max_long
|| ' ' || m.mkt_bdry_max_lat || ')'::box3D from markets m where m.market_id =
1;<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">ERROR: BOX3D parser -
doesnt start with BOX3D(<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">----------------------<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">I found some information
that suggested I attempt to make a Geometry out of this string instead. So I
tried this:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">select
GeometryFromText('BOX3D(' || m.mkt_bdry_min_long || ' ' || m.mkt_bdry_min_lat ||
', ' || m.mkt_bdry_max_long || ' ' || m.mkt_bdry_max_lat || ')', 4326) from
markets m where m.market_id = 1;<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">ERROR: Invalid OGC WKT
(does not start with P,L,M,C or G)<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">----------------------<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">I also found some
information that suggests using “expand(geometry, float).” But, I don’t want to
have to figure out how to expand anything since I already have the exact
bounding box.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Any help in figuring out how
to construct this Box3D would be greatly
appreciated.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Thanks.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">-James<o:p></o:p></SPAN></FONT></P></DIV></BODY></HTML>