[postgis-users] Query crashed

[Mikael Carneholm]

Thanks, that worked:

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 immutable;

explain analyze
select
      p.point__id,
      p.latitude,
      p.longitude,
      p.altitude_meters
from point p
where p.geo && SetSRID(createbb2(53.948821,10.858131,1000), 3007)
     and distance (
        p.geo,
        creategeom(53.948821,10.858131,3007)
    ) <= 1000;

                                                                                                              QUERY PLAN                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_point_geo on point p  (cost=0.00..6.02 rows=1 width=45) (actual time=1.921..33.662 rows=414 loops=1)
   Index Cond: (geo && '0103000020BF0B000001000000050000000000002006F84A40000000C0A9B125400000002006F84A400000002010BD2540000000C0DFFA4A400000002010BD2540000000C0DFFA4A40000000C0A9B125400000002006F84A40000000C0A9B12540'::geometry)
   Filter: (distance(geo, creategeom(53.948821, 10.858131, 3007)) <= 1000::double precision)
 Total runtime: 34.407 ms


Still, I would be happier if the box3d parser was fixed. Anyone working on that?

/Mikael


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of Mark
Cave-Ayland
Sent: den 22 november 2005 13:21
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Query crashed


> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Mikael Carneholm
> Sent: 22 November 2005 10:59
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] Query crashed
> 
> This seems to be related:
> http://postgis.refractions.net/pipermail/postgis-users/2005-
> November/010029.html
> 
> /Mikael


Hi Mikael,

Yes I believe it is probably a similar issue because of the articially low
cost of the function. There was some discussion on the list that the planner
would be able inline SQL function if they were declared IMMUTABLE so you
might like to try something like:

CREATE OR REPLACE FUNCTION createbb2(latitude numeric, longitude numeric,
size_m integer) returns box3d as $$
	SELECT expand(makepoint(latitude,longitude), 0.0000111319 *
size_m)::box3d;
$$ LANGUAGE 'SQL' IMMUTABLE;

If you are fortunate, you may find that the planner will be able to inline
the function for you and hence give a better query plan.


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users