开发者

MySQL geometry intersection produces non-intersecting results

开发者 https://www.devze.com 2023-02-02 11:55 出处:网络
I have a MySQL table of postcodes with the geometric area of each saved as a polygon. I also have a table of regional areas that are also polygons. I\'m doing a simple query to find the intersecting a

I have a MySQL table of postcodes with the geometric area of each saved as a polygon. I also have a table of regional areas that are also polygons. I'm doing a simple query to find the intersecting area for a particular postcode (ie to find which area it belongs to) but I'm getting areas returned that do not intersect at all.

Here is my query:

SELECT a.*, pc.StateID AS PostcodeStateID
FROM area a
 JOIN postcode pc ON INTERSECTS(pc.Geometry, a.Geometry)
WHERE pc.Postcode = '4065'

Why is MySQL returning results that don't intersect? Is there an error in my query?

Here is the polygon for the postcode 4065:

POLYGON((152.9871 -27.448586,152.98726 -27.448668,152.987373 -27.448625,152.987458 -27.448574,152.987604 -27.448519,152.987771 -27.448446,152.988061 -27.448355,152.988173 -27.448282,152.98834 -27.44814,152.988498 -27.448048,152.988658 -27.447966,152.988843 -27.447963,152.988904 -27.448012,152.988949 -27.448143,152.988935 -27.448316,152.988908 -27.448474,152.988972 -27.448616,152.989126 -27.448805,152.989213 -27.448971,152.989352 -27.449098,152.9896 -27.449201,152.989764 -27.449225,152.989941 -27.449326,152.990131 -27.449307,152.990288 -27.44932,152.990427 -27.449238,152.990628 -27.449156,152.990844 -27.449044,152.990994 -27.448949,152.991106 -27.448904,152.991606 -27.44891,152.991756 -27.448928,152.991745 -27.44899,152.991725 -27.449105,152.991708 -27.449198,152.992 -27.449173,152.992137 -27.449201,152.992113 -27.449339,152.992071 -27.449333,152.992063 -27.449471,152.992054 -27.449609,152.992043 -27.449793,152.992062 -27.449796,152.991999 -27.44992,152.991886 -27.450141,152.99159 -27.450097,152.991513 -27.45027,152.991442 -27.450631,152.991544 -27.450646,152.991647 -27.450662,152.99175 -27.450677,152.991852 -27.450692,152.991835 -27.45078,152.991817 -27.450868,152.9918 -27.450957,152.991783 -27.451044,152.992193 -27.451105,152.99233 -27.451125,152.992643 -27.451172,152.992628 -27.451251,152.992607 -27.451358,152.992586 -27.451466,152.992565 -27.451574,152.992544 -27.451682,152.992523 -27.45179,152.992502 -27.451897,152.99243 -27.452263,152.992277 -27.45305,152.992126 -27.453026,152.991978 -27.453003,152.991829 -27.45298,152.991477 -27.452925,152.991451 -27.453049,152.9913 -27.453025,152.990949 -27.452969,152.990846 -27.453403,152.990587 -27.45364,152.990559 -27.45366,152.990146 -27.453595,152.990163 -27.453508,152.99018 -27.453418,152.990197 -27.453329,152.989741 -27.453259,152.989723 -27.453349,152.989713 -27.453404,152.989707 -27.453439,152.98969 -27.453525,152.989672 -27.453618,152.989595 -27.454024,152.989394 -27.454012,152.989425 -27.453844,152.989454 -27.45369,152.989346 -27.453709,152.98929 -27.45372,152.989239 -27.453729,152.989183 -27.453739,152.989082 -27.453758,152.988978 -27.453777,152.988953 -27.453782,152.98893 -27.453789,152.988785 -27.453835,152.98869 -27.453866,152.988576 -27.453902,152.988698 -27.454195,152.988631 -27.454392,152.988563 -27.454478,152.98876 -27.454651,152.988669 -27.454734,152.988696 -27.454758,152.98856 -27.454891,152.988369 -27.454722,152.988244 -27.454868,152.98862 -27.455196,152.988549 -27.455261,152.988496 -27.455309,152.988478 -27.455326,152.988442 -27.455358,152.988407 -27.455391,152.988336 -27.455455,152.988264 -27.45552,152.988193 -27.455585,152.988122 -27.45565,152.988051 -27.455715,152.98798 -27.45578,152.987909 -27.455845,152.987838 -27.45591,152.987766 -27.455975,152.987739 -27.456,152.987696 -27.45604,152.987644 -27.456087,152.987624 -27.456105,152.987553 -27.45617,152.98741 -27.456301,152.987335 -27.456368,152.987269 -27.45643,152.987198 -27.456495,152.987174 -27.456517,152.987126 -27.45656,152.987084 -27.456599,152.987055 -27.456625,152.986984 -27.456689,152.98689 -27.456777,152.9867 -27.456611,152.986582 -27.45672,152.986697 -27.45682,152.986634 -27.456878,152.986125 -27.456854,152.98592 -27.457003,152.985847 -27.457181,152.98581 -27.45727,152.985774 -27.457359,152.985747 -27.457424,152.985738 -27.457448,152.985975 -27.457462,152.986073 -27.457468,152.986171 -27.457474,152.986268 -27.45748,152.986369 -27.457486,152.98647 -27.457492,152.986571 -27.457499,152.986621 -27.457502,152.986672 -27.457505,152.986773 -27.457511,152.986873 -27.457517,152.986974 -27.457523,152.987075 -27.457529,152.98707 -27.45762,152.987067 -27.457667,152.987059 -27.457801,152.987161 -27.457805,152.987207 -27.457806,152.987323 -27.45781,152.987431 -27.457813,152.987482 -27.457815,152.987639 -27.45782,152.987647 -27.45782,152.987616 -27.458112,152.98781 -27.458212,152.987837 -27.458237,152.98841 -27.457968,152.988425 -27.457992,152.988483 -27.458088,152.988425 -27.458115,152.988474 -27.458195,152.988522 -27.458275,152.988547 -27.458315,152.988571 -27.458355,152.988619 -27.458434,152.988574 -27.458456,152.988691 -27.458647,152.988305 -27.458681,152.988492 -27.458847,152.988493 -27.458866,152.988447 -27.458921,152.988623 -27.459293,152.988656 -27.459351,152.988529 -27.459836,152.988978 -27.459766,152.988936 -27.459984,152.988912 -27.460105,152.988526 -27.460023,152.988524 -27.460209,152.988947 -27.460299,152.988934 -27.460394,152.988732 -27.460353,152.988704 -27.460639,152.988567 -27.460537,152.988571 -27.460819,152.98845 -27.460861,152.988447 -27.460866,152.988434 -27.46092,152.988416 -27.461001,152.9884 -27.461083,152.988387 -27.461165,152.988376 -27.461247,152.988367 -27.461316,152.988358 -27.461398,152.988349 -27.461481,152.988339 -27.461588,152.988332 -27.461668,152.988327 -27.461745,152.988328 -27.46177,152.98833 -27.461822,152.988328 -27.461829,152.988317 -27.461902,152.988313 -27.461989,152.988309 -27.462119,152.988305 -27.462249,152.988301 -27.462379,152.988297 -27.462507,152.988295 -27.462596,152.988294 -27.462681,152.988297 -27.462767,152.988303 -27.462854,152.988312 -27.462939,152.988324 -27.46302,152.988339 -27.463105,152.988358 -27.46319,152.988378 -27.463273,152.988403 -27.463356,152.988431 -27.46344,152.988462 -27.463522,152.988495 -27.463601,152.988531 -27.463681,152.98857 -27.46376,152.988613 -27.463838,152.988704 -27.463991,152.988749 -27.464068,152.988786 -27.464146,152.988817 -27.464229,152.988838 -27.464314,152.988847 -27.464402,152.988847 -27.464488,152.988834 -27.464574,152.988812 -27.464661,152.988813 -27.464683,152.988826 -27.464701,152.988848 -27.464711,152.988899 -27.46472,152.988983 -27.464873,152.989011 -27.464908,152.989051 -27.464934,152.989097 -27.464947,152.988453 -27.465053,152.991379 -27.465487,152.991614 -27.465522,152.991626 -27.465529,152.991628 -27.465542,152.991618 -27.465551,152.991978 -27.465577,152.991917 -27.465936,152.991898 -27.466034,152.991851 -27.466294,152.991847 -27.466312,152.991831 -27.466403,152.991818 -27.466473,152.991812 -27.466505,152.991793 -27.466604,152.991772 -27.46672,152.991752 -27.466829,152.991923 -27.466854,152.991991 -27.466864,152.992025 -27.466869,152.992121 -27.466883,152.992104 -27.466972,152.992088 -27.467061,152.992264 -27.467086,152.992234 -27.467264,152.992462 -27.467297,152.992428 -27.467476,152.992223 -27.467447,152.992025 -27.467418,152.99199 -27.467596,152.991939 -27.467863,152.991841 -27.467848,152.991741 -27.467833,152.991581 -27.467808,152.991549 -27.467977,152.991514 -27.46816,152.991251 -27.468121,152.991163 -27.467999,152.991103 -27.46799,152.991033 -27.468367,152.990892 -27.468347,152.990814 -27.468336,152.990592 -27.468304,152.990571 -27.468301,152.990373 -27.468273,152.990321 -27.468265,152.990166 -27.468243,152.990023 -27.46801,152.989975 -27.468044,152.990089 -27.468231,152.989985 -27.468215,152.98988 -27.468199,152.989897 -27.468103,152.989758 -27.468202,152.989576 -27.46822,152.989475 -27.468227,152.989314 -27.468243,152.989308 -27.468532,152.989313 -27.468625,152.989272 -27.468655,152.989265 -27.468903,152.989432 -27.46893,152.989598 -27.468957,152.989593 -27.468983,152.989578 -27.469057,152.989558 -27.469158,152.989236 -27.469106,152.989228 -27.46914,152.9892 -27.469228,152.989202 -27.469251,152.989217 -27.469271,152.989241 -27.469281,152.989107 -27.469366,152.989197 -27.469381,152.989137 -27.469721,152.989197 -27.469756,152.989281 -27.469768,152.989192 -27.469878,152.989145 -27.469937,152.989137 -27.469975,152.989133 -27.469997,152.989109 -27.470125,152.989044 -27.470094,152.988914 -27.470031,152.988882 -27.470184,152.988671 -27.470149,152.988648 -27.470258,152.988515 -27.4703,152.988556 -27.470106,152.988368 -27.470073,152.98819 -27.470042,152.988175 -27.470106,152.987975 -27.470176,152.987775 -27.470246,152.987598 -27.470308,152.98755 -27.470305,152.987328 -27.470289,152.987171 -27.470278,152.987028 -27.470268,152.987004 -27.470416,152.986728 -27.470395,152.98654 -27.470382,152.986496 -27.470612,152.98625 -27.47082,152.986294 -27.470587,152.986296 -27.470582,152.985909 -27.470523,152.985808 -27.470508,152.985606 -27.470477,152.985405 -27.470447,152.985203 -27.470416,152.985002 -27.470386,152.98498 -27.470498,152.984838 -27.470438,152.984696 -27.470379,152.984602 -27.47034,152.984508 -27.4703,152.984479 -27.47046,152.984283 -27.470417,152.984264 -27.470527,152.984051 -27.47056,152.984113 -27.47022,152.984005 -27.470223,152.983902 -27.470225,152.983701 -27.470231,152.983502 -27.4702,152.983457 -27.470193,152.98315 -27.470445,152.983257 -27.470617,152.983149 -27.470821,152.982741 -27.470853,152.982738 -27.47094,152.982705 -27.471003,152.982652 -27.471105,152.982618 -27.47117,152.982566 -27.47127,152.982513 -27.471372,152.982481 -27.471435,152.982399 -27.471519,152.982321 -27.471546,152.982134 -27.471609,152.982124 -27.471613,152.981991 -27.471658,152.98189 -27.471692,152.981849 -27.471706,152.981797 -27.471724,152.981773 -27.471732,152.981728 -27.471721,152.981684 -27.471711,152.981639 -27.4717,152.981585 -27.471687,152.981485 -27.471663,152.981441 -27.471653,152.981385 -27.47164,152.981286 -27.471616,152.981244 -27.471606,152.981186 -27.471592,152.981087 -27.471569,152.981047 -27.471559,152.980956 -27.471542,152.980857 -27.471522,152.98083 -27.471517,152.980757 -27.471503,152.980607 -27.471474,152.980557 -27.471464,152.980457 -27.471445,152.980357 -27.471425,152.980352 -27.471354,152.980267 -27.471359,152.980258 -27.4714,152.979956 -27.471421,152.979972 -27.471585,152.979751 -27.471413,152.979709 -27.470972,152.979623 -27.47096,152.979522 -27.470947,152.979421 -27.470934,152.979319 -27.47092,152.979273 -27.470914,152.979218 -27.470907,152.979117 -27.470894,152.978914 -27.470867,152.978693 -27.470838,152.978698 -27.471206,152.978657 -27.471201,152.978596 -27.471222,152.978499 -27.471255,152.978409 -27.470901,152.978217 -27.470968,152.978028 -27.471034,152.977969 -27.471054,152.977915 -27.471074,152.977746 -27.471043,152.977597 -27.471015,152.977448 -27.470988,152.977427 -27.470984,152.977299 -27.470961,152.97715 -27.470934,152.977104 -27.470925,152.977017 -27.47087,152.97688 -27.470783,152.976744 -27.470697,152.976779 -27.470502,152.976477 -27.470461,152.976426 -27.470459,152.976257 -27.470455,152.976068 -27.470456,152.976054 -27.470456,152.975961 -27.470453,152.975896 -27.47045,152.975733 -27.470608,152.975687 -27.470571,152.975658 -27.470522,152.975649 -27.470467,152.975235 -27.470332,152.975448 -27.469976,152.974652 -27.469523,152.974546 -27.46937,152.974526 -27.469204,152.974545 -27.468993,152.974633 -27.468714,152.974637 -27.468646,152.974633 -27.468593,152.974629 -27.468584,152.974143 -27.468319,152.974009 -27.467988,152.974409 -27.466946,152.97471 -27.466812,152.974483 -27.466789,152.974544 -27.466476,152.974635 -27.4665,152.974708 -27.466492,152.974857 -27.466747,152.975117 -27.466632,152.975812 -27.466005,152.975062 -27.465782,152.975064 -27.465753,152.975208 -27.465625,152.975353 -27.465498,152.975497 -27.46537,152.975545 -27.465213,152.975593 -27.465056,152.974926 -27.464858,152.974724 -27.464801,152.97438 -27.464705,152.97408 -27.465314,152.973703 -27.46588,152.972592 -27.466594,152.972378 -27.466731,152.972112 -27.466902,152.971662 -27.46718,152.971331 -27.467384,152.970998 -27.46759,152.970474 -27.467535,152.970473 -27.467621,152.970477 -27.467655,152.970485 -27.467673,152.970499 -27.467691,152.970537 -27.467722,152.970569 -27.467749,152.970586 -27.467766,152.97061 -27.467781,152.970633 -27.467798,152.970647 -27.467818,152.970654 -27.467846,152.970643 -27.467867,152.970625 -27.4679,152.970574 -27.467988,152.970557 -27.468024,152.970533 -27.468062,152.970493 -27.468098,152.970461 -27.468128,152.970437 -27.468145,152.970324 -27.468171,152.969865 -27.4679,152.969698 -27.4677,152.969423 -27.466939,152.969212 -27.466833,152.967559 -27.465996,152.967053 -27.465482,152.966176 -27.465614,152.965628 -27.465528,152.965404 -27.465352,152.965527 -27.46525,152.965778 -27.465041,152.96585 -27.465011,152.965904 -27.464286,152.965999 -27.463018,152.966792 -27.463792,152.967741 -27.464153,152.967865 -27.4642,152.967903 -27.463937,152.9678 -27.463899,152.967735 -27.463865,152.967651 -27.463831,152.967594 -27.46377,152.967555 -27.463695,152.967497 -27.463605,152.967433 -27.463596,152.967401 -27.463618,152.967353 -27.463634,152.967288 -27.463656,152.967215 -27.463668,152.967169 -27.463625,152.96711 -27.463574,152.967055 -27.463497,152.967012 -27.463453,152.966967 -27.46339,152.966964 -27.463325,152.966916 -27.463227,152.966884 -27.463165,152.966809 -27.463075,152.966747 -27.463008,152.966628 -27.462916,152.966506 -27.46283,152.966391 -27.462831,152.966295 -27.462806,152.966229 -27.462739,152.966194 -27.462702,152.966152 -27.46265,152.966098 -27.462592,152.966075 -27.462554,152.96604 -27.462504,152.965985 -27.462412,152.965925 -27.462715,152.965311 -27.46285,152.963905 -27.462083,152.963268 -27.46127,152.963371 -27.460747,152.964283 -27.460207,152.964975 -27.460382,152.966268 -27.460984,152.966397 -27.460331,152.96646 -27.460012,152.96664 -27.459099,152.966453 -27.459074,152.958335 -27.458025,152.958657 -27.456434,152.959015 -27.454672,152.959051 -27.454492,152.962212 -27.454979,152.96237 -27.455004,152.962376 -27.454969,152.964977 -27.454887,152.965027 -27.454806,152.965077 -27.454725,152.965413 -27.454179,152.965974 -27.453919,152.966808 -27.453906,152.967493 -27.453703,152.967549 -27.453255,152.967695 -27.453282,152.967775 -27.452867,152.967967 -27.4529,152.968198 -27.452939,152.96854 -27.452997,152.96858 -27.453003,152.968944 -27.453064,152.969358 -27.453133,152.969289 -27.453491,152.969783 -27.453539,152.970143 -27.453574,152.970696 -27.453655,152.970798 -27.453309,152.9716 -27.453466,152.972681 -27.452638,152.973213 -27.452795,152.973355 -27.453028,152.973515 -27.453075,152.974381 -27.453071,152.974713 -27.453281,152.974547 -27.453501,152.974711 -27.453604,152.974648 -27.453687,152.974679 -27.453731,152.974805 -27.453907,152.974931 -27.454082,152.975014 -27.454199,152.975098 -27.454316,152.975182 -27.454433,152.975265 -27.454549,152.97529 -27.454583,152.975285 -27.454609,152.975271 -27.454682,152.975239 -27.454844,152.975208 -27.455006,152.975265 -27.455015,152.975559 -27.45506,152.975607 -27.45503,152.975811 -27.455045,152.975839 -27.454925,152.975868 -27.4548,152.975882 -27.454776,152.975908 -27.454762,152.975938 -27.454761,152.976043 -27.454609,152.975953 -27.454482,152.976262 -27.454307,152.976291 -27.454384,152.976489 -27.454428,152.976686 -27.454473,152.976884 -27.454517,152.976916 -27.454524,152.977081 -27.454561,152.977279 -27.454605,152.977408 -27.454631,152.977481 -27.454611,152.977603 -27.454577,152.977767 -27.454531,152.97793 -27.454429,152.978094 -27.454684,152.978183 -27.454639,152.9782 -27.454463,152.978386 -27.454379,152.978436 -27.454378,152.97857 -27.454215,152.978433 -27.454087,152.978306 -27.453969,152.978356 -27.45384,152.97841 -27.453703,152.978477 -27.453529,152.978552 -27.453337,152.978653 -27.453079,152.978833 -27.453105,152.978853 -27.452998,152.978873 -27.45289,152.978893 -27.452783,152.978913 -27.452675,152.978914 -27.452657,152.978982 -27.452667,152.978916 -27.452565,152.978718 -27.452259,152.978827 -27.45205,152.978831 -27.451994,152.978914 -27.45184,152.978934 -27.451821,152.978962 -27.451815,152.978988 -27.451825,152.9792 -27.451979,152.979321 -27.451817,152.979324 -27.451832,152.979365 -27.451715,152.979404 -27.451658,152.979448 -27.45162,152.979539 -27.45153,152.9797 -27.451457,152.979812 -27.45141,152.980029 -27.451332,152.980154 -27.451324,152.980399 -27.451357,152.980557 -27.451355,152.980707 -27.451305,152.980806 -27.451269,152.981048 -27.451105,152.981163 -27.451042,152.98135 -27.45095,152.981477 -27.450834,152.98152 -27.450758,152.981523 -27.450743,152.981524 -27.450684,152.981538 -27.450626,152.981562 -27.450563,152.981652 -27.450502,152.981718 -27.450457,152.981922 -27.450264,152.982037 -27.4502,152.982155 -27.450125,152.982289 -27.450037,152.982385 -27.449979,152.982468 -27.449949,152.982619 -27.449909,152.98288 -27.449827,152.983179 -27.449698,152.98343 -27.449633,152.983578 -27.449604,152.983751 -27.449492,152.983805 -27.449433,152.983997 -27.449274,152.984193 -27.449089,152.984359 -27.448862,152.984442 -27.448741,152.984456 -27.448679,152.984436 -27.448634,152.984382 -27.448566,152.984328 -27.448511,152.984305 -27.448478,152.984297 -27.448448,152.984307 -27.448419,152.984355 -27.448348,152.984589 -27.448132,152.984716 -27.448001,152.984715 -27.447976,152.984799 -27.447851,152.984844 -27.44784,152.98498 -27.447629,152.985086 -27.447518,152.985194 -27.447451,152.985471 -27.447351,152.985536 -27.447314,152.985668 -27.44728,152.985872 -27.447289,152.986138 -27.447344,152.986305 -27.447423,152.986393 -27.447532,152.986464 -27.447672,152.986546 -27.447878,152.986737 -27.448173,152.986964 -27.448461,152.9871 -27.448586))

