<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=583223913-07052008><FONT face=Arial
color=#0000ff size=2>Try</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=583223913-07052008><FONT face=Arial
color=#0000ff size=2>ST_SetSRID(ST_MakeBox3D(ST_MakePoint(west_in,south_in),
ST_MakePoint(east_in,north_in)),4326)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=583223913-07052008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=583223913-07052008><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=583223913-07052008><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Calum
Byrom<BR><B>Sent:</B> Tuesday, May 06, 2008 12:01 PM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Using
box3d in a stored procedure with passed invariables<BR></FONT><BR></DIV>
<DIV></DIV>Hi,<BR><BR>Hopefully this is trivial problem that someone can help me
out with quickly. Am trying to use the BOX3D function within a stored
procedure, using coordinate values passed in as parameters. The problem is
I cannot figure out how to specify the box3d command using these
parameters. I guess it has to do with variable expansion and the use of
apostrophes, and I've played around a lot with these, but cannot get things to
work. The code is as follows:<BR><BR>CREATE FUNCTION
add_spatiotemporal_row(original_document_id_in int, north_in real,
<BR> south_in real, west_in real, east_in real, start_time_in
timestamp, <BR> end_time_in timestamp) RETURNS void AS
<BR>$$<BR> DECLARE<BR>
spatial_db_id integer := null;<BR>
temporal_db_id integer := null;<BR> bbox
varchar := west_in || ' ' || south_in || ', ' || east_in || ' ' ||
north_in;<BR>
BEGIN<BR> -- This inserts a new row in
the spatial data table, if
required<BR> IF west_in is not null
AND east_in is not null AND<BR>
north_in is not null AND south_in is not null
THEN<BR>
INSERT INTO SPATIAL_DATA (spatial_data_id, geometry, create_date) VALUES
<BR>
(DEFAULT, <BR>
setsrid('BOX3D(west_in+" "+south_in+", "+east_in+"
"+north_in)'::box3d,4326),<BR>
current_timestamp);<BR><BR>
SELECT MAX(spatial_data_id) INTO spatial_db_id FROM
SPATIAL_DATA;<BR> END IF;<BR>...<BR><BR>-
this produces the following error:<BR><BR>Error: database error <class
'pg.DatabaseError'> error 'ERROR: BOX3D parser - couldnt parse.
It should look like: BOX3D(xmin ymin zmin,xmax ymax zmax) or BOX3D(xmin
ymin,xmax ymax)<BR>CONTEXT: SQL statement "INSERT INTO SPATIAL_DATA
(spatial_data_id, geometry, create_date) VALUES (DEFAULT,
setsrid('BOX3D(west_in+" "+south_in+", "+east_in+" "+north_in)'::box3d,4326),
current_timestamp)"<BR>PL/pgSQL function "add_spatiotemporal_row" line 9 at SQL
statement<BR><BR>- if I swap the quotes and apostrophes around I
get:<BR><BR>Error: database error <class 'pg.DatabaseError'> error
'ERROR: column "BOX3D(west_in+' '+south_in+', '+east_in+' '+north_in)"
does not exist<BR>LINE 1: ..., geometry, create_date) VALUES (DEFAULT,
setsrid("BOX3D(wes...<BR>
^<BR>QUERY: INSERT INTO SPATIAL_DATA (spatial_data_id, geometry,
create_date) VALUES (DEFAULT, setsrid("BOX3D(west_in+' '+south_in+', '+east_in+'
'+north_in)"::box3d,4326), current_timestamp)<BR><BR>- I get this error a lot if
I try and reference the variables directly in the call to BOX3D.<BR><BR>Lastly,
if I use the declared variable, bbox to create the box3d I get the
following:<BR><BR>Error: database error <class 'pg.DatabaseError'> error
'ERROR: BOX3D parser - couldnt parse. It should look like:
BOX3D(xmin ymin zmin,xmax ymax zmax) or BOX3D(xmin ymin,xmax
ymax)<BR>CONTEXT: SQL statement "INSERT INTO SPATIAL_DATA
(spatial_data_id, geometry, create_date) VALUES (DEFAULT,
setsrid('BOX3D(bbox)'::box3d,4326), current_timestamp)"<BR><BR>If I could
get the first and third example to expand out the passed in parameters then
things would work - please could someone help me here - it's driving me
crazy!<BR><BR>Thanks,<BR><BR>Calum.<BR><BR></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>