[postgis-users] Using box3d in a stored procedure with passed in variables

[Calum Byrom]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080506/85e00220/attachment.html>