South Queensland polygon:

POLYGON((152.608337402344 -28.2656823901465,152.578125 -28.3297687914445,152.528686523438 -28.3043806829628,152.530059814453 -28.2608441148048,152.479248046875 -28.2608441148048,152.479248046875 -28.2608441148048,152.369384765625 -28.3527337602378,152.369384765625 -28.3527337602378,152.29248046875 -28.3962327116804,152.210083007812 -28.4542035499492,152.210083007812 -28.4542035499492,152.16064453125 -28.4155598516658,152.16064453125 -28.4155598516658,151.979370117188 -28.5266224186481,151.979370117188 -28.5266224186481,151.979370117188 -28.5989915677057,151.979370117188 -28.5989915677057,152.039794921875 -28.7532125379903,152.039794921875 -28.7532125379903,152.02880859375 -28.868729056029,151.990356445312 -28.9072060763367,151.990356445312 -28.9072060763367,151.847534179688 -28.9072060763367,151.847534179688 -28.9072060763367,151.688232421875 -28.8879693483034,151.688232421875 -28.8879693483034,151.490478515625 -28.950475674848,151.490478515625 -28.950475674848,151.28173828125 -28.9120147012556,151.28173828125 -28.9120147012556,151.116943359375 -28.8350499726352,151.116943359375 -28.8350499726352,151.023559570312 -28.8254253744772,151.023559570312 -28.8254253744772,151.007080078125 -28.714678586706,150.75439453125 -28.6134594240044,150.75439453125 -28.6134594240044,150.748901367188 -28.555576049186,150.748901367188 -28.555576049186,150.53466796875 -28.4831770585708,150.53466796875 -28.4831770585708,150.27099609375 -28.5314485763192,150.27099609375 -28.5314485763192,149.87548828125 -28.6279252876186,149.87548828125 -28.6279252876186,149.661254882812 -28.6038144078413,149.661254882812 -28.6038144078413,149.556884765625 -28.5700498796474,149.4580078125 -28.5845217193704,149.4580078125 -28.5845217193704,149.375610351562 -28.700224692777,149.375610351562 -28.700224692777,149.12841796875 -28.8157998864873,149.12841796875 -28.8157998864873,148.936157226562 -28.9985318140518,140.99853515625 -29.0081403629782,140.99853515625 -26.0024871419457,138.004760742188 -25.9926124897092,137.96630859375 -24.8764699108315,137.96630859375 -24.8764699108315,140.55908203125 -24.806681353852,140.55908203125 -24.806681353852,143.173828125 -25.284437746983,143.173828125 -25.284437746983,144.810791015625 -24.567108352576,144.810791015625 -24.567108352576,145.34912109375 -24.1066471792018,145.34912109375 -24.1066471792018,146.6015625 -24.1868474285212,146.6015625 -24.1868474285212,149.798583984375 -25.1751165316218,151.23779296875 -24.6969342263667,152.017822265625 -24.4971463205719,152.149658203125 -24.6470171626304,152.149658203125 -24.6470171626304,152.314453125 -24.7568083111927,152.314453125 -24.7568083111927,152.479248046875 -24.6969342263667,152.479248046875 -24.6969342263667,152.51220703125 -24.966140159913,152.51220703125 -24.966140159913,152.698974609375 -25.1850588835807,152.698974609375 -25.1850588835807,152.874755859375 -25.2148810711326,152.874755859375 -25.2148810711326,153.12744140625 -25.0557451170153,153.204345703125 -24.9163314045991,153.204345703125 -24.9163314045991,153.138427734375 -24.746831298412,153.138427734375 -24.746831298412,153.226318359375 -24.6570021732791,153.336181640625 -24.9262947663956,153.336181640625 -24.9262947663956,153.094482421875 -25.7801071184222,153.088989257812 -25.8097819758404,152.792358398438 -25.8246166182321,152.792358398438 -25.8246166182321,152.166137695312 -25.8888785821271,152.166137695312 -25.8888785821271,152.138671875 -26.071586453171,152.138671875 -26.071586453171,152.259521484375 -26.2638622801111,152.259521484375 -26.2638622801111,152.391357421875 -26.3967902051027,152.391357421875 -26.3967902051027,152.627563476562 -26.5639633713402,152.627563476562 -26.5639633713402,152.589111328125 -26.7603258373944,152.589111328125 -26.7603258373944,152.611083984375 -26.8926790959082,152.611083984375 -26.8926790959082,152.814331054688 -27.0786915529275,152.698974609375 -27.1202574690006,152.738800048828 -27.381523191705,152.738800048828 -27.381523191705,152.873382568359 -27.7552536093976,152.876129150391 -27.7576841561385,152.832183837891 -27.885211577912开发者_开发技巧2,152.832183837891 -27.8852115779122,152.826690673828 -28.075615439519,152.826690673828 -28.075615439519,152.86376953125 -28.3116350467506,152.81982421875 -28.3370214231953,152.764892578125 -28.3527337602378,152.764892578125 -28.3527337602378,152.690734863281 -28.3333951691964,152.690734863281 -28.3333951691964,152.655029296875 -28.3067988591941,152.628936767578 -28.3043806829628,152.628936767578 -28.3043806829628,152.608337402344 -28.2656823901465))

