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

[Obe, Regina]

Try
ST_SetSRID(ST_MakeBox3D(ST_MakePoint(west_in,south_in),
ST_MakePoint(east_in,north_in)),4326)
 
Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Calum Byrom
Sent: Tuesday, May 06, 2008 12:01 PM
To: postgis-users at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080507/c7d03247/attachment.html>