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

[Calum Byrom]

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 <robe.dnd at cityofboston.gov>
wrote:

>  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. *
>
> ------------------------------
>
> * 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080507/5f9bc250/attachment.html>