[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: [postgis-users] Spatial index in stored function
From: "Santosh Gaikwad" <Santosh.Gaikwad () saama ! com>
Date: 2007-12-18 10:16:54
Message-ID: 58276F8B2051DF43A7FCA653A4768AC902083754 () si-pu-k3-exsvr ! main-bom-in ! saama ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@saama.com
http://www.saama.com <http://www.saama.com>
[Attachment #5 (text/html)]
<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>
["Santosh Gaikwad.vcf" (text/x-vcard)]
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic