<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns0="urn:schemas-microsoft-com:office:smarttags">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="Street"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="country-region"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PersonName"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="address"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>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.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>-- Function: applyradioustraffic(clongitude double
precision, clatitude double precision, radiusinmeters double precision)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>-- DROP FUNCTION applyradioustraffic(clongitude double
precision, clatitude double precision, radiusinmeters double precision);<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>CREATE OR REPLACE FUNCTION applyradioustraffic(clongitude
double precision, clatitude double precision, radiusinmeters double precision)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> RETURNS SETOF t_traffic AS<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>$BODY$<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> DECLARE rec t_traffic%ROWTYPE;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> begin<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> FOR rec IN <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> SELECT avg(traffic_vol)from psl_traffic <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>where<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> st_dwithin(<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> transform(the_geom,2163),<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> transform(GeomFromText('POINT(' || clongitude || ' '
|| clatitude ||')',4326),2163),<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> radiusInMeters) and (cnt_type like 'ADT%')<o:p></o:p></span></font></p>
<p class=MsoNormal><st1:place w:st="on"><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>LOOP</span></font></st1:place><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -- additional processing if any goes here --<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> return next rec;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> END <st1:place w:st="on">LOOP</st1:place>;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> return;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> end;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>$BODY$<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> LANGUAGE 'plpgsql' VOLATILE;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>ALTER FUNCTION applyradioustraffic(clongitude double
precision, clatitude double precision, radiusinmeters double precision) OWNER
TO postgres;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>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. <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks & Regards,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><st1:PersonName w:st="on"><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>Santosh Gaikwad</span></font></st1:PersonName><o:p></o:p></p>
<p class=MsoNormal><font size=2 face="Times New Roman"><span style='font-size:
10.0pt'>Senior Software Developer</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 color=black face="Times New Roman"><span
style='font-size:10.0pt;color:black'>Saama Technologies (</span></font><font
size=2><span style='font-size:10.0pt'><ns0:place w:insAuthor="sgaikwad"
w:insDate="2007-12-18T15:30:00Z" w:endInsAuthor="sgaikwad"
w:endInsDate="2007-12-18T15:30:00Z"><ns0:country-region w:insAuthor="sgaikwad"
w:insDate="2007-12-18T15:30:00Z" w:endInsAuthor="sgaikwad"
w:endInsDate="2007-12-18T15:30:00Z"><st1:country-region w:st="on"><st1:place
w:st="on"><font color=black><span style='color:black'>India</span></font></st1:place></st1:country-region></ns0:country-region></ns0:place><font
color=black><span style='color:black'>) Pvt. Ltd.,<br>
</span></font>Unit No.101-102, First floor,<br>
Weikfield IT CITI INFO PARK, <br>
Weikfield estates, <br>
<st1:Street w:st="on"><st1:address w:st="on">Pune Nagar Road</st1:address></st1:Street>,<br>
Pune - 411 014. </span></font><font size=2><span lang=FR style='font-size:10.0pt'>India<br>
Phone : +91 20 66071397</span></font><span lang=FR><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 face="Times New Roman"><span lang=FR
style='font-size:10.0pt'>Mobile: +91-9422005927<br>
E-mail :Santosh.Gaikwad@saama.com</span></font><span lang=FR><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 face="Times New Roman"><span style='font-size:
10.0pt'><a href="http://www.saama.com"><span lang=FR>http://www.saama.com</span></a></span></font><span
lang=FR><o:p></o:p></span></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=FR
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>