Thanks - I managed to get things working in the end - thanks to a previous posting that I'd missed earlier on:
http://postgis.refractions.net/pipermail/postgis-users/2007-May/015782.html - build the command string first, then run execute on it. On Wed, May 7, 2008 at 2:59 PM, Obe, Regina <[EMAIL PROTECTED]> wrote: > Try > ST_SetSRID(ST_MakeBox3D(ST_MakePoint(west_in,south_in), > ST_MakePoint(east_in,north_in)),4326) > > Hope that helps, > Regina > > ------------------------------ > *From:* [EMAIL PROTECTED] [mailto: > [EMAIL PROTECTED] *On Behalf Of *Calum Byrom > *Sent:* Tuesday, May 06, 2008 12:01 PM > *To:* postgis-users@postgis.refractions.net > *Subject:* [postgis-users] Using box3d in a stored procedure with passed > invariables > > Hi, > > 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: > > CREATE FUNCTION add_spatiotemporal_row(original_document_id_in int, > north_in real, > south_in real, west_in real, east_in real, start_time_in timestamp, > end_time_in timestamp) RETURNS void AS > $$ > DECLARE > spatial_db_id integer := null; > temporal_db_id integer := null; > bbox varchar := west_in || ' ' || south_in || ', ' || east_in || ' > ' || north_in; > BEGIN > -- This inserts a new row in the spatial data table, if required > IF west_in is not null AND east_in is not null AND > north_in is not null AND south_in is not null THEN > 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); > > SELECT MAX(spatial_data_id) INTO spatial_db_id FROM > SPATIAL_DATA; > END IF; > ... > > - this produces the following error: > > 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) > 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)" > PL/pgSQL function "add_spatiotemporal_row" line 9 at SQL statement > > - if I swap the quotes and apostrophes around I get: > > Error: database error <class 'pg.DatabaseError'> error 'ERROR: column > "BOX3D(west_in+' '+south_in+', '+east_in+' '+north_in)" does not exist > LINE 1: ..., geometry, create_date) VALUES (DEFAULT, setsrid("BOX3D(wes... > ^ > 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) > > - I get this error a lot if I try and reference the variables directly in > the call to BOX3D. > > Lastly, if I use the declared variable, bbox to create the box3d I get the > following: > > 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) > CONTEXT: SQL statement "INSERT INTO SPATIAL_DATA (spatial_data_id, > geometry, create_date) VALUES (DEFAULT, setsrid('BOX3D(bbox)'::box3d,4326), > current_timestamp)" > > 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! > > Thanks, > > Calum. > > ------------------------------ > > * 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. * > > ------------------------------ > > * Help make the earth a greener place. If at all possible resist printing > this email and join us in saving paper. * > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
