Thanks - I managed to get things working in the end - thanks to a previous posting that I'd missed earlier on:<br><br><a href="http://postgis.refractions.net/pipermail/postgis-users/2007-May/015782.html" target="_blank">http://postgis.refractions.net/pipermail/postgis-users/2007-May/015782.html</a><br>
<br>- build the command string first, then run execute on it.<br><br><div class="gmail_quote">On Wed, May 7, 2008 at 2:59 PM, Obe, Regina <<a href="mailto:robe.dnd@cityofboston.gov">robe.dnd@cityofboston.gov</a>> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Try</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" 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><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Hope that helps,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Regina</font></span></div><br>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font face="Tahoma" size="2"><b>From:</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On Behalf Of </b>Calum
Byrom<br><b>Sent:</b> Tuesday, May 06, 2008 12:01 PM<div class="Ih2E3d"><br><b>To:</b>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div><b>Subject:</b> [postgis-users] Using
box3d in a stored procedure with passed invariables<br></font><br></div><div><div></div><div class="Wj3C7c">
<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></div></div></div>
<div><p></p><hr size="1">
<p><b>
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.
</b></p></div>
<p></p><hr size="1">
<p><b><font color="#339900" size="2"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </font></b></p> <p> </p>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>