Thread: point <-> polygon not supported?
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I’m using version 7.3.2. I got the following error when I executed the <span class="SpellE">sql</span>statement in psql: </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><span class="SpellE"><span class="GramE"><font face="Arial" size="2"><spanstyle="font-size:10.0pt;font-family:Arial">llogicdb</span></font></span></span><font face="Arial" size="2"><spanstyle="font-size:10.0pt;font-family:Arial">=> select ID, POINAME, ADDRESS, (<span class="SpellE">GEOM::point</span><-> polygon('((-122.406903</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">, 37.785768)<span class="GramE">,(</span>-122.405903, 37.785768),(-122.405903, 37.786768),(-122.406903,37</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">.786768))')) * 87878.15460046429 as CFF_DISTANCE from NT_SF_POI where (FACILITY</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">TYPE='3578') and (<span class="SpellE">GEOM::point</span> <-> <span class="GramE">polygon(</span>'((-122.406903,37.785768),(-122.4059</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">03, 37.785768)<span class="GramE">,(</span>-122.405903, 37.786768),(-122.406903, 37.786768))')) * 87878.15</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">460046429 <= 3.41381770434236E-5 order by CFF_DISTANCE;</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">ERROR:<span style="mso-spacerun:yes"> </span>Unable to identify an operator '<->' for types 'point'and 'polygon'</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"><span style="mso-spacerun:yes"> </span>You will have to retype this query using an explicit cast</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Does this mean that the <-> operator does not support distance between a point and a simple polygon?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">ScottDing</span></font><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial"></span></font></div>
Scott Ding wrote: > I’m using version 7.3.2. I got the following error when I executed the > sql statement in psql: <cut> > Does this mean that the <-> operator does not support distance between a > point and a simple polygon? No. Try this query: select o.oprname,t1.typname as left, t2.typname as right from pg_operator o join pg_type t1 on (o.oprleft=t1.oid) join pg_type t2 on (o.oprright=t2.oid) where oprname='<->'; Regards, Tomasz Myrta
Tomasz, Thanks for the tip. What I want to do is something like the following: Select * from table where (table.geom <-> polygon('((3,3), (3,4), (4,5), (5,4), (5,3))')) < 1.0); Table.geom is a point column. How do I modify your statement to accomplish the above? Regards, Scott Ding -----Original Message----- From: Tomasz Myrta [mailto:jasiek@klaster.net] Sent: Thursday, February 20, 2003 6:55 AM To: Scott Ding Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] point <-> polygon not supported? Scott Ding wrote: > I'm using version 7.3.2. I got the following error when I executed the > sql statement in psql: <cut> > Does this mean that the <-> operator does not support distance between a > point and a simple polygon? No. Try this query: select o.oprname,t1.typname as left, t2.typname as right from pg_operator o join pg_type t1 on (o.oprleft=t1.oid) join pg_type t2 on (o.oprright=t2.oid) where oprname='<->'; Regards, Tomasz Myrta ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Scott Ding wrote: > Tomasz, > > Thanks for the tip. What I want to do is something like the following: > > Select * from table where (table.geom <-> polygon('((3,3), (3,4), (4,5), > (5,4), (5,3))')) < 1.0); > > Table.geom is a point column. I have never user geometric types... I think, you can use some data type, which can be used for counting distance, for example circle: Select * from table where (circle(table.geom,0) <-> polygon('((3,3), (3,4), (4,5), (5,4), (5,3))')) < 1.0); Regards, Tomasz Myrta
Tomasz, This works! Thanks, Scott Ding -----Original Message----- From: Tomasz Myrta [mailto:jasiek@klaster.net] Sent: Thursday, February 20, 2003 9:06 AM To: Scott Ding Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] point <-> polygon not supported? Scott Ding wrote: > Tomasz, > > Thanks for the tip. What I want to do is something like the following: > > Select * from table where (table.geom <-> polygon('((3,3), (3,4), (4,5), > (5,4), (5,3))')) < 1.0); > > Table.geom is a point column. I have never user geometric types... I think, you can use some data type, which can be used for counting distance, for example circle: Select * from table where (circle(table.geom,0) <-> polygon('((3,3), (3,4), (4,5), (5,4), (5,3))')) < 1.0); Regards, Tomasz Myrta ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org