[postgis-users] Problem with the overlap operator && (?)
Rob Tester
robtester at gmail.com
Wed Nov 15 05:59:22 PST 2006
I understand that the && uses bounding boxes and I also understand why the
index uses bounding boxes.
This seems to be a new behavior with the data.
I can tell you that spatially, they are not close. You would have to draw a
bounding box for both of them that would be larger than required to get that
to happen.
It should also mean that this query:
select
overlaps(envelope(a.wkb_geometry),envelope(b.wkb_geometry)),a.cr_id,b.ogc_fid,b.blockgroupid,b.blockid
from az_carrierroute9 a,az_kz_calculated_test b where a.ogc_fid=2203 and
b.ogc_fid=293546 and a.wkb_geometry&&b.wkb_geometry
Would return TRUE for the overlaps(geometry,geometry), but it does not. It
still returns FALSE which is puzzling me.
Geometry 1-
MULTIPOLYGON(((-111.127256 33.284309,-111.123972 33.284791,-111.124819
33.296575,-111.117514 33.317232,-111.101362 33.326736,-111.086473
33.340807,-111.092134 33.34884,-111.08693 33.356057,-111.048166
33.318328,-111.053736 33.314172,-111.090787 33.302698,-111.087266
33.295347,-111.091988 33.292795,-111.100199 33.28848,-111.103485
33.288164,-111.103945 33.286973,-111.108114 33.286857,-111.112283
33.283821,-111.113521 33.284236,-111.113686 33.281877,-111.115918
33.281868,-111.127256 33.284309),(-111.107079 33.301021,-111.108317
33.300412,-111.107646 33.300558,-111.107079 33.301021),(-111.123411
33.286903,-111.123448 33.286839,-111.123342 33.286902,-111.123411
33.286903)),((-111.314552 33.08251,-111.31396 33.0826,-111.314319
33.082485,-111.315002 33.082063,-111.315057 33.082525,-111.314552
33.08251)))
Geometry 2 -
POLYGON((-111.215261 33.109331,-111.215533 33.109223,-111.215996
33.108925,-111.216132 33.108696,-111.217139 33.107871,-111.218174
33.107687,-111.218936 33.107458,-111.219344 33.10716,-111.22065
33.105418,-111.220719 33.105375,-111.221058 33.105166,-111.22133
33.105143,-111.222147 33.105739,-111.222365 33.105807,-111.222828
33.105784,-111.222991 33.105624,-111.223154 33.105097,-111.223345
33.104845,-111.225168 33.104592,-111.225794 33.10363,-111.226583
33.102392,-111.226855 33.102117,-111.228026 33.102208,-111.228597
33.102071,-111.229196 33.1023,-111.229605 33.102345,-111.230203
33.101956,-111.230965 33.100237,-111.232216 33.098472,-111.232668
33.097067,-111.232732 33.096868,-111.232868 33.096685,-111.23314
33.096547,-111.234528 33.096524,-111.236107 33.096752,-111.236498
33.096711,-111.238205 33.097541,-111.239398 33.098245,-111.239429
33.098286,-111.239892 33.098675,-111.240356 33.098883,-111.241171
33.099247,-111.241634 33.099454,-111.241964 33.099671,-111.242372
33.099939,-111.242887 33.100278,-111.243623 33.101148,-111.24414
33.101079,-111.245419 33.101239,-111.246209 33.101605,-111.247975
33.102024,-111.248414 33.102246,-111.249396 33.103047,-111.249559
33.103093,-111.250675 33.103116,-111.252036 33.103323,-111.253397
33.103873,-111.254132 33.103874,-111.255031 33.103599,-111.258733
33.103578,-111.259685 33.10367,-111.260447 33.103922,-111.261346
33.103739,-111.261645 33.103762,-111.262298 33.104015,-111.263006
33.103877,-111.269735 33.103847,-111.272425 33.103835,-111.273187
33.104087,-111.272588 33.104865,-111.272587 33.106171,-111.272423
33.107156,-111.272042 33.108485,-111.272041 33.109768,-111.271469
33.111349,-111.270842 33.112173,-111.269889 33.11286,-111.269753
33.11309,-111.269181 33.113616,-111.268473 33.114051,-111.268402
33.114066,-111.267819 33.114189,-111.267356 33.114532,-111.265559
33.116135,-111.264987 33.117029,-111.264034 33.117761,-111.263189
33.118815,-111.262863 33.119113,-111.261501 33.119502,-111.26082
33.119983,-111.260302 33.120647,-111.260193 33.121105,-111.259239
33.122663,-111.258966 33.123831,-111.258122 33.12506,-111.257521
33.125771,-111.25635 33.12687,-111.255178 33.128313,-111.254824
33.129023,-111.254497 33.129298,-111.254061 33.129481,-111.253816
33.129756,-111.253625 33.130237,-111.253107 33.130672,-111.252889
33.131497,-111.252098 33.133169,-111.251362 33.134016,-111.251226
33.134497,-111.251078 33.134726,-111.250676 33.135345,-111.250315
33.135894,-111.250192 33.136215,-111.249965 33.136834,-111.249747
33.137705,-111.249339 33.138346,-111.249204 33.139309,-111.248959
33.13979,-111.248549 33.14015,-111.243156 33.140447,-111.218147
33.141826,-111.217681 33.148859,-111.217254 33.155306,-111.217102
33.155449,-111.216421 33.156412,-111.216176 33.157076,-111.215959
33.157282,-111.215628 33.157381,-111.215033 33.157558,-111.214951
33.157649,-111.214951 33.157855,-111.215523 33.158405,-111.215496
33.158634,-111.214979 33.159139,-111.214924 33.160055,-111.214625
33.160536,-111.214298 33.160788,-111.214298 33.161155,-111.214625
33.16143,-111.214653 33.161911,-111.215198 33.162323,-111.215089
33.162896,-111.213999 33.163836,-111.214 33.164065,-111.214136
33.164156,-111.214136 33.16434,-111.213972 33.164546,-111.213973
33.165187,-111.213591 33.165554,-111.213019 33.165806,-111.212584
33.166173,-111.212066 33.16725,-111.212121 33.167594,-111.212257
33.167731,-111.21223 33.168029,-111.211359 33.169381,-111.211277
33.169747,-111.211141 33.169931,-111.210732 33.170114,-111.209779
33.170229,-111.206728 33.170184,-111.206156 33.170069,-111.20542
33.169725,-111.204466 33.169451,-111.203431 33.168649,-111.202859
33.168466,-111.201742 33.168305,-111.201664 33.16823,-111.201551
33.168122,-111.201442 33.167778,-111.20117 33.167504,-111.20117
33.167252,-111.201742 33.166908,-111.201933 33.166541,-111.20196
33.165968,-111.202286 33.165327,-111.202668 33.165121,-111.203431
33.165029,-111.203757 33.164823,-111.203866 33.164639,-111.203866
33.163998,-111.203703 33.1637,-111.202122 33.161959,-111.202121
33.161317,-111.202584 33.160767,-111.202557 33.160011,-111.202938
33.159461,-111.203265 33.158476,-111.203837 33.158155,-111.203918
33.157514,-111.20381 33.157353,-111.202856 33.157078,-111.202556
33.156872,-111.202202 33.156093,-111.202175 33.155727,-111.202311
33.155406,-111.202311 33.155062,-111.201984 33.154627,-111.201984
33.154375,-111.202066 33.154329,-111.202175 33.153871,-111.201575
33.153413,-111.201548 33.153161,-111.202174 33.152611,-111.202256
33.15158,-111.202174 33.15119,-111.201411 33.150091,-111.201302
33.149793,-111.201453 33.149617,-111.201793 33.14922,-111.202119
33.148395,-111.202691 33.147502,-111.202909 33.146907,-111.203018
33.146815,-111.203002 33.146677,-111.202991 33.146586,-111.203317
33.146059,-111.203399 33.145761,-111.203181 33.145646,-111.203045
33.145417,-111.202881 33.14528,-111.202146 33.145097,-111.201928
33.14489,-111.201819 33.144272,-111.201574 33.143607,-111.201547
33.142806,-111.2019 33.142301,-111.202173 33.141568,-111.202608
33.141454,-111.203208 33.141591,-111.203398 33.141385,-111.204542
33.141224,-111.204569 33.140926,-111.20416 33.140216,-111.204051
33.13985,-111.204133 33.139368,-111.204296 33.139048,-111.204296
33.13875,-111.20367 33.138292,-111.203697 33.137627,-111.204105
33.137238,-111.204242 33.136917,-111.204214 33.136367,-111.203615
33.136,-111.203343 33.135726,-111.20307 33.134626,-111.202579
33.13403,-111.202579 33.133959,-111.202579 33.133801,-111.20296
33.133297,-111.203123 33.132907,-111.203151 33.132541,-111.203069
33.132312,-111.202415 33.131762,-111.202415 33.131258,-111.203232
33.130822,-111.204648 33.130799,-111.205465 33.130639,-111.207153
33.129653,-111.207588 33.12892,-111.207779 33.128828,-111.208133
33.128439,-111.208296 33.128095,-111.208677 33.127637,-111.208732
33.127087,-111.208514 33.126606,-111.208541 33.125919,-111.208677
33.125407,-111.208685 33.12506,-111.20914 33.124093,-111.209275
33.122283,-111.209629 33.121046,-111.210037 33.120564,-111.21001
33.119923,-111.209683 33.11935,-111.209683 33.118709,-111.209955
33.117862,-111.210063 33.1158,-111.210254 33.114837,-111.210444
33.114379,-111.211043 33.11376,-111.211505 33.112798,-111.211914
33.112156,-111.212703 33.111331,-111.213138 33.11069,-111.21371
33.110346,-111.21385 33.110231,-111.214662 33.109567,-111.215261
33.109331),(-111.213018 33.162415,-111.212773 33.162507,-111.212637
33.162782,-111.212719 33.163057,-111.213237 33.163469,-111.213373
33.1634,-111.213128 33.163057,-111.213209 33.162851,-111.213454
33.162873,-111.214054 33.163194,-111.214381 33.163148,-111.214517
33.163034,-111.214489 33.162667,-111.214108 33.162507,-111.213018
33.162415))
----- Original Message -----
From: "Paul Ramsey" <pramsey at refractions.net>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Sent: Tuesday, November 14, 2006 10:07 PM
Subject: Re: [postgis-users] Problem with the overlap operator && (?)
> Greg is right, the issue is that Overlaps() tests geometry overlap while
> && tests bounding box overlap, and the two are only the same if both
> objects are boxes. :)
>
> But "why test bounding box overlaps???" you ask. Because that is an
> indexable relationship, while the strict test is not. So you use the two
> in combination. The && operator returns a superset of the eventual
> result, which you winnow using an exact function.
>
> The core trick of fast spatial database operations is to use the bounding
> box relation to return a superset and then filter than superset with an
> exact test.
>
> P.
>
> On 14-Nov-06, at 8:40 PM, Gregory S. Williamson wrote:
>
>> Rob,
>>
>> Without examples of the data involved, it's hard to say for sure, but
>> offhand perhaps the && operator, which uses bounding boxes only, does
>> find that the 2nd object's BB overlaps the first object's BB, but the
>> stricter (and slower, non-index using, "overlaps" function) is correctly
>> seeing that they do not really overlap. Think of a "C" shape with a much
>> smaller circle in the open area in the middle ... or a shape that has a
>> long diagonal, so the bounding box embraces a large are compared to the
>> actual area of the shape.
>>
>> If you can reduce this to a simple case and show the geometries I am
>> sure that smarter heads than mine could chime in.
>>
>> HTH
>>
>> Greg Williamson
>> DBA
>> GlobeXplorer LLC
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net on behalf of Rob
>> Tester
>> Sent: Tue 11/14/2006 8:21 PM
>> To: postgis-users at postgis.refractions.net
>> Cc: Subject: [postgis-users] Problem with the overlap operator && (?)
>>
>> I recently (with my install of 1.1.5) have run into something that is
>> puzzling me about the overlaps operator. I have two tables that I am
>> comparing the geometry objects using the following SQL:
>>
>> select overlaps (a.wkb_geometry,b.wkb_geometry),a.primaryKey,b.primaryKey
>> from az_cr9 a,az_kz b where a.primaryKey=2203 and b.primaryKey=293546
>> and
>> a.wkb_geometry&&b.wkb_geometry
>>
>> This returns one row:
>>
>> f;2203;293546
>>
>> My question is how does the overlaps operator return TRUE and the
>> overlaps
>> function return FALSE. In fact the two geometries in question do not
>> close
>> to each other. Is this operator broken?
>>
>> This query returns no rows as expected:
>>
>> select
>> overlaps
>> (a.wkb_geometry,b.wkb_geometry),a.cr_id,b.ogc_fid,b.blockgroupid,
>> b.blockid
>> from az_carrierroute9 a,az_kz_calculated_test b where a.ogc_fid=2203 and
>> b.ogc_fid=293546 and overlaps(a.wkb_geometry,b.wkb_geometry)
>>
>>
>> Any help on this?
>>
>> Rob
>>
>>
>>
>> -------------------------------------------------------
>> Click link below if it is SPAM gsw at globexplorer.com
>> "https://mailscanner.globexplorer.com/dspam/dspam.cgi?
>> signatureID=455a9686171332117817174&user=gsw at globexplorer.com&retrain=
>> spam&template=history&history_page=1"
>> !DSPAM:455a9686171332117817174!
>> -------------------------------------------------------
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list