North Brisbane Polygon:

POLYGON((153.0392074585 -27.418404876708,153.06083679199 -27.414138203938,153.08109283447 -27.414138203938,153.08624267578 -27.424804576823,153.08692932129 -27.43607933646,153.09722900391 -27.443087390371,153.1010055542 -27.447048267433,153.11405181885 -27.441259245303,153.12023162842 -27.43272750118,153.14357757568 -27.421757146832,153.15628051758 -27.402251603719,153.164176940918 -27.3589613881093,153.105125427246 -27.3388348190319,153.073883056641 -27.2845358519143,153.102722167969 -27.2662268432579,153.114395141602 -27.2633276402901,153.123664855957 -27.231889324324,153.116970062256 -27.1883799269902,153.101348876953 -27.1903649358738,153.04676055908 -27.192960663416,153.034400939941 -27.1775381013414,153.0704498291 -27.109867436717,152.99354553223 -27.03649860919,152.90840148926 -27.024265804424,152.85484313965 -27.031605647184,152.80883789062 -27.101921467004,152.71476745605 -27.131868716147,152.70858764648 -27.214945578838,152.72163391113 -27.331515160145,152.75939941406 -27.412614352295,152.83836364746 -27.472942804291,152.85209655762 -27.444306136921,152.86239624023 -27.406518735533,152.90084838867 -27.398593931034,152.91595458984 -27.371157528764,152.941360473633 -27.3656694317512,152.987022399902 -27.3757307350603,153.001098632812 -27.3882298678595,153.014144897461 -27.4071283123416,153.027534484863 -27.4153572701181,153.0392074585 -27.418404876708))

