11

I am working with census shapefiles (multipolygons) and I have another table with points. We record which census block each point is in. I have one point that returns false for all ST_Within and ST_Touches queries. It does, however, return true for ST_Intersects on one block.

If it were another polygon I can understand how this would happen but how can a single point (I've verified that this is the case) be true for Intersects but false for both Within and Touches? Examining it in QGis it appears that it should return true for Touches.

Our table has millions of points and only this single point seems to be having any issue. I've run ST_IsValid on both the polygon and the point.

I am fairly new to geometry data. Am I missing something about how this works?

And the polygon block (geoid: '170898507021157')

SELECT ST_Within(pt, geom), ST_Touches(pt, geom), ST_Intersects(pt, geom)
FROM ( VALUES
    (
        ST_SetSRID( ST_GeometryFromText($$MULTIPOLYGON(((-88.466933 42.139013,-88.464586 42.139034,-88.463821 42.13901,-88.463811 42.138813,-88.463815 42.138705,-88.46382 42.138572,-88.463849 42.138468,-88.463886 42.138398,-88.464044 42.138452,-88.464239 42.138464,-88.464616 42.138472,-88.464961 42.138468,-88.465318 42.13846,-88.465558 42.138464,-88.465695 42.138443,-88.465824 42.138402,-88.465969 42.138319,-88.466048 42.138248,-88.466106 42.138161,-88.466135 42.138057,-88.466148 42.1379,-88.466149 42.137731,-88.466143 42.137406,-88.466143 42.137086,-88.466131 42.136945,-88.466098 42.13685,-88.466027 42.136746,-88.46596 42.136677,-88.465799 42.136609,-88.465571 42.136559,-88.465359 42.136534,-88.465167 42.136547,-88.464221 42.136559,-88.464216 42.136248,-88.464199 42.13609,-88.464179 42.135999,-88.46412 42.13587,-88.464046 42.135779,-88.463847 42.135597,-88.463531 42.135373,-88.463341 42.135252,-88.46313 42.135068,-88.462915 42.134957,-88.462601 42.134865,-88.462337 42.134822,-88.461619 42.134822,-88.460882 42.134828,-88.46044 42.134834,-88.460047 42.13484,-88.460053 42.135105,-88.460047 42.135381,-88.460034 42.135461,-88.459985 42.135559,-88.459905 42.135651,-88.459825 42.135756,-88.459752 42.135848,-88.459703 42.135934,-88.459684 42.136001,-88.45966 42.136118,-88.459654 42.136259,-88.459647 42.136836,-88.459107 42.136824,-88.458855 42.136818,-88.45864 42.136855,-88.458407 42.136978,-88.458315 42.137082,-88.458272 42.13718,-88.458247 42.137291,-88.458235 42.137408,-88.458241 42.137537,-88.458247 42.137647,-88.458253 42.138421,-88.458235 42.139017,-88.457615 42.139023,-88.457197 42.139029,-88.456607 42.139066,-88.455926 42.139146,-88.455133 42.139256,-88.45474 42.139313,-88.454709 42.139196,-88.454716 42.13907,-88.454683 42.138859,-88.454646 42.138353,-88.454669 42.138226,-88.454628 42.137907,-88.454616 42.137669,-88.454626 42.137153,-88.454611 42.137072,-88.454576 42.136978,-88.454512 42.136892,-88.454395 42.136793,-88.454355 42.136747,-88.454314 42.13659,-88.454306 42.136383,-88.454232 42.136322,-88.454175 42.136293,-88.454087 42.136261,-88.453929 42.136244,-88.453854 42.136242,-88.45377 42.136248,-88.453595 42.136284,-88.453339 42.136363,-88.453245 42.136397,-88.453161 42.136445,-88.453091 42.136465,-88.452984 42.136463,-88.452758 42.136445,-88.452403 42.136441,-88.452284 42.13642,-88.452172 42.136401,-88.451882 42.136359,-88.451677 42.136347,-88.45101 42.136297,-88.450681 42.136267,-88.450621 42.136253,-88.450506 42.136233,-88.450417 42.136208,-88.450304 42.136162,-88.450194 42.13611,-88.45004 42.13605,-88.449814 42.135954,-88.449742 42.135934,-88.44962 42.135916,-88.449449 42.135913,-88.448948 42.135919,-88.448035 42.135911,-88.447642 42.135913,-88.447494 42.135908,-88.4472 42.135908,-88.447133 42.135897,-88.446934 42.13583,-88.446806 42.135794,-88.446619 42.135764,-88.44654 42.135733,-88.446374 42.135693,-88.446295 42.135691,-88.446062 42.135732,-88.445985 42.135752,-88.445877 42.135766,-88.445808 42.135768,-88.445736 42.135761,-88.445664 42.135738,-88.445495 42.135652,-88.445404 42.135599,-88.44532 42.135556,-88.445258 42.135506,-88.445209 42.135449,-88.445154 42.135363,-88.44512 42.135297,-88.445108 42.135274,-88.445041 42.135094,-88.444995 42.135001,-88.444959 42.134915,-88.444908 42.134837,-88.444846 42.134763,-88.444795 42.134717,-88.444739 42.134677,-88.444663 42.134636,-88.444579 42.134617,-88.444485 42.134605,-88.444233 42.134608,-88.444145 42.134617,-88.444044 42.134637,-88.443934 42.134652,-88.44379 42.134686,-88.443725 42.134696,-88.443549 42.134682,-88.44342 42.134663,-88.44335 42.134643,-88.443252 42.134599,-88.443115 42.134602,-88.442976 42.13462,-88.442873 42.134642,-88.442774 42.134674,-88.442618 42.134767,-88.442551 42.134783,-88.442501 42.134819,-88.442441 42.134853,-88.442345 42.134882,-88.44228 42.13488,-88.44221 42.134857,-88.442107 42.134807,-88.442023 42.134755,-88.441961 42.134695,-88.441917 42.134632,-88.44191 42.134553,-88.441919 42.134482,-88.441996 42.134302,-88.442027 42.134205,-88.442039 42.134084,-88.442036 42.134019,-88.442012 42.133923,-88.441966 42.133862,-88.44189 42.13383,-88.441743 42.133808,-88.441565 42.133793,-88.441433 42.133798,-88.441253 42.133811,-88.440949 42.13385,-88.440761 42.133852,-88.440644 42.133824,-88.440548 42.133781,-88.440298 42.133605,-88.440192 42.13351,-88.440153 42.133451,-88.440125 42.133391,-88.439999 42.13319,-88.439932 42.133128,-88.43987 42.133078,-88.439805 42.133033,-88.439663 42.132971,-88.439317 42.132844,-88.439019 42.132728,-88.438784 42.132647,-88.438695 42.132611,-88.438599 42.132588,-88.438405 42.132574,-88.438345 42.132561,-88.438273 42.132574,-88.438234 42.132576,-88.438001 42.132417,-88.437931 42.132361,-88.43785 42.132306,-88.437624 42.132201,-88.437463 42.132137,-88.437208 42.132053,-88.437129 42.132019,-88.436992 42.132,-88.436788 42.131979,-88.436678 42.131963,-88.436565 42.131939,-88.436488 42.131918,-88.436205 42.131804,-88.436123 42.131761,-88.436032 42.131731,-88.435981 42.131695,-88.435953 42.131665,-88.435936 42.131624,-88.435888 42.131579,-88.435626 42.131274,-88.435578 42.131238,-88.435522 42.131165,-88.435409 42.131043,-88.435294 42.130892,-88.435229 42.13084,-88.43502 42.130604,-88.434892 42.130467,-88.43439 42.129908,-88.434291 42.129803,-88.434111 42.129599,-88.434051 42.129544,-88.434007 42.129515,-88.4337 42.129405,-88.433628 42.129373,-88.433554 42.129314,-88.433501 42.129257,-88.433481 42.129203,-88.433475 42.129,-88.434945 42.128994,-88.435401 42.128955,-88.436779 42.128706,-88.437438 42.128631,-88.437986 42.128635,-88.438514 42.128698,-88.439106 42.128842,-88.441903 42.129725,-88.442869 42.130081,-88.444453 42.130597,-88.447242 42.131469,-88.447807 42.131653,-88.448286 42.131788,-88.448679 42.131886,-88.449035 42.131948,-88.449502 42.131977,-88.451438 42.132031,-88.452216 42.132022,-88.453027 42.132095,-88.453641 42.132193,-88.454022 42.132267,-88.455004 42.132451,-88.455582 42.132587,-88.456073 42.132771,-88.456687 42.133029,-88.457178 42.133274,-88.457433 42.133409,-88.458097 42.133717,-88.458735 42.134017,-88.458987 42.134122,-88.459504 42.134245,-88.459832 42.134282,-88.460495 42.134263,-88.461281 42.134263,-88.461905 42.134249,-88.462648 42.134259,-88.462991 42.134306,-88.463362 42.134392,-88.463604 42.134506,-88.463705 42.134554,-88.463991 42.134706,-88.464239 42.134897,-88.464391 42.135068,-88.46467 42.135368,-88.464905 42.135602,-88.465077 42.135754,-88.465286 42.135916,-88.465429 42.135983,-88.465667 42.136068,-88.465924 42.136106,-88.466362 42.136116,-88.466905 42.136087,-88.466935 42.137029,-88.466933 42.139013),(-88.458229 42.134374,-88.458161 42.134318,-88.457492 42.133962,-88.456546 42.133502,-88.456091 42.133293,-88.455809 42.133188,-88.455551 42.133133,-88.455471 42.133102,-88.455385 42.133158,-88.45533 42.133244,-88.455299 42.13336,-88.455275 42.133471,-88.455282 42.133657,-88.455274 42.133965,-88.455279 42.133988,-88.455294 42.134017,-88.455319 42.134042,-88.45533 42.13405,-88.455351 42.134062,-88.455388 42.134075,-88.455429 42.134081,-88.45546 42.13408,-88.455658 42.134071,-88.455717 42.13407,-88.455796 42.134076,-88.455874 42.134088,-88.455948 42.134108,-88.45602 42.134134,-88.456046 42.134146,-88.456482 42.134355,-88.457315 42.134739,-88.457836 42.135006,-88.457946 42.134877,-88.458112 42.134687,-88.458204 42.134552,-88.458247 42.134447,-88.458229 42.134374),(-88.452273 42.135697,-88.452285 42.135431,-88.452274 42.135336,-88.452194 42.134994,-88.452165 42.134926,-88.452102 42.134856,-88.452036 42.134814,-88.451952 42.134783,-88.451842 42.134768,-88.450207 42.134731,-88.450045 42.134734,-88.449883 42.134753,-88.449809 42.134767,-88.449989 42.135232,-88.450033 42.13532,-88.450071 42.135375,-88.450125 42.135435,-88.450188 42.135489,-88.450274 42.135547,-88.450379 42.1356,-88.450498 42.135641,-88.450645 42.13567,-88.451787 42.135817,-88.45195 42.135827,-88.452057 42.135822,-88.452288 42.13579,-88.452273 42.135697)))$$), 4326 ),
        ST_GeometryFromText('POINT(-88.450000 42.135254)',4326)
    )
) AS t(geom, pt);
  • Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. – Midavalo Oct 20 '17 at 23:53
  • 1
    Possibly point is on the line between two vertices. At least that's the impression I'm getting from Esri documentation (intersects means not disjoint; touches means at a vertex). – mkennedy Oct 21 '17 at 1:10
  • Is it possible that the point falls within the bounding box of a multipolygon NS ST_Intersects is taking the bounding geometry of the multipolygon while the others are only taking what lies on the exact coordinates of the feature? Can you post a screen capture of the point/poly in QGIS? – DPSSpatial Oct 21 '17 at 1:22
  • I'm having a little trouble with Imgur from my laptop but I've added the geometry for both above. Both are SRID 4326 – indiri Oct 21 '17 at 3:32
  • Whoof, here I was reviewing the Dimensionally Extended 9-Intersection Matrix ( DE-9IM ) and wondering if I was missing something. Returns 0F2FF1FF2 here – Evan Carroll Oct 21 '17 at 21:09
6

ST_Within(A,B) tells you whether "A is within B". Is the polygon within the point? Nope. ST_Contains(A, B) tells you whether "A contains B" and it is.

SELECT ST_Within(geom,pt), ST_Contains(geom,pt), ST_Touches(geom,pt), ST_Intersects(geom,pt)
FROM ( VALUES
    (
    ST_SetSRID( ST_GeometryFromText($$MULTIPOLYGON(((-88.466933 42.139013,-88.464586 42.139034,-88.463821 42.13901,-88.463811 42.138813,-88.463815 42.138705,-88.46382 42.138572,-88.463849 42.138468,-88.463886 42.138398,-88.464044 42.138452,-88.464239 42.138464,-88.464616 42.138472,-88.464961 42.138468,-88.465318 42.13846,-88.465558 42.138464,-88.465695 42.138443,-88.465824 42.138402,-88.465969 42.138319,-88.466048 42.138248,-88.466106 42.138161,-88.466135 42.138057,-88.466148 42.1379,-88.466149 42.137731,-88.466143 42.137406,-88.466143 42.137086,-88.466131 42.136945,-88.466098 42.13685,-88.466027 42.136746,-88.46596 42.136677,-88.465799 42.136609,-88.465571 42.136559,-88.465359 42.136534,-88.465167 42.136547,-88.464221 42.136559,-88.464216 42.136248,-88.464199 42.13609,-88.464179 42.135999,-88.46412 42.13587,-88.464046 42.135779,-88.463847 42.135597,-88.463531 42.135373,-88.463341 42.135252,-88.46313 42.135068,-88.462915 42.134957,-88.462601 42.134865,-88.462337 42.134822,-88.461619 42.134822,-88.460882 42.134828,-88.46044 42.134834,-88.460047 42.13484,-88.460053 42.135105,-88.460047 42.135381,-88.460034 42.135461,-88.459985 42.135559,-88.459905 42.135651,-88.459825 42.135756,-88.459752 42.135848,-88.459703 42.135934,-88.459684 42.136001,-88.45966 42.136118,-88.459654 42.136259,-88.459647 42.136836,-88.459107 42.136824,-88.458855 42.136818,-88.45864 42.136855,-88.458407 42.136978,-88.458315 42.137082,-88.458272 42.13718,-88.458247 42.137291,-88.458235 42.137408,-88.458241 42.137537,-88.458247 42.137647,-88.458253 42.138421,-88.458235 42.139017,-88.457615 42.139023,-88.457197 42.139029,-88.456607 42.139066,-88.455926 42.139146,-88.455133 42.139256,-88.45474 42.139313,-88.454709 42.139196,-88.454716 42.13907,-88.454683 42.138859,-88.454646 42.138353,-88.454669 42.138226,-88.454628 42.137907,-88.454616 42.137669,-88.454626 42.137153,-88.454611 42.137072,-88.454576 42.136978,-88.454512 42.136892,-88.454395 42.136793,-88.454355 42.136747,-88.454314 42.13659,-88.454306 42.136383,-88.454232 42.136322,-88.454175 42.136293,-88.454087 42.136261,-88.453929 42.136244,-88.453854 42.136242,-88.45377 42.136248,-88.453595 42.136284,-88.453339 42.136363,-88.453245 42.136397,-88.453161 42.136445,-88.453091 42.136465,-88.452984 42.136463,-88.452758 42.136445,-88.452403 42.136441,-88.452284 42.13642,-88.452172 42.136401,-88.451882 42.136359,-88.451677 42.136347,-88.45101 42.136297,-88.450681 42.136267,-88.450621 42.136253,-88.450506 42.136233,-88.450417 42.136208,-88.450304 42.136162,-88.450194 42.13611,-88.45004 42.13605,-88.449814 42.135954,-88.449742 42.135934,-88.44962 42.135916,-88.449449 42.135913,-88.448948 42.135919,-88.448035 42.135911,-88.447642 42.135913,-88.447494 42.135908,-88.4472 42.135908,-88.447133 42.135897,-88.446934 42.13583,-88.446806 42.135794,-88.446619 42.135764,-88.44654 42.135733,-88.446374 42.135693,-88.446295 42.135691,-88.446062 42.135732,-88.445985 42.135752,-88.445877 42.135766,-88.445808 42.135768,-88.445736 42.135761,-88.445664 42.135738,-88.445495 42.135652,-88.445404 42.135599,-88.44532 42.135556,-88.445258 42.135506,-88.445209 42.135449,-88.445154 42.135363,-88.44512 42.135297,-88.445108 42.135274,-88.445041 42.135094,-88.444995 42.135001,-88.444959 42.134915,-88.444908 42.134837,-88.444846 42.134763,-88.444795 42.134717,-88.444739 42.134677,-88.444663 42.134636,-88.444579 42.134617,-88.444485 42.134605,-88.444233 42.134608,-88.444145 42.134617,-88.444044 42.134637,-88.443934 42.134652,-88.44379 42.134686,-88.443725 42.134696,-88.443549 42.134682,-88.44342 42.134663,-88.44335 42.134643,-88.443252 42.134599,-88.443115 42.134602,-88.442976 42.13462,-88.442873 42.134642,-88.442774 42.134674,-88.442618 42.134767,-88.442551 42.134783,-88.442501 42.134819,-88.442441 42.134853,-88.442345 42.134882,-88.44228 42.13488,-88.44221 42.134857,-88.442107 42.134807,-88.442023 42.134755,-88.441961 42.134695,-88.441917 42.134632,-88.44191 42.134553,-88.441919 42.134482,-88.441996 42.134302,-88.442027 42.134205,-88.442039 42.134084,-88.442036 42.134019,-88.442012 42.133923,-88.441966 42.133862,-88.44189 42.13383,-88.441743 42.133808,-88.441565 42.133793,-88.441433 42.133798,-88.441253 42.133811,-88.440949 42.13385,-88.440761 42.133852,-88.440644 42.133824,-88.440548 42.133781,-88.440298 42.133605,-88.440192 42.13351,-88.440153 42.133451,-88.440125 42.133391,-88.439999 42.13319,-88.439932 42.133128,-88.43987 42.133078,-88.439805 42.133033,-88.439663 42.132971,-88.439317 42.132844,-88.439019 42.132728,-88.438784 42.132647,-88.438695 42.132611,-88.438599 42.132588,-88.438405 42.132574,-88.438345 42.132561,-88.438273 42.132574,-88.438234 42.132576,-88.438001 42.132417,-88.437931 42.132361,-88.43785 42.132306,-88.437624 42.132201,-88.437463 42.132137,-88.437208 42.132053,-88.437129 42.132019,-88.436992 42.132,-88.436788 42.131979,-88.436678 42.131963,-88.436565 42.131939,-88.436488 42.131918,-88.436205 42.131804,-88.436123 42.131761,-88.436032 42.131731,-88.435981 42.131695,-88.435953 42.131665,-88.435936 42.131624,-88.435888 42.131579,-88.435626 42.131274,-88.435578 42.131238,-88.435522 42.131165,-88.435409 42.131043,-88.435294 42.130892,-88.435229 42.13084,-88.43502 42.130604,-88.434892 42.130467,-88.43439 42.129908,-88.434291 42.129803,-88.434111 42.129599,-88.434051 42.129544,-88.434007 42.129515,-88.4337 42.129405,-88.433628 42.129373,-88.433554 42.129314,-88.433501 42.129257,-88.433481 42.129203,-88.433475 42.129,-88.434945 42.128994,-88.435401 42.128955,-88.436779 42.128706,-88.437438 42.128631,-88.437986 42.128635,-88.438514 42.128698,-88.439106 42.128842,-88.441903 42.129725,-88.442869 42.130081,-88.444453 42.130597,-88.447242 42.131469,-88.447807 42.131653,-88.448286 42.131788,-88.448679 42.131886,-88.449035 42.131948,-88.449502 42.131977,-88.451438 42.132031,-88.452216 42.132022,-88.453027 42.132095,-88.453641 42.132193,-88.454022 42.132267,-88.455004 42.132451,-88.455582 42.132587,-88.456073 42.132771,-88.456687 42.133029,-88.457178 42.133274,-88.457433 42.133409,-88.458097 42.133717,-88.458735 42.134017,-88.458987 42.134122,-88.459504 42.134245,-88.459832 42.134282,-88.460495 42.134263,-88.461281 42.134263,-88.461905 42.134249,-88.462648 42.134259,-88.462991 42.134306,-88.463362 42.134392,-88.463604 42.134506,-88.463705 42.134554,-88.463991 42.134706,-88.464239 42.134897,-88.464391 42.135068,-88.46467 42.135368,-88.464905 42.135602,-88.465077 42.135754,-88.465286 42.135916,-88.465429 42.135983,-88.465667 42.136068,-88.465924 42.136106,-88.466362 42.136116,-88.466905 42.136087,-88.466935 42.137029,-88.466933 42.139013),(-88.458229 42.134374,-88.458161 42.134318,-88.457492 42.133962,-88.456546 42.133502,-88.456091 42.133293,-88.455809 42.133188,-88.455551 42.133133,-88.455471 42.133102,-88.455385 42.133158,-88.45533 42.133244,-88.455299 42.13336,-88.455275 42.133471,-88.455282 42.133657,-88.455274 42.133965,-88.455279 42.133988,-88.455294 42.134017,-88.455319 42.134042,-88.45533 42.13405,-88.455351 42.134062,-88.455388 42.134075,-88.455429 42.134081,-88.45546 42.13408,-88.455658 42.134071,-88.455717 42.13407,-88.455796 42.134076,-88.455874 42.134088,-88.455948 42.134108,-88.45602 42.134134,-88.456046 42.134146,-88.456482 42.134355,-88.457315 42.134739,-88.457836 42.135006,-88.457946 42.134877,-88.458112 42.134687,-88.458204 42.134552,-88.458247 42.134447,-88.458229 42.134374),(-88.452273 42.135697,-88.452285 42.135431,-88.452274 42.135336,-88.452194 42.134994,-88.452165 42.134926,-88.452102 42.134856,-88.452036 42.134814,-88.451952 42.134783,-88.451842 42.134768,-88.450207 42.134731,-88.450045 42.134734,-88.449883 42.134753,-88.449809 42.134767,-88.449989 42.135232,-88.450033 42.13532,-88.450071 42.135375,-88.450125 42.135435,-88.450188 42.135489,-88.450274 42.135547,-88.450379 42.1356,-88.450498 42.135641,-88.450645 42.13567,-88.451787 42.135817,-88.45195 42.135827,-88.452057 42.135822,-88.452288 42.13579,-88.452273 42.135697)))$$), 4326 ),
    ST_GeometryFromText('POINT(-88.450000 42.135254)',4326)
    )
) AS t(geom, pt);

Here's the results as seen on my computer:

 st_within | st_contains | st_touches | st_intersects 
-----------+-------------+------------+---------------
 f         | t           | f          | t
  • I was doing ST_Within(point, geom) (is point within geom) and it returned false. The edit above was entered by someone incorrectly. And my computer does return it false for touch as well. I checked that the polygon is valid. If you are getting true for ST_Touches thoughts on why I don't? All blocks were imported at once (so seems unlikely only one imported corrupt?) and the others return correctly even when the point only touches. – indiri Oct 22 '17 at 15:51
  • 2
    Well, then I'll go back to my previous comment to the effect that you've unearthed a very very small difference between implementations in GEOS (within/contains) and PostGIS (intersects). The fact that I'm seeing correct results is probably a result of the transition from binary into text form and back, as the difference is so very small that just that transform could effect it. – Paul Ramsey Oct 22 '17 at 18:35
  • @PaulRamsey I think your original idea ties in with similar odd behaviour I saw in this question. In that case it was QGIS geometry functions, which use GEOS under the hood. – Steven Kay Oct 22 '17 at 20:45
  • So I think your comment is really the answer here but I'm going to mark this complete. Thanks for the interesting insight! – indiri Oct 24 '17 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.