[postgis-users] BOX3D functions

[Mikael Carneholm]

Hi,

I have problems creating a function that creates a bounding box given the coordinates for a point. It seems to be the same problem as in
http://postgis.refractions.net/pipermail/postgis-users/2005-November/009837.html.

Here's the first version:

create or replace function createbb(latitude numeric, longitude numeric, size_m integer) returns box3d as $$
declare
	lat_multiplier numeric := 0.0000110574;
	long_multiplier numeric := 0.0000111319;	
begin
     return 'BOX3D('||
		(latitude+size_m*lat_multiplier)||' '||(longitude+size_m*long_multiplier)||', '||
		(latitude-size_m*lat_multiplier)||' '||(longitude-size_m*long_multiplier)||')'::box3d;
end;
$$
language plpgsql;

=> select createbb(53.948821,10.858131,1000);

ERROR:  BOX3D parser - doesnt start with BOX3D(
CONTEXT:  SQL statement "SELECT  'BOX3D('|| ( $1 + $2 * $3 )||' '||( $4 + $5 * $6 )||', '|| ( $7 - $8 * $9 )||' '||( $10 - $11 * $12 )|| ')'::box3d"
PL/pgSQL function "createbb" line 5 at return



Using the suggested expand(geometry,float)::box3d solution to return a box3d works in that it does create a box3d:

create or replace function createbb2(latitude numeric, longitude numeric, size_m integer) returns box3d as $$
declare
	latlong_multiplier numeric := 0.0000111319;	
begin
     return expand( makepoint(latitude,longitude), (size_m*latlong_multiplier) )::box3d;
end;
$$
language plpgsql;

=> select createbb2(53.948821,10.858131,1000);

BOX3D(53.937686920166 10.8469982147217 0,53.9599533081055 10.8692636489868 0)

However, this box3d does not work as expected:

=> explain select
    p.point__id,
    p.latitude,
    p.longitude
from point p
where 
    p.geo && SetSRID(createbb2(53.948821,10.858131,100), 3007);

                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Seq Scan on point p  (cost=0.00..81962.20 rows=6 width=37)
   Filter: (geo && setsrid((createbb2(53.948821, 10.858131, 100))::geometry, 3007))
(2 rows)

Reference plan when not using createbb2 ("manual" box3d creation):

=> explain select 
    p.point__id, 
    p.latitude, 
    p.longitude
from point p
where 
    p.geo && SetSRID('BOX3D(47.2 -1.68, 46.0 -1.67)'::box3d, 3007);

                                                                                                              QUERY PLAN                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_point_geo on point p  (cost=0.00..114.35 rows=28 width=37)
   Index Cond: (geo && '0103000020BF0B000001000000050000000000000000004740E17A14AE47E1FABF0000000000004740B81E85EB51B8FABF000000A099994740B81E85EB51B8FABF000000A099994740E17A14AE47E1FABF0000000000004740E17A14AE47E1FABF'::geometry)
(2 rows)

Strange, huh?

I would still prefer to be able to use the first version ("createbb") as expand doesn't let you scale the x,y dimensions differently. But for now I just want to make use of the index, any ideas why this is not happening?

Btw:
select version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20050227 (Red Hat 3.4.3-22.fc3)
(1 row)

select postgis_version();
            postgis_version            
---------------------------------------
 1.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

/Mikael