<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Scott.<br>
<br>
I looks like it's your 'select * from getdrglaters(...)' that is in
error. <br>
<br>
In order to create a valid polygon, the exterior and interior rings
must close. In particular, the first point and the last point must be
the same. In your example, the point $1,$2 that you passed into your
function does not match $7,$8. <br>
<br>
Either change your query and pass 3-point polygons to your function, or
change your function.<br>
Adding the first point after the 4th in your function will solve your
problem and still let you pass in 4-point polygons. i.e.<br>
<blockquote><tt>CREATE OR REPLACE FUNCTION getdrglayers(float8, float8,
float8, float8, float8, float8, float8, float8) RETURNS SETOF geometry
AS<br>
$BODY$<br>
<br>
declare myview record;<br>
<br>
begin<br>
FOR myview IN <br>
SELECT a.geom <br>
FROM stockdrgmeta a,<br>
(SELECT GeomFromText('POLYGON ((' || $1 || ' ' || $2 ||
',' || $3 || ' ' || $4 || ',' || $5 || ' ' || $6 || ',' || $7 || ' ' ||
$8 || ',' || $1 || ' ' || $2 || '))', 4269) as geom) AS b<br>
WHERE a.geom && b.geom <br>
AND intersects(a.geom, b.geom) <br>
ORDER BY geom ASC<br>
LOOP<br>
return next myview;<br>
END LOOP;<br>
return;<br>
end;<br>
<br>
$BODY$<br>
LANGUAGE plpgsql;</tt><br>
</blockquote>
* Note: You will have to change your function to return a SETOF
geometry instead of a SETOF record, or postgres will complain 'ERROR:
a column definition list is required for functions returning "record"'.<br>
<br>
Also, be careful with the order you pass your arguments to your
function. It looks like the polygon you are trying to create is
invalid, that is, it self-intersects.<br>
<blockquote><tt>select isvalid('POLYGON((-110.96949012708
32.2491676567968, -110.883487063427 32.1943120714019, -110.88341713745
32.2491175557995, -110.969508426838 32.1943620668489, -110.96949012708
32.2491676567968))'::geometry);<br>
NOTICE: Self-intersection<br>
isvalid <br>
---------<br>
f<br>
(1 row)<br>
</tt></blockquote>
Hope this helps.<br>
Cheers,<br>
Kevin<br>
<br>
Scott Schulthess wrote:
<blockquote
cite="mid4BF377919225F449BB097CB76FFE9BC83DDB7D@ptolemy.topozone.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="Microsoft Word 11 (filtered medium)">
<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;}
pre
{margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
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></o:SmartTagType>
<div class="Section1">
<p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;">Hello,<o:p></o:p></span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;">I was wondering if you
could help me out. <o:p></o:p></span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;">I receive this error when
I try to run the spatial query. <o:p></o:p></span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;"><o:p> </o:p></span></font></p>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">Npgsql.NpgsqlException: ERROR: XX000: geometry contains non-closed rings<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"> at Npgsql.NpgsqlConnector.CheckErrors()<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"> at Npgsql.NpgsqlConnector.CheckErrorsAndNotifications()<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"> at Npgsql.NpgsqlCommand.ExecuteCommand()<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"> at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"> at Npgsql.NpgsqlCommand.ExecuteReader()<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"> at dbutils.getlayers(Int32 z, Double n1, Double e1, Double n2, Double e2, Int32 viewscale) in \dbutils.cs:line 47<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"> at GetLayers.GetLayerList(Int32 z, Double n1, Double e1, Double n2, Double e2, Int32 viewscale) in App_Code\GetLayers.cs:line <o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"><o:p> </o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">That’s from my .net webservice – I receive this from the command line.<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"><o:p> </o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">ERROR: geometry contains non-closed rings<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">CONTEXT: PL/pgSQL function "getdrglayers" line 3 at for over select rows<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"><o:p> </o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">CREATE OR REPLACE FUNCTION getdrglayers(float8, float8, float8, float8, float8, float8, float8, float8) RETURNS SETOF record AS<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">$BODY$<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">declare myview record;<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">begin<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">FOR myview IN SELECT sourcescale from stockdrgmeta where (GeomFromText('POLYGON ((' || $1 || ' ' || $2 || ',' || $3 || ' ' || $4 || ',' || <o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">$5 || ' ' || $6 || ',' || $7 || ' ' || $8 || '))', 4269) && geom) and intersects(GeomFromText('POLYGON ((' || $1 || ' ' || $2 || ',' || $3 <o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">|| ' ' || $4 || ',' || $5 || ' ' || $6 || ',' || $7 || ' ' || $8 || '))', 4269),geom) order by sourcescale asc<o:p></o:p></span></font></pre>
<pre><st1:place w:st="on"><font face="Courier New" size="2"><span
style="font-size: 10pt;">LOOP</span></font></st1:place><o:p></o:p></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">return next myview;<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">END <st1:place
w:st="on">LOOP</st1:place>;<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">return;<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">end;$BODY$<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"><o:p> </o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">And I execute the statement like so<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"><o:p> </o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">select * from getdrglayers('-110.96949012708','32.2491676567968','-110.883487063427','32.1943120714019','-110.88341713745','32.2491175557995','-110.969508426838','32.1943620668489') as (sourcescale int4);<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;"><o:p> </o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">Thanks!
<o:p></o:p></span></font></pre>
<pre><font face="Courier New" size="2"><span style="font-size: 10pt;">-Scott<o:p></o:p></span></font></pre>
</div>
<pre wrap="">
<hr size="4" width="90%">
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
<pre class="moz-signature" cols="1000">--
Kevin Neufeld,
Refractions Research Inc.,
<a class="moz-txt-link-abbreviated" href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a>
Phone: (250) 383-3022
Fax: (250) 383-2140 </pre>
</body>
</html>