Inner Brisbane polygon:

POLYGON((153.09482574463 -27.483146627112,153.10941696167 -27.503855957645,153.10735702515 -27.51786300129,153.08881759644 -27.534151558368,153.09396743774 -27.544501910396,153.11319351196 -27.547393746242,153.14580917358 -27.56580838805,153.13997268677 -27.598520997659,153.12726974487 -27.605062348558,153.09259414673 -27.612059640474,153.05276870728 -27.615558118831,153.00590515137 -27.625444518702,152.99509048462 -27.648103820206,152.96659469604 -27.642933654548,152.92385101318 -27.61571022405,152.91904449463 -27.590762145112,152.86308288574 -27.580720462015,152.85999298096 -27.544806317754,152.8551864624 -27.515274877553,152.83184051514 -27.487410631243,152.85106658936 -27.445829551176,152.86308288574 -27.406213945867,152.90153503418 -27.398898741713,152.91595458984 -27.369633084668,152.941017150879 -27.3638400056117,152.988739013672 -27.3739014752212,153.004188537598 -27.3873153450577,153.016548156738 -27.405909155361,153.038177490234 -27.4162715609242,153.079719543457 -27.4126143522949,153.087959289551 -27.4248045768235,153.087959289551 -27.4354699194348,153.099975585938 -27.4446108214545,153.089160919189 -27.446743589631,153.060321807861 -27.4406498568828,153.050022125244 -27.4417162844096,153.043155670166 -27.4414115918823,153.040409088135 -27.4350128544576,153.032341003418 -27.4456772106967,153.017749786377 -27.4418686303578,153.00178527832 -27.4465912504143,152.999897003174 -27.4502473335489,152.990627288818 -27.4470482674331,152.989940643311 -27.4645658255604,152.995777130127 -27.4698967038784,153.004875183105 -27.4747704241218,153.012256622314 -27.4697443966476,153.017406463623 -27.468830548842,153.026332855225 -27.4797962223421,153.030796051025 -27.4808622712773,153.033542633057 -27.4761411191008,153.032341003418 -27.4668505192685,153.035945892334 -27.4633473028864,153.039035797119 -27.4670028304985,153.04126739502 -27.4752273243429,153.045558929443 -27.4767503113957,153.054656982422 -27.4720289830085,153.056373596191 -27.4689828573358,153.052253723145 -27.4625857193753,153.04967880249 -27.4433920782702,153.058433532715 -27.4417162844096,153.087100982666 -27.4484193071291,153.101348876953 -27.4458295511756,153.108730316162 -27.4510090022823,153.117485046387 -27.469287473692,153.097915649414 -27.4769026089431,153.09482574463 -27.483146627112))

