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