[postgis-users] Spatial index in stored function

[Santosh Gaikwad]

Hi,

 

I have created the spatial index on geometry column in my table. I also
made a stored function which uses the following query inside it.

 

-- Function: applyradioustraffic(clongitude double precision, clatitude
double precision, radiusinmeters double precision)

 

-- DROP FUNCTION applyradioustraffic(clongitude double precision,
clatitude double precision, radiusinmeters double precision);

 

CREATE OR REPLACE FUNCTION applyradioustraffic(clongitude double
precision, clatitude double precision, radiusinmeters double precision)

  RETURNS SETOF t_traffic AS

$BODY$

   DECLARE rec t_traffic%ROWTYPE;

   begin

       FOR rec IN 

            SELECT avg(traffic_vol)from psl_traffic  

where

     st_dwithin(

      transform(the_geom,2163),

      transform(GeomFromText('POINT(' || clongitude || ' ' || clatitude
||')',4326),2163),

      radiusInMeters) and (cnt_type like 'ADT%')

LOOP

           -- additional processing if any goes here --

                        return next rec;

            END LOOP;

       return;

   end;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION applyradioustraffic(clongitude double precision,
clatitude double precision, radiusinmeters double precision) OWNER TO
postgres;

 

When I run the query, it gives me faster result and shows it is using
spatial index after doing explain analyze. When I do explain analyze for
the stored function it doesn't not it is using spatial index. I would
like to know whether spatial indexes are getting used in stored function
or not. My stored function is result is bit slower. 

 

Thanks & Regards,

 

Santosh Gaikwad

Senior Software Developer

Saama Technologies (India) Pvt. Ltd.,
Unit No.101-102, First floor,
Weikfield IT CITI INFO PARK, 
Weikfield estates, 
Pune Nagar Road,
Pune - 411 014. India
Phone : +91 20 66071397

Mobile: +91-9422005927
E-mail :Santosh.Gaikwad at saama.com

http://www.saama.com <http://www.saama.com> 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071218/ebed796d/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Santosh Gaikwad.vcf
Type: text/x-vcard
Size: 145 bytes
Desc: Santosh Gaikwad.vcf
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071218/ebed796d/attachment.vcf>