Brisbane CBD polygon:

POLYGON((153.041095733643 -27.4337940052648,153.044013977051 -27.4409545515136,153.049507141113 -27.442478012047,153.052425384521 -27.4630426701134,153.056373596191 -27.4686782401377,153.054656982422 -27.4720289830085,153.045558929443 -27.4767503113957,153.04126739502 -27.4752273243429,153.038864135742 -27.4673074523273,153.035945892334 -27.4634996189573,153.032341003418 -27.4665458961769,153.033456802368 -27.4756842226692,153.03053855896 -27.4810907089926,153.026847839355 -27.4799485156788,153.017234802246 -27.468830548842,153.012256622314 -27.4697443966476,153.004231452942 -27.475036949481,153.000755310059 -27.4722193630674,152.995433807373 -27.4706582368754,152.988739013672 -27.4654035520852,152.988309860229 -27.461595652945,152.987794876099 -27.4583207544854,152.985734939575 -27.4579399460257,152.986164093018 -27.4564928618847,152.988653182983 -27.4540556246159,152.98942565918 -27.4459057213361,152.996463775635 -27.4455248700075,152.997665405273 -27.4462865713499,152.99732208252 -27.4488763165743,152.999811172485 -27.4494856595558,153.001012802124 -27.4478099582569,153.000583648682 -27.4460580614994,153.001527786255 -27.4448393343027,153.004961013794 -27.4446108214545,153.009080886841 -27.4410307250398,153.010969161987 -27.440192813359,153.01251411438 -27.4421733216231,153.015089035034 -27.4417162844096,153.017749786377 -27.4405736830936,153.021183013916 -27.4423256669402,153.028049468994 -27.4443823081329,153.029508590698 -27.4435444219045,153.032255172729 -27.4449916759386,153.032855987549 -27.441944803253,153.03783416748 -27.4383646203335,153.038949966431 -27.4341748970835,153.041095733643 -27.4337940052648))

