Details
Major
Description
The following 2 queries do exactly the same thing: try to determine if point with coordinates N44.4 E26.1 (center of Bucharest) are inside the Bucharest-Ilfov region as defined by NUTS_LAYER data.
One uses the binary shape data, the other textual data of the same shape.
SELECT WITHIN( GEOMFROMTEXT('POINT(26.1 44.4)' ), 0x000000000102000000dd00000097562d44e26a3a409d9e7763c1424640cda94e626b683a40a31d37fcee404640fe68931ee1663a407b4b395f6c414640dc69c6fda25c3a40dfcdfe40393e4640a338317fe0603a405e6743fe193d4640062acd412b603a40807edfbff93c4640751bbee02e613a400536e7e0193c4640977730bd58623a400312f8c34f3c46401ad97ff854623a40548ec9e27e3b46406de7566364653a40be16f4de983b4640323834c060653a40a6d1e4628c3a4640f8922122906d3a407c9c69c2763a46401f4d501e71703a40b7989f1b1a3b4640749c363eee713a40838aaa5f693a4640ecdcf2635a733a409649ed357b3a4640d28bc45fee733a40b1fb8ee1313b46405ad9999da0753a40770fd07db93a46401d1f62972f743a407a82c5b45f3a46405c6f850bd4763a409be447fc0a3a46407dab5f4436683a40c631923dc23646404d467fc39f653a409a1189424b364640fdaab7616f643a408351499d003746402ba798de59593a40d3a9d6c2ac344640e0320285d5583a400adcba9b273346402899863cdd573a4072fdbb3ef33246405003610212573a404792205c8133464026a8cbbd1b533a40f37e87a2c03246405ccd959c6e553a4002bb9a3ce53146408cc5cac39d533a4084d89942673146406ee415e357523a40af95d05d92314640f469ff5e65513a40be2ec37f3a334640abcb1363f44e3a405713ef00cf334640117750e4a44e3a40a8aad040ac34464081202ae8514b3a4087a8c29f61354640ee96cee220433a40cb3ac7806c344640e76f2c83c5493a40fc992501ea3346407e180b9e9d4a3a40438cd7bc2a334640fd849dbd8e4c3a409868908227334640be139dc0cf4c3a401b9fc9fef93146405358930266503a40063065e000324640b7d9b342ec503a40fa997adda22f46408eb685425f543a4039f24064113046407a3695a01a553a40427a8a1ca22f46404371b1fdbf503a405b28999c5a2e46405e2ecc9df4523a40532e38833f2e4640d3a81b83a94e3a404e0d349ff32c46401b86ea419c4d3a40a089b0e1692d46400438a7edba493a404ef2237e452c464071b43e4052483a40d7fa22a1ad2c4640c6317c9f05413a4054b3b27d482a46404f011a0005473a406ad95a5fa42846400efc034070413a40e63c635f3226464017996ac1e6423a406122895ec62546403c8b3963f3413a40b6c5e0615a254640c104c99640403a4003b5183ccc2546409eea7afda4363a40b55208e492214640591bbee02e353a40feb4519d8e224640156c0d00b0303a40b7ec10ff30204640d86c40df71323a40b9551003dd1f4640d9d2fe6211313a4012dbdd03f41e46405e9d4da2b92c3a402d95b7231c204640c28526e4de2a3a40fbc9181fe61e46403b736ac2512c3a404b0169ff831e4640fb9bab41f3293a4029417fa1c71d46401057b840dd2a3a403b8908ffa21b46404803d3c496283a40ef75525f161c4640fa473a5edc263a4091442fa3d81d4640c32ac82a00183a40f0033bfe8b2046402123fb7b84163a40f92f1004482246409290323dbc0f3a40954737c22222464048f8c841640b3a40adf886c2e7234640494a647a1d093a40de1e8480fc23464066f48d4358093a404eb6813bd0244640fc17f26327083a403f53af5b8425464020f2f16492053a40e869c020692546408cb8ea950d033a408a93fb1d0a264640ddeb8e20f0023a40fb96395d96264640fd664c1cd4003a4060e5d0225b264640b30a8563f1f93940c77020248b22464034f3cefcc5003a40fdf9b66029274640fc74971f13ff3940eb8d5a617a28464004c40ebe8bf9394066a3737e0a2a4640449149a187f839404f07b29eda294640368fadc2c1f839401de4f560d22846401281d48107f439403244f98296284640637b174a81f33940bd25e4831e2946405f2a919fe8f4394076e272bc822946404dd4bc3ecef43940d14659bf192a4640d65d34bff5f139403eec8502362a464012c3691c45f0394058703fe0012b46401a715c2107e939405b5b785eaa2a46403c35f2d47ee539409762f206182c4640a9bf48c384e339405e6743fe192c4640ca558737c6e43940ce0017644b2d46400beef19df3e339409a081b9ede2d4640fb9bab41f3df394093b4c6a0932d46402102f8020edd3940e5637781122e4640f126a9a7eade3940aadec5fbf12e46405f763980d9db3940fab48afe50304640ae47cbdcd7db3940a73e90bcf331464080f9bf7ee3d9394014f1811d7f314640726fd97cb7d33940e63fa4df3e3246408a90a4ff40d339408e812040063346401b45c07c0dd539409f1d705db1334640591c29b6ddde39408aabcabeab3546407cb58d9a0add3940d567b27f1e364640faea94a2f0dd394062670a9d573646408d60cd5c3bd939408bc058df403746401b6899a0e1dd39403a62d861cc3846403defb0e485dc39407aabae43b53b46402d058e5fd3d9394039ecbe63f83b4640d8a5a1a1dadc3940a77a32ff683d46405003610212df39407a09f9a0e73c4640d690138145de39400da5f6225a3b46407100e72043df39409f20b1ddbd3a4640bdb266bf49e1394060c77f81a03a46408cc5cac39de13940677c5f5c2a3b4640de363d83e1e6394048747ade0d3a464057636424d6ea39402829b000263d46406a82925dc4e53940747b4963343e4640fb26a83c15ec3940aea70183244246408259fc016ae4394010751f805445464007bd923f73e039407a39ecbee3464640304438c192e339403695456197474640ca6aa400ace03940575bb1bf6c484640c64fcd40c0e239400c9d82fcec4846409e925dc4d2e039405098dbbd5c494640e63c4dc175e339400da5f6225a4a46405b632bc313e83940520ababd24484640634bea5fedee3940ec8497e0544946409456d7fc53fe3940411af9bc624e46402505002258fb39405ad93ee4ad504640e3df51befbf73940a68922a4ee504640dcdd5e2dd2f03940ae9e93deb75246401ef62c639ef139406ceee87f3954464006d31ade07f639406d54a703d95346409cf67efed6f8394086ff7403855446405643cc800ef7394046da71c36f554640b652639d85f73940a73b4f3ce75546400b7bc44373fb394006d9b27cdd5446408d7bdd7de9fb39402e1d739e31554640a4e6069ed6f9394093c9a99de1554640f12a55fda5fb39405098dbbd5c5646401d9837e224f9394076df313c76564640409e4720b9f539404d56f0db905746409071207fc4fd39401eff058280584640d1abebab06fd39402030babcb9594640ed6877a3eaf93940d1066003a259464048de23f664f839402984b9dd4b5c4640aeb64ce19cf939400e87a5819f5d4640c2a8ff07b3f739403b8c497faf5d46407b1a8b016df73940f3cccb61775e46405d39d62027f63940014eefe27d5e46409bc39f3c87f6394041ffe89bb45f46400b0e8a41bbf7394044fb58c1ef5f4640bb9dd89eb4f839409f3e027f785e4640fd9fadde3cfd39403b7aa7026e5f4640fb05a5c39e003a40e2218c9f465e46407e8d0ea37b063a409947fe60605f4640d1292389b90c3a4062821abe055f464041857e01180d3a40da37f7570f5e4640dadfc380800d3a4049827005945e46403750caff3f103a404913ef00cf5e4640ee240c5e4f103a407b57e883e55f4640a4cbf67c28133a4073d9e89c1f604640029af27c61143a402148895d5b5f46402d058e5fd3153a4052f2ea1c835f4640a55247228f163a40228d0a9cec5e4640561234c1cb183a40b29e5a7df55e4640b70fd484481a3a409e996038575e46402b6503441c173a4014967840596146403a79ecc26a1a3a40b66ad784346246409cdeaf5d351d3a408196ae609b604640b364e99ed01f3a40c5ef517f3d604640698e965ef6233a40b3a487a155604640b98c857c2b243a40969867252d614640ac94f9a2982e3a409a2fdae305604640fd698d9ce02d3a40e08e1b7eb760464066a5a4e25a2f3a4062821abe05614640eb6cb2a1f6313a409a3e3be03a6046402235d7c45d3d3a40e2f198814a5f4640368c6c42b53d3a40e700c11cbd5e4640ce309f07d23f3a40b05f5b3f7d5e4640312eb022fe413a401781b1be015f46402f1c638275423a40c9acdee1f66046403b3a0900e9453a40d6d0b183ca604640745e4df96b463a40b47d737f75624640813ca5deae4d3a40321d3a3d6f6246405d16fdfc52593a401a69a9bc9d5d464066c6360422603a40d252793b425c4640a23eb37ff95c3a40439259bd435a4640d5b727a3255f3a40163c308070594640addd60037d5c3a401bf33ae210584640c4622f6f69593a40a5d76663a557464071c444decd573a40ae8383bd0958464024a188a0c5543a403259dc7fe4564640ff9b72e030593a40a48cb800b4554640db1a6c45f65d3a40fa997adda252464028dab0016c5a3a406edfa3fefa5146403602db1c425b3a40b9196ec067514640344d3338a55e3a402920ed7f00524640cb15399f95603a407fc00303884f464082209b3f01663a404380b74002504640e603ecfe0c673a40dd88d2de604e4640de1e6ee23f5f3a401bab7823734b4640b4c51fa056613a40af9811de9e4a46406b7f51ddb75c3a40ff59f3e3af484640d12a8e5e685e3a409eb646046348464042284a9ddb5c3a401106499fd6474640fb7f304b965b3a40da91ea3b3f48464043c2e160ca563a405e6743fe19464640db88111d5d593a406f4be482b3464640494e8127885e3a4082902c6082444640b60d8d823b5c3a40f4c308e15144464016d84d84685d3a40015130638a4346406e312999f7663a4093d217424e44464092ae83de76673a40295c8fc275434640b213b93bc6653a4012691b7f22424640b1cae08332673a40d36a48dce3414640b1a4c6df51693a405340daff8043464097562d44e26a3a409d9e7763c1424640 ) SELECT WITHIN( GEOMFROMTEXT('POINT(26.1 44.4)' ), GEOMFROMTEXT(' LINESTRING(26.4175150500001 44.52152675,26.4078885500001 44.50729325,26.40187255 44.51111975,26.36186205 44.4861222500001,26.3784255500001 44.47735575,26.3756600500001 44.47637175,26.3796215500001 44.46953975,26.38416655 44.4711842500001,26.3841090500001 44.46480975,26.39606305 44.46560275,26.3960075500001 44.45740925,26.42798055 44.45674925,26.43922605 44.46173425,26.44504155 44.45634075,26.4505979984214 44.4568850906973,26.4528560500001 44.46245975,26.45948205 44.45878575,26.453851186233 44.4560457195666,26.4641730500001 44.45346025,26.4070780500001 44.42780275,26.3969690500001 44.4241717500001,26.39232455 44.42970625,26.34902755 44.4115222500001,26.34700805 44.39964625,26.3432195500001 44.39804825,26.34011855 44.40238525,26.3246420500001 44.3965037500001,26.33371905 44.38980825,26.32662605 44.38596375,26.32165355 44.38727925,26.3179530500001 44.40022275,26.3084165500001 44.4047547500001,26.30720355 44.41150675,26.2942185500001 44.41704175,26.2622205500001 44.4095612500001,26.2881700500001 44.4055787500001,26.2914675500001 44.39974175,26.2990530500001 44.39964325,26.3000450500001 44.39044175,26.3140565500001 44.39065175,26.31610505 44.37215775,26.32957855 44.37553075,26.3324375500001 44.37213475,26.3154295500001 44.36214025,26.3240450500001 44.3613132500001,26.30727405 44.35118475,26.30316555 44.35479375,26.2880085500001 44.34587075,26.28250505 44.34904875,26.2539920500001 44.3303372500001,26.2774200500001 44.31751625,26.25561905 44.29841225,26.2613335500001 44.2951162500001,26.25762005 44.2918207500001,26.25098555 44.29529525,26.2134550500001 44.26229525,26.20774655 44.26997725,26.1901855500001 44.25149525,26.19705005 44.24893225,26.19167155 44.24182175,26.1747075500001 44.25085875,26.1674635500001 44.24139775,26.17312255 44.23840325,26.16386805 44.23265475,26.1674385500001 44.21591175,26.15855055 44.21943275,26.15180005 44.23317375,26.0937525500001 44.2542722500001,26.08795905 44.26782275,26.0614660500001 44.26668575,26.0444985500001 44.28051025,26.0356060500001 44.28114325,26.0365030500001 44.28760475,26.0318510500001 44.29310175,26.02176505 44.29227075,26.0119260500001 44.29718375,26.0114765500001 44.30146375,26.0032365500001 44.29965625,25.9763395500001 44.2698712500001,26.0030210500001 44.30595025,25.99638555 44.31623475,25.9747885500001 44.32844525,25.97081955 44.32698425,25.9717065500001 44.31892025,25.9532395500001 44.3170932500001,25.9511915500001 44.3212437500001,25.95667455 44.32430225,25.9562720500001 44.3289107500001,25.94515605 44.32977325,25.93855455 44.33599475,25.91026505 44.33332425,25.89646655 44.3444832500001,25.8887445500001 44.34454325,25.89364955 44.35386325,25.8904360500001 44.35835625,25.87480555 44.3560677500001,25.8634950500001 44.35993975,25.8707680500001 44.3667597500001,25.8587875500001 44.37747175,25.8587625500001 44.39025075,25.85112755 44.3866917500001,25.82701855 44.39254375,25.8252105500001 44.3986282500001,25.8322370500001 44.40385025,25.87057055 44.41930375,25.8634430500001 44.4228057500001,25.8669530500001 44.42454875,25.8485620500001 44.43166725,25.86672405 44.4437372500001,25.8614180500001 44.46646925,25.8508815500001 44.46851775,25.86271105 44.47976675,25.87136855 44.4758187500001,25.86824805 44.46368825,25.8721180500001 44.45891925,25.88003155 44.45802325,25.88131355 44.46223025,25.9018785500001 44.4535482500001,25.91733005 44.47772225,25.8975275500001 44.48597375,25.92219905 44.5167392500001,25.89224255 44.54164125,25.87675855 44.55382525,25.8889580500001 44.55930725,25.8776245500001 44.56581875,25.8857460500001 44.5697322500001,25.8782160500001 44.5731427500001,25.8885155500001 44.58087575,25.90655155 44.56362125,25.93330955 44.57290275,25.99346905 44.6123882500001,25.9818135500001 44.63030675,25.9686850500001 44.6322827500001,25.94070705 44.64623625,25.9438230500001 44.65800475,25.9610575500001 44.65506025,25.9720305500001 44.66030925,25.9650650500001 44.6674732500001,25.96688255 44.67111925,25.9822275500001 44.66300925,25.9840315500001 44.66557675,25.97593105 44.67094775,25.9830015500001 44.6747052500001,25.97321905 44.67548325,25.9598560500001 44.6841082500001,25.99127955 44.69142175,25.9883830500001 44.7009807500001,25.97623655 44.70025675,25.9702905500001 44.7210652500001,25.9750500500001 44.73143025,25.96757555 44.73191825,25.96650705 44.73801825,25.96153455 44.73821675,25.9630010500001 44.7476992500001,25.96770105 44.74950425,25.97150605 44.73805225,25.9892100500001 44.7455447500001,26.0024225500001 44.73653025,26.0253240500001 44.74512875,26.04970605 44.74236275,26.05114755 44.73484325,26.0527420500001 44.73889225,26.0634765500001 44.74069225,26.0637110500001 44.7491917500001,26.07483655 44.75096475,26.0796125500001 44.7449757500001,26.0852565500001 44.74618875,26.08812155 44.74159575,26.09685905 44.74186675,26.10266905 44.73703675,26.0902750500001 44.76053625,26.10319155 44.76722775,26.1140955500001 44.75474175,26.12427705 44.7518767500001,26.1404780500001 44.7526132500001,26.1412885500001 44.75919025,26.18201655 44.7501797500001,26.1792085500001 44.7555997500001,26.18498055 44.75798775,26.1951695500001 44.75179675,26.2397120500001 44.7444612500001,26.2410470500001 44.74014625,26.2492985500001 44.7381972500001,26.25778405 44.74224075,26.25960555 44.75753425,26.27308655 44.7561802500001,26.2750850500001 44.7692107500001,26.3034495500001 44.76901975,26.3489225500001 44.73137625,26.37551905 44.72077125,26.3631820500001 44.70519225,26.37166805 44.6987457500001,26.3612825500001 44.68801525,26.34926505 44.68473475,26.3429850500001 44.68779725,26.33114055 44.67884825,26.34840205 44.66955575,26.36703905 44.64559525,26.35321055 44.64047225,26.3564775500001 44.63597875,26.36970855 44.64064025,26.37728305 44.62133825,26.39845655 44.6250687500001,26.4025420500001 44.6123312500001,26.3720685500001 44.5894512500001,26.3802280500001 44.58297325,26.3621805500001 44.56786775,26.36878005 44.56552175,26.36272605 44.5612372500001,26.35776205 44.56442975,26.3390255500001 44.54766825,26.3490770500001 44.55235325,26.36926505 44.53522875,26.3602830500001 44.53374875,26.3648760500001 44.52766075,26.4022155500001 44.5336382500001,26.4041575500001 44.52703125,26.39755605 44.51667775,26.40311455 44.51476625,26.4114055500001 44.52737425,26.4175150500001 44.52152675) ') )
Both these queries return 0.
Tried WITHIN() and ST_WITHIN(). Same result.
Both queries return 1 if run on Mysql-5.0.67 and 5.1.56. (these I had available for testing).
I discovered this bug while trying to determine why a certain functionality of www.opencaching.ro website did not work. This website is part of the Opencaching network and is based on OCPL code with live SVN sync with the project's repository.
The other two websites using this code (www.opencaching.pl and www.opencaching.nl) use an older version of mysql as database backend and do not experience this problem.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
That query returns 1 as You probably expect:
select
WITHIN( GEOMFROMTEXT('POINT(26.1 44.4)' ),
GEOMFROMTEXT('POLYGON((26.4175150500001 44.52152675,26.4078885500001 44.50729325,26.40187255 44.51111975,26.36186205 44.4861222500001,26.3784255500001 44.47735575,26.3756600500001 44.47637175,26.3796215500001 44.46953975,26.38416655 44.4711842500001,26.3841090500001 44.46480975,26.39606305 44.46560275,26.3960075500001 44.45740925,26.42798055 44.45674925,26.43922605 44.46173425,26.44504155 44.45634075,26.4505979984214 44.4568850906973,26.4528560500001 44.46245975,26.45948205 44.45878575,26.453851186233 44.4560457195666,26.4641730500001 44.45346025,26.4070780500001 44.42780275,26.3969690500001 44.4241717500001,26.39232455 44.42970625,26.34902755 44.4115222500001,26.34700805 44.39964625,26.3432195500001 44.39804825,26.34011855 44.40238525,26.3246420500001 44.3965037500001,26.33371905 44.38980825,26.32662605 44.38596375,26.32165355 44.38727925,26.3179530500001 44.40022275,26.3084165500001 44.4047547500001,26.30720355 44.41150675,26.2942185500001 44.41704175,26.2622205500001 44.4095612500001,26.2881700500001 44.4055787500001,26.2914675500001 44.39974175,26.2990530500001 44.39964325,26.3000450500001 44.39044175,26.3140565500001 44.39065175,26.31610505 44.37215775,26.32957855 44.37553075,26.3324375500001 44.37213475,26.3154295500001 44.36214025,26.3240450500001 44.3613132500001,26.30727405 44.35118475,26.30316555 44.35479375,26.2880085500001 44.34587075,26.28250505 44.34904875,26.2539920500001 44.3303372500001,26.2774200500001 44.31751625,26.25561905 44.29841225,26.2613335500001 44.2951162500001,26.25762005 44.2918207500001,26.25098555 44.29529525,26.2134550500001 44.26229525,26.20774655 44.26997725,26.1901855500001 44.25149525,26.19705005 44.24893225,26.19167155 44.24182175,26.1747075500001 44.25085875,26.1674635500001 44.24139775,26.17312255 44.23840325,26.16386805 44.23265475,26.1674385500001 44.21591175,26.15855055 44.21943275,26.15180005 44.23317375,26.0937525500001 44.2542722500001,26.08795905 44.26782275,26.0614660500001 44.26668575,26.0444985500001 44.28051025,26.0356060500001 44.28114325,26.0365030500001 44.28760475,26.0318510500001 44.29310175,26.02176505 44.29227075,26.0119260500001 44.29718375,26.0114765500001 44.30146375,26.0032365500001 44.29965625,25.9763395500001 44.2698712500001,26.0030210500001 44.30595025,25.99638555 44.31623475,25.9747885500001 44.32844525,25.97081955 44.32698425,25.9717065500001 44.31892025,25.9532395500001 44.3170932500001,25.9511915500001 44.3212437500001,25.95667455 44.32430225,25.9562720500001 44.3289107500001,25.94515605 44.32977325,25.93855455 44.33599475,25.91026505 44.33332425,25.89646655 44.3444832500001,25.8887445500001 44.34454325,25.89364955 44.35386325,25.8904360500001 44.35835625,25.87480555 44.3560677500001,25.8634950500001 44.35993975,25.8707680500001 44.3667597500001,25.8587875500001 44.37747175,25.8587625500001 44.39025075,25.85112755 44.3866917500001,25.82701855 44.39254375,25.8252105500001 44.3986282500001,25.8322370500001 44.40385025,25.87057055 44.41930375,25.8634430500001 44.4228057500001,25.8669530500001 44.42454875,25.8485620500001 44.43166725,25.86672405 44.4437372500001,25.8614180500001 44.46646925,25.8508815500001 44.46851775,25.86271105 44.47976675,25.87136855 44.4758187500001,25.86824805 44.46368825,25.8721180500001 44.45891925,25.88003155 44.45802325,25.88131355 44.46223025,25.9018785500001 44.4535482500001,25.91733005 44.47772225,25.8975275500001 44.48597375,25.92219905 44.5167392500001,25.89224255 44.54164125,25.87675855 44.55382525,25.8889580500001 44.55930725,25.8776245500001 44.56581875,25.8857460500001 44.5697322500001,25.8782160500001 44.5731427500001,25.8885155500001 44.58087575,25.90655155 44.56362125,25.93330955 44.57290275,25.99346905 44.6123882500001,25.9818135500001 44.63030675,25.9686850500001 44.6322827500001,25.94070705 44.64623625,25.9438230500001 44.65800475,25.9610575500001 44.65506025,25.9720305500001 44.66030925,25.9650650500001 44.6674732500001,25.96688255 44.67111925,25.9822275500001 44.66300925,25.9840315500001 44.66557675,25.97593105 44.67094775,25.9830015500001 44.6747052500001,25.97321905 44.67548325,25.9598560500001 44.6841082500001,25.99127955 44.69142175,25.9883830500001 44.7009807500001,25.97623655 44.70025675,25.9702905500001 44.7210652500001,25.9750500500001 44.73143025,25.96757555 44.73191825,25.96650705 44.73801825,25.96153455 44.73821675,25.9630010500001 44.7476992500001,25.96770105 44.74950425,25.97150605 44.73805225,25.9892100500001 44.7455447500001,26.0024225500001 44.73653025,26.0253240500001 44.74512875,26.04970605 44.74236275,26.05114755 44.73484325,26.0527420500001 44.73889225,26.0634765500001 44.74069225,26.0637110500001 44.7491917500001,26.07483655 44.75096475,26.0796125500001 44.7449757500001,26.0852565500001 44.74618875,26.08812155 44.74159575,26.09685905 44.74186675,26.10266905 44.73703675,26.0902750500001 44.76053625,26.10319155 44.76722775,26.1140955500001 44.75474175,26.12427705 44.7518767500001,26.1404780500001 44.7526132500001,26.1412885500001 44.75919025,26.18201655 44.7501797500001,26.1792085500001 44.7555997500001,26.18498055 44.75798775,26.1951695500001 44.75179675,26.2397120500001 44.7444612500001,26.2410470500001 44.74014625,26.2492985500001 44.7381972500001,26.25778405 44.74224075,26.25960555 44.75753425,26.27308655 44.7561802500001,26.2750850500001 44.7692107500001,26.3034495500001 44.76901975,26.3489225500001 44.73137625,26.37551905 44.72077125,26.3631820500001 44.70519225,26.37166805 44.6987457500001,26.3612825500001 44.68801525,26.34926505 44.68473475,26.3429850500001 44.68779725,26.33114055 44.67884825,26.34840205 44.66955575,26.36703905 44.64559525,26.35321055 44.64047225,26.3564775500001 44.63597875,26.36970855 44.64064025,26.37728305 44.62133825,26.39845655 44.6250687500001,26.4025420500001 44.6123312500001,26.3720685500001 44.5894512500001,26.3802280500001 44.58297325,26.3621805500001 44.56786775,26.36878005 44.56552175,26.36272605 44.5612372500001,26.35776205 44.56442975,26.3390255500001 44.54766825,26.3490770500001 44.55235325,26.36926505 44.53522875,26.3602830500001 44.53374875,26.3648760500001 44.52766075,26.4022155500001 44.5336382500001,26.4041575500001 44.52703125,26.39755605 44.51667775,26.40311455 44.51476625,26.4114055500001 44.52737425,26.4175150500001 44.52152675))'));
Request REOPEN!
select
within(
geomfromtext('point(1 1)'),
geomfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)')
)
this also returns 0.
point at coordinates 1,1 is definitely inside a 2x2 square. It does not lie on any segment.
point (-1 -1) which is external to the shape returns 0.
point (0 0), point (0 1), point (1 2) return 1, and these ARE on the segments.
I have:
root@oc:~# mysql --version mysql Ver 15.1 Distrib 5.5.35-MariaDB, for Linux (x86_64) using readline 5.1 from Slackware-14.1
I get :
MariaDB [(none)]> select
-> mbr_within(
-> geomfromtext('point(1 1)'),
-> geomfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)')
-> );
ERROR 1305 (42000): FUNCTION mbr_within does not exist
and
MariaDB [(none)]> select within( geomfromtext('point(1 1)'), geomfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)') );
+-------------------------------------------------------------------------------------------+
| within( geomfromtext('point(1 1)'), geomfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)') ) |
+-------------------------------------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
the actual function is MBRWITHIN () without "_".
On MySQL
mysql Ver 14.14 Distrib 5.1.56, for slackware-linux-gnu (x86_64) using readline 5.1
both within() and mbrwithin() return 1 for the above mentioned 2x2 square.
Yes, within(geomfromtext('point(1 1)'), geomfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)')) returns 0, because you ask whether a point is within a line, and it means, by definition, whether a point lies on the line segment. And it does not, thus you get 0, as expected. Perhaps, you want to know whether a point lies within a polygon ? then you need to say that in the query.
select within( geomfromtext('point(1 1)'), polyfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)') );
MariaDB: returns 0
MySQL-5.1: returns 1
Same result if both constructs use polyfromtext.
select within( geomfromtext('point(1 1)'), geomfromtext('polygon((0 0, 0 2, 2 2, 2 0, 0 0))') );
MariaDB: returns 1
MySQL-5.1: returns 1
Is this correct and expected?
If so, why polyfromtext('linestring... does not create a polygon?
Because POLYFROMTEXT only creates a polygon if the text defines a polygon, and you have specified LINESTRING.
As far as I understand, POLYFROMTEXT does the same as GEOMFROMTEXT, but only returns polygons or an error, if the text does not specify a polygon. But as far as I can see, in MySQL (and MariaDB) POLYFROMTEXT is simply a different name for GEOMFROMTEXT with no special checks that the geometry is a polygon.
To understand better the difference between MySQL 5.5 and MariaDB, try
select within(geomfromtext('point(0 2)'), geomfromtext('linestring(0 0, 2 2)'));
that's the difference between MBR calculations and exact.
This is not a bug!
The WITHIN relation in this case means that the POINT(26.1 44.4) lies exactly on the segment of the specified LINESTRING(...). In this case it's 0.
Though that polyline is closed and the POINT lies WITHIN the polygon, surrounded by this line.
The 5.1 behaviour can be reproduced with the MBR_WITHIN() function.