locked
STWithin not returning expected results on geometry RRS feed

  • Question

  • Hello,

    I am trying to do a STWithin() w/ the new 2010 census shapes and I have found a case where I know that there are blocks contained w/ in other blocks.  But the function below is not returning what I would expect.  Can anyone perhaps recommend why this might be occuring?

    DECLARE

    @geom

    geometry

    SELECT

    @geom = mapGeom FROM tl10All10Geo WHERE uogid=346044

    SELECT

    * FROM tl10All10Geo

    WHERE

    (mapGeom.STWithin(@geom))=1


    derek
    Friday, March 25, 2011 2:57 AM

All replies

  • Results are "not what what you would expect".... too few? too many? none at all?

    It'd be helpful if you could give the WKT of the mapGeom instance corresponding to uogid 346044, and the WKT of one of the geometries that you expect should be returned but that isn't (or one that is being returned that you think shouldn't be)


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Friday, March 25, 2011 8:40 AM
    Answerer
  • Yes of course that would help.  The 
    WKT for  346044 and 346045 are below. As you should see 346044 is within 346045. But this type of TSQL doesn't seem to catch that on my end

    declare @geom geometry

    select @geom = mapGeom from tl10block10Regions where regionid=346044

    select * from tl10block10Regions

    where mapGeom.STWithin(@geom)=1

    WKT for 346044 is POLYGON ((-80.11956900000007 26.914039000000145, -80.119353999999888 26.914033999999948, -80.119140000000073 26.914043000000085, -80.118926999999914 26.914066999999978, -80.118716999999975 26.914103999999913, -80.118510000000029 26.914154999999898, -80.118307999999942 26.914219999999869, -80.1181109999999 26.914297000000037, -80.117876000000138 26.914412000000048, -80.1176959999999 26.914516999999918, -80.1162279999999 26.915523000000118, -80.115891000000147 26.9157550000001, -80.115735000000015 26.91587300000015, -80.115593000000032 26.916004000000008, -80.115466 26.916147000000088, -80.115216000000075 26.916476999999876, -80.115054000000114 26.916689000000126, -80.11493300000015 26.916823000000143, -80.114800999999972 26.916948999999928, -80.114657999999878 26.9170639999999, -80.114542999999912 26.917143999999908, -80.113240999999874 26.917848999999933, -80.112920999999957 26.91802699999986, -80.112763999999956 26.918133000000015, -80.112617000000057 26.918250000000107, -80.112479999999934 26.91837700000012, -80.112382000000025 26.918482000000072, -80.112312 26.918565999999945, -80.112204999999989 26.918713999999913, -80.112133999999884 26.918830000000067, -80.1120500000001 26.918988999999886, -80.111981999999884 26.919153999999867, -80.111928000000148 26.919323999999992, -80.111889999999889 26.919495999999882, -80.111865999999978 26.9196709999999, -80.111858999999953 26.919846999999855, -80.11187599999991 26.920144999999962, -80.111924999999957 26.920851000000003, -80.11197300000002 26.921561999999884, -80.112017999999892 26.922220999999869, -80.11201899999989 26.922304000000068, -80.112020999999928 26.922489000000127, -80.1119990000001 26.923044999999874, -80.1119789999999 26.923560000000045, -80.112133999999912 26.923557999999897, -80.114211000000054 26.923623000000138, -80.114418000000029 26.923643999999971, -80.1145170000001 26.923659000000072, -80.114672999999982 26.9236900000001, -80.114728000000113 26.923708000000044, -80.114878000000019 26.92374800000006, -80.115072999999867 26.923813999999908, -80.115262000000058 26.923893000000085, -80.115443000000141 26.923984000000019, -80.115617000000057 26.924088000000111, -80.115781000000112 26.924201999999948, -80.11593599999992 26.924328000000088, -80.117614999999944 26.925885000000111, -80.117710000000088 26.925960999999926, -80.117861999999946 26.926064999999884, -80.118026000000057 26.926152999999893, -80.118239999999972 26.926245999999949, -80.118523999999951 26.926315000000045, -80.118650000000159 26.926333999999976, -80.118829000000019 26.926347000000046, -80.123463999999942 26.926496000000007, -80.12346400000007 26.92554599999994, -80.123479999999944 26.925377999999949, -80.123511999999863 26.925212000000091, -80.1235589999999 26.925048999999902, -80.123621000000057 26.924890000000026, -80.123697999999877 26.924737000000103, -80.123789000000144 26.924588999999983, -80.123892999999867 26.924448999999886, -80.12400999999997 26.924316999999984, -80.124106000000083 26.924224000000144, -80.126024000000029 26.922469999999972, -80.126144000000068 26.92233900000004, -80.126215999999928 26.92224600000003, -80.12626900000005 26.922163999999867, -80.12634200000015 26.92211600000007, -80.1263450000001 26.92202700000006, -80.126369000000025 26.921976999999881, -80.126430000000113 26.92181600000001, -80.126472000000092 26.921650000000025, -80.126493999999852 26.921481000000071, -80.126604000000142 26.918701999999968, -80.12659599999995 26.9185880000001, -80.126568000000162 26.918419000000053, -80.126520000000085 26.918253000000057, -80.126453999999868 26.918091999999994, -80.126368999999855 26.917937999999939, -80.126266999999856 26.917792999999953, -80.12615300000013 26.917663000000125, -80.126105999999936 26.917615999999938, -80.125968 26.91749699999988, -80.124772000000078 26.916672999999975, -80.124723000000046 26.916635999999887, -80.124584000000013 26.91651799999994, -80.124499999999941 26.916432000000142, -80.124385999999944 26.916294000000004, -80.124289 26.916146999999921, -80.124233999999944 26.916043000000027, -80.124166999999886 26.9158830000001, -80.124119000000036 26.915717000000118, -80.124090999999936 26.915560999999936, -80.123587000000072 26.915608999999908, -80.12348 26.915611999999861, -80.123373000000115 26.915606999999891, -80.123265999999944 26.915595000000124, -80.123110000000153 26.915560999999897, -80.122958999999938 26.915509999999916, -80.122816999999941 26.915443000000142, -80.122684999999862 26.915361000000114, -80.1226030000001 26.91529800000011, -80.122423000000126 26.915137000000072, -80.12227999999989 26.915027999999911, -80.122126999999892 26.914928999999852, -80.121965000000159 26.914843000000118, -80.121839000000051 26.914785999999857, -80.121665000000135 26.914722000000037, -80.121400000000079 26.914635, -80.121189000000143 26.914552000000146, -80.120986999999943 26.914455000000103, -80.120748999999861 26.914332999999889, -80.120554999999982 26.914251000000061, -80.12035500000016 26.914180999999932, -80.120149999999953 26.914123999999873, -80.120006999999916 26.914092999999912, -80.119729000000021 26.91405200000013, -80.11956900000007 26.914039000000145), (-80.117609000000073 26.922229000000048, -80.117653999999831 26.922242000000132, -80.117693999999915 26.92226400000003, -80.117726000000076 26.922295000000055, -80.117748999999876 26.922330999999971, -80.11775999999989 26.922371999999996, -80.117760000000146 26.922414000000064, -80.117747999999935 26.922492000000055, -80.117743000000146 26.922579999999925, -80.11775200000011 26.92270999999991, -80.117779999999883 26.922838000000016, -80.117810000000176 26.922921999999897, -80.117871000000022 26.923040999999891, -80.117947999999984 26.923152000000069, -80.118041999999889 26.923251999999849, -80.118149999999929 26.923340000000042, -80.118270000000038 26.923413999999998, -80.118414999999857 26.923478999999944, -80.118537000000131 26.923522000000084, -80.11940999999986 26.923777000000136, -80.119426000000146 26.923836000000104, -80.119390999999965 26.923905999999917, -80.119365999999957 26.92395500000012, -80.119302000000033 26.924006000000084, -80.118507000000008 26.923775000000081, -80.118285000000029 26.923703000000046, -80.118111999999925 26.923637999999865, -80.117887000000053 26.923541000000032, -80.117093000000068 26.923134000000086, -80.116943999999947 26.923052999999989, -80.116914000000051 26.923018999999904, -80.116896999999966 26.922977999999965, -80.116896000000011 26.92293400000003, -80.116909999999891 26.922892000000068, -80.116938000000147 26.922855999999932, -80.1169770000001 26.922830000000097, -80.117023000000145 26.922815999999955, -80.117155999999923 26.922796999999964, -80.1175520000001 26.922740999999924, -80.117484999999945 26.922552999999869, -80.11741200000013 26.922254000000077, -80.117561999999921 26.922227000000067, -80.117609000000073 26.922229000000048), (-80.1196810000001 26.91435100000005, -80.119875999999863 26.914373000000037, -80.120068000000032 26.914408999999875, -80.120256000000055 26.914459000000122, -80.120440000000144 26.914521999999987, -80.120616999999982 26.914597999999884, -80.120954000000054 26.914768999999918, -80.121165999999974 26.91485999999994, -80.121394000000024 26.914943000000154, -80.121608000000137 26.915011000000128, -80.1217860000001 26.915083999999954, -80.121899999999826 26.915142000000056, -80.122062 26.915240999999938, -80.122163000000114 26.915314999999985, -80.122304000000014 26.915436999999955, -80.122487999999962 26.915603999999934, -80.122682999999853 26.915760000000006, -80.12288800000006 26.915905999999953, -80.123054000000025 26.916011000000029, -80.123276000000075 26.91613599999987, -80.123505000000037 26.916248999999894, -80.12372599999992 26.916348999999975, -80.123833999999945 26.916407999999866, -80.123937999999967 26.916473000000138, -80.124153000000177 26.916620999999864, -80.124265999999878 26.916717999999996, -80.124364 26.916825999999951, -80.124549999999942 26.917094999999946, -80.124662999999856 26.917231999999867, -80.124789000000121 26.91735999999991, -80.124925999999974 26.917477999999949, -80.125019999999878 26.917549000000058, -80.12519 26.917659000000128, -80.125352999999862 26.917745999999912, -80.125622999999862 26.917865000000067, -80.125707999999847 26.917912000000118, -80.125827000000172 26.9179939999999, -80.125932000000077 26.918089999999953, -80.125995000000145 26.91816000000016, -80.126073999999846 26.918274999999955, -80.126117999999934 26.918354999999885, -80.126167000000066 26.918482000000107, -80.126190000000122 26.918569999999953, -80.126205000000041 26.918673999999864, -80.126209000000074 26.918762999999984, -80.126179999999877 26.919419000000019, -80.126155999999924 26.919587000000114, -80.126116999999923 26.919753000000114, -80.126023000000117 26.920044000000082, -80.125996999999927 26.92015899999987, -80.125975000000153 26.920333000000063, -80.125973999999957 26.920530000000102, -80.12599200000011 26.920697999999884, -80.12602500000007 26.920863999999877, -80.126087999999839 26.921093000000031, -80.126106999999891 26.921197000000024, -80.126117000000022 26.92135399999988, -80.126112999999961 26.92145900000002, -80.126101000000048 26.921562999999939, -80.126064999999926 26.921717000000132, -80.126010999999977 26.921866999999903, -80.125938000000033 26.922009999999904, -80.125847000000078 26.92214400000001, -80.12575200000002 26.922255999999884, -80.125673999999933 26.922332999999881, -80.125461999999871 26.922523999999942, -80.1253830000001 26.922576000000003, -80.125294999999966 26.922616000000044, -80.125202000000073 26.922642000000049, -80.12515300000014 26.922650999999934, -80.12488600000006 26.922681999999927, -80.12412500000012 26.922589000000059, -80.124144999999857 26.922706000000129, -80.124162999999925 26.92302999999993, -80.123922999999991 26.923761999999954, -80.123403999999965 26.9242150000001, -80.122864999999877 26.924664999999987, -80.122326999999885 26.92466300000012, -80.122347999999874 26.924397000000059, -80.122188000000008 26.924408000000032, -80.122430999999963 26.923175999999991, -80.122461000000015 26.922103999999926, -80.122272999999979 26.921019999999952, -80.12222700000008 26.920448000000004, -80.122187000000054 26.919953000000067, -80.12216200000006 26.919637000000041, -80.122003000000092 26.919392000000048, -80.120663999999863 26.918357999999973, -80.120475999999968 26.918212000000064, -80.12031500000009 26.918095000000068, -80.120288999999957 26.918813000000132, -80.120278000000155 26.919237000000006, -80.120282000000032 26.919287000000075, -80.120312999999982 26.919470999999962, -80.120202999999961 26.919490000000128, -80.120071999999936 26.919518999999877, -80.119943000000134 26.919557000000104, -80.11981700000004 26.919601999999905, -80.119636000000142 26.919682000000133, -80.119519999999881 26.919745000000113, -80.1192629999999 26.919901000000028, -80.118776999999938 26.920200999999921, -80.1186010000001 26.920326000000014, -80.118435000000019 26.92046099999995, -80.118281000000081 26.920606999999961, -80.118020999999956 26.920883999999958, -80.117986999999857 26.92091600000003, -80.117911000000149 26.92097199999991, -80.117827000000119 26.92101599999987, -80.117781 26.921032999999944, -80.117687000000075 26.921057999999885, -80.117590000000163 26.921068999999878, -80.1171530000001 26.921055999999993, -80.117167000000137 26.921169000000006, -80.117308999999878 26.921800999999952, -80.115765999999979 26.922083999999892, -80.115570999999875 26.922121000000114, -80.1154210000001 26.922166000000121, -80.115325000000084 26.922205000000055, -80.115189 26.922276000000135, -80.115062999999992 26.922363000000043, -80.11495099999992 26.922461999999879, -80.114884000000174 26.922535999999884, -80.1148539999999 26.922574000000118, -80.114397999999881 26.922393000000088, -80.1140280000001 26.922284000000062, -80.113864000000149 26.922240999999872, -80.113703999999871 26.922190000000136, -80.11354300000005 26.922128000000072, -80.113355 26.922026000000066, -80.113298999999955 26.921988999999961, -80.113218000000117 26.921805000000141, -80.113224999999886 26.921725000000094, -80.113310000000155 26.921315999999962, -80.113349000000085 26.921149, -80.1133430000001 26.920985999999989, -80.113288999999824 26.92082300000013, -80.1131859999999 26.920628000000047, -80.112956999999966 26.920464000000035, -80.1126230000001 26.92030599999989, -80.112452999999945 26.92022800000008, -80.112265999999877 26.920133000000067, -80.112174999999979 26.92006399999995, -80.112119999999862 26.919963000000024, -80.112100999999925 26.919781999999927, -80.112151999999938 26.919500999999912, -80.112244999999859 26.919201000000012, -80.1123440000001 26.918975999999873, -80.112407999999846 26.918877000000126, -80.112453999999957 26.918845999999924, -80.112515000000087 26.918723999999912, -80.11258199999989 26.918621999999871, -80.112644000000046 26.918566999999868, -80.112691999999868 26.918499000000129, -80.112763999999871 26.918448999999939, -80.112854999999925 26.918377000000064, -80.112959999999944 26.918279000000069, -80.1130409999999 26.918236999999976, -80.113098999999863 26.918194999999919, -80.113241999999872 26.918114999999947, -80.113351000000023 26.918064999999917, -80.113661000000121 26.91789800000014, -80.114018000000016 26.917707000000082, -80.114124000000018 26.917643999999942, -80.114491000000072 26.917371000000024, -80.114574999999874 26.917322000000091, -80.114774999999909 26.917188000000106, -80.114942 26.917052, -80.115077999999883 26.916921000000109, -80.11520299999998 26.916780999999929, -80.115605000000087 26.916257000000126, -80.11572499999987 26.916118999999959, -80.11586000000004 26.915993999999987, -80.116008999999977 26.915881000000084, -80.116115000000121 26.915813999999994, -80.116283000000124 26.915725999999964, -80.116459999999876 26.915653000000095, -80.116582999999849 26.915614000000108, -80.117107999999973 26.915480000000137, -80.117277000000016 26.915425999999989, -80.117402 26.915372999999885, -80.117556000000121 26.915289999999874, -80.117697999999947 26.915193000000023, -80.117827000000162 26.915081999999895, -80.117905000000079 26.915001000000053, -80.118042999999943 26.914832000000104, -80.118144 26.914732, -80.118258000000068 26.914644999999933, -80.118384999999947 26.914572, -80.118527999999955 26.914510999999891, -80.118711999999917 26.914450000000006, -80.118901000000164 26.914402999999933, -80.119094000000146 26.9143689999999, -80.1192889999999 26.91434900000014, -80.119484999999983 26.914342999999974, -80.1196810000001 26.91435100000005))
    WKT for 346045 is POLYGON ((-80.117609 26.922229, -80.117562 26.922227, -80.117412 26.922254, -80.117485 26.922553, -80.117552 26.922741, -80.117635 26.922917, -80.117725 26.92307, -80.117836 26.923224, -80.117935 26.923331, -80.118048 26.923426, -80.118131 26.923481, -80.118219 26.92353, -80.118378 26.9236, -80.118505 26.923643, -80.119391 26.923906, -80.119426 26.923836, -80.11941 26.923777, -80.118537 26.923522, -80.118415 26.923479, -80.11827 26.923414, -80.11815 26.92334, -80.118042 26.923252, -80.117948 26.923152, -80.117871 26.923041, -80.11781 26.922922, -80.11778 26.922838, -80.117752 26.92271, -80.117743 26.92258, -80.117748 26.922492, -80.11776 26.922414, -80.11776 26.922372, -80.117749 26.922331, -80.117726 26.922295, -80.117694 26.922264, -80.117654 26.922242, -80.117609 26.922229))

    derek
    Friday, March 25, 2011 12:34 PM
  • I think this situation can be explained from a misunderstanding of what "within" means.

    According to the OGC definition of "within" (as measured by STWithin()), geometry A is within geometry B if the interior of geometry A intersects the interior of geometry B, and neither the interior nor the boundary of geometry A intersects the exterior of geometry B.

    Using the example geometries you provided above, ypu're expecting Polygon 346045 to be "within" Polygon 346044 because, geographically-speaking, it is completely surrounded by it. However, the area in which Polygon 346045 lies is actually contained within an interior ring of the larger geometry. Interior rings define areas of space that are "cut out" of a polygon - "holes", if you like.

    In fact, the only points that the two polygons have in common are the points that lie on their shared boundary - no points from the interior of Polygon 346045 lie in the interior Polygon 346044, so it can't be within it.

    Does that make sense?


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Friday, March 25, 2011 3:30 PM
    Answerer
  • Hello,

    Yes this does make sense.  That for taking the time to explain it.  Just as a concluding not on this topic and in-line w/ your explanation I was able to perform what I needed to do by turning the exterior ring of the polygon in question into a new polygon.  Here is an example of that

    declare @StartGeom geometry
    declare @geomWKT varchar(max)
    declare @ExteriorRingGeom geometry

    select @StartGeom = mapGeom.STExteriorRing() from tl10block10Regions where regionid=346044
    set @geomWKT = REPLACE(@StartGeom.STAsText(),'LINESTRING','POLYGON(')+')'
    print @geomWKT
    select @ExteriorRingGeom = geometry::STGeomFromText(@geomWKT,4326)
     
    select * from tl10block10Regions
    where mapGeom.STWithin(@ExteriorRingGeom)=1


    derek
    Friday, March 25, 2011 4:39 PM