South Brisbane polygon:

POLYGON((152.86308288574 -27.580720462015,152.838363647461 -27.5788946026678,152.834243774414 -27.6585954639474,152.85209655762 -27.718788898838,152.87887573242 -27.785631542689,152.98873901367 -27.778949125104,153.079719543457 -27.7634564871653,153.22151184082 -27.8160009916616,153.263397216797 -27.9182834797361,153.34648132324 -27.907058371122,153.440895080566 -27.9061481761228,153.463897705078 -27.7309451582021,153.563804626465 -27.412004805752,153.425102233887 -27.3839620300855,153.336181640625 -27.4671551415181,153.29532623291 -27.5588081442964,153.28262329102 -27.535673730105,153.22288513184 -27.547241546253,153.19953918457 -27.5539381462411,153.1761932373 -27.558808144296,153.14254760742 -27.566112736347,153.132934570312 -27.5843720895287,153.116455078125 -27.5922835320307,153.086929321289 -27.602019908337,153.051223754883 -27.6068877721431,153.00315856934 -27.623315215692,152.99629211426 -27.648255880202,152.96882629395 -27.643389855465,152.92350769043 -27.612363860419,152.91801452637 -27.58802359546,152.86308288574 -27.580720462015))

Only inner Brisbane and Brisbane CBD should intersect, but all 5 are being returned


II think that INTERSECTS function does not depend strictly on the set of points you have, but on those of the minimum bounding rentangle (MBR) containing it.

From http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html

•MBRIntersects(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 intersect.

From http://howto-use-mysql-spatial-ext.blogspot.com/ :

NOTE: The MySQL Spatial Documentation specifies that the Intersect function for every Geometry is approximated to the Intersect function of the bounding boxes of the involved geometries.

It seems you will get some false positives, but no false negatives, since the MBR is a superset of your Geometry.

Granted, I just looked this up. I hadn't even heard of this stuff ten minutes ago.

0

精彩评论

暂无评论...
验证码 换一张
取 消