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.
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
