search

magentos-pl / opencaching-pl

Automatically exported from code.google.com/p/opencaching-pl
0 stars 0 forks source link

region from coordinates yields nothing #120

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
When creating or editing a cache, the button "region from coordinates" opens a 
new window to show the region where these coordinates are located, listing 
country, region as found by OCRO and region as found by Google Maps. 

In the case of OCRO, I always get an empty list. 

See attached image.

Original issue reported on code.google.com by andrixnet on 3 Jun 2014 at 1:12

Attachments:

GoogleCodeExporter commented 9 years ago 
some times ago I gave you nuts_layer / nuts_codes tables, probably you do not 
intstalled it properly on your production server. (if I remember good it was 
working good on your test server) Please check these tables, try to restore it 
witch original data

Original comment by wloczynutka on 3 Jun 2014 at 6:23

GoogleCodeExporter commented 9 years ago 
I have loaded the nuts_codes and nuts_layer tables by importing the SQL files 
received. The import was successful, without errors. 
(had to temporarily change server config to accept larger max_allowed_packet 
while doing this import)

Other then this, what could I be missing? 

See attached file. It is the contents of the nuts* tables for Romania, minus 
the shape column which contains binary data.

Original comment by andrixnet on 4 Jun 2014 at 9:08

Attachments:

GoogleCodeExporter commented 9 years ago 
Issue is not a problem in the code, but rather a problem or bug in the 
database. 
OCRO uses mariaDB-5.5.35 (fork of mysql) and there is a problem at DB level. 
Geometric query does not return correct result. 

The query below uses spacial data for RO32 region (bucharest-Ilfov) and tries 
to determine if the center of Bucharest city is WITHIN the shape. 
MySQL 5.0.67 and 5.1.53 return 1 (correct)
MariaDB 5.5.35 returns 0 (incorrect)
Hence the problem of this bug. 

Being a backend problem, I close this report as invalid.

I have reported the bug with MariaDB developers and I will continue to update 
this bug until the problem is solved. 

SELECT
WITHIN(
  GEOMFROMTEXT('POINT(26.1 44.4)' ),
0x000000000102000000dd00000097562d44e26a3a409d9e7763c1424640cda94e626b683a40a31d
37fcee404640fe68931ee1663a407b4b395f6c414640dc69c6fda25c3a40dfcdfe40393e4640a338
317fe0603a405e6743fe193d4640062acd412b603a40807edfbff93c4640751bbee02e613a400536
e7e0193c4640977730bd58623a400312f8c34f3c46401ad97ff854623a40548ec9e27e3b46406de7
566364653a40be16f4de983b4640323834c060653a40a6d1e4628c3a4640f8922122906d3a407c9c
69c2763a46401f4d501e71703a40b7989f1b1a3b4640749c363eee713a40838aaa5f693a4640ecdc
f2635a733a409649ed357b3a4640d28bc45fee733a40b1fb8ee1313b46405ad9999da0753a40770f
d07db93a46401d1f62972f743a407a82c5b45f3a46405c6f850bd4763a409be447fc0a3a46407dab
5f4436683a40c631923dc23646404d467fc39f653a409a1189424b364640fdaab7616f643a408351
499d003746402ba798de59593a40d3a9d6c2ac344640e0320285d5583a400adcba9b273346402899
863cdd573a4072fdbb3ef33246405003610212573a404792205c8133464026a8cbbd1b533a40f37e
87a2c03246405ccd959c6e553a4002bb9a3ce53146408cc5cac39d533a4084d89942673146406ee4
15e357523a40af95d05d92314640f469ff5e65513a40be2ec37f3a334640abcb1363f44e3a405713
ef00cf334640117750e4a44e3a40a8aad040ac34464081202ae8514b3a4087a8c29f61354640ee96
cee220433a40cb3ac7806c344640e76f2c83c5493a40fc992501ea3346407e180b9e9d4a3a40438c
d7bc2a334640fd849dbd8e4c3a409868908227334640be139dc0cf4c3a401b9fc9fef93146405358
930266503a40063065e000324640b7d9b342ec503a40fa997adda22f46408eb685425f543a4039f2
4064113046407a3695a01a553a40427a8a1ca22f46404371b1fdbf503a405b28999c5a2e46405e2e
cc9df4523a40532e38833f2e4640d3a81b83a94e3a404e0d349ff32c46401b86ea419c4d3a40a089
b0e1692d46400438a7edba493a404ef2237e452c464071b43e4052483a40d7fa22a1ad2c4640c631
7c9f05413a4054b3b27d482a46404f011a0005473a406ad95a5fa42846400efc034070413a40e63c
635f3226464017996ac1e6423a406122895ec62546403c8b3963f3413a40b6c5e0615a254640c104
c99640403a4003b5183ccc2546409eea7afda4363a40b55208e492214640591bbee02e353a40feb4
519d8e224640156c0d00b0303a40b7ec10ff30204640d86c40df71323a40b9551003dd1f4640d9d2
fe6211313a4012dbdd03f41e46405e9d4da2b92c3a402d95b7231c204640c28526e4de2a3a40fbc9
181fe61e46403b736ac2512c3a404b0169ff831e4640fb9bab41f3293a4029417fa1c71d46401057
b840dd2a3a403b8908ffa21b46404803d3c496283a40ef75525f161c4640fa473a5edc263a409144
2fa3d81d4640c32ac82a00183a40f0033bfe8b2046402123fb7b84163a40f92f1004482246409290
323dbc0f3a40954737c22222464048f8c841640b3a40adf886c2e7234640494a647a1d093a40de1e
8480fc23464066f48d4358093a404eb6813bd0244640fc17f26327083a403f53af5b8425464020f2
f16492053a40e869c020692546408cb8ea950d033a408a93fb1d0a264640ddeb8e20f0023a40fb96
395d96264640fd664c1cd4003a4060e5d0225b264640b30a8563f1f93940c77020248b22464034f3
cefcc5003a40fdf9b66029274640fc74971f13ff3940eb8d5a617a28464004c40ebe8bf9394066a3
737e0a2a4640449149a187f839404f07b29eda294640368fadc2c1f839401de4f560d22846401281
d48107f439403244f98296284640637b174a81f33940bd25e4831e2946405f2a919fe8f4394076e2
72bc822946404dd4bc3ecef43940d14659bf192a4640d65d34bff5f139403eec8502362a464012c3
691c45f0394058703fe0012b46401a715c2107e939405b5b785eaa2a46403c35f2d47ee539409762
f206182c4640a9bf48c384e339405e6743fe192c4640ca558737c6e43940ce0017644b2d46400bee
f19df3e339409a081b9ede2d4640fb9bab41f3df394093b4c6a0932d46402102f8020edd3940e563
7781122e4640f126a9a7eade3940aadec5fbf12e46405f763980d9db3940fab48afe50304640ae47
cbdcd7db3940a73e90bcf331464080f9bf7ee3d9394014f1811d7f314640726fd97cb7d33940e63f
a4df3e3246408a90a4ff40d339408e812040063346401b45c07c0dd539409f1d705db1334640591c
29b6ddde39408aabcabeab3546407cb58d9a0add3940d567b27f1e364640faea94a2f0dd39406267
0a9d573646408d60cd5c3bd939408bc058df403746401b6899a0e1dd39403a62d861cc3846403def
b0e485dc39407aabae43b53b46402d058e5fd3d9394039ecbe63f83b4640d8a5a1a1dadc3940a77a
32ff683d46405003610212df39407a09f9a0e73c4640d690138145de39400da5f6225a3b46407100
e72043df39409f20b1ddbd3a4640bdb266bf49e1394060c77f81a03a46408cc5cac39de13940677c
5f5c2a3b4640de363d83e1e6394048747ade0d3a464057636424d6ea39402829b000263d46406a82
925dc4e53940747b4963343e4640fb26a83c15ec3940aea70183244246408259fc016ae439401075
1f805445464007bd923f73e039407a39ecbee3464640304438c192e339403695456197474640ca6a
a400ace03940575bb1bf6c484640c64fcd40c0e239400c9d82fcec4846409e925dc4d2e039405098
dbbd5c494640e63c4dc175e339400da5f6225a4a46405b632bc313e83940520ababd24484640634b
ea5fedee3940ec8497e0544946409456d7fc53fe3940411af9bc624e46402505002258fb39405ad9
3ee4ad504640e3df51befbf73940a68922a4ee504640dcdd5e2dd2f03940ae9e93deb75246401ef6
2c639ef139406ceee87f3954464006d31ade07f639406d54a703d95346409cf67efed6f8394086ff
7403855446405643cc800ef7394046da71c36f554640b652639d85f73940a73b4f3ce75546400b7b
c44373fb394006d9b27cdd5446408d7bdd7de9fb39402e1d739e31554640a4e6069ed6f9394093c9
a99de1554640f12a55fda5fb39405098dbbd5c5646401d9837e224f9394076df313c76564640409e
4720b9f539404d56f0db905746409071207fc4fd39401eff058280584640d1abebab06fd39402030
babcb9594640ed6877a3eaf93940d1066003a259464048de23f664f839402984b9dd4b5c4640aeb6
4ce19cf939400e87a5819f5d4640c2a8ff07b3f739403b8c497faf5d46407b1a8b016df73940f3cc
cb61775e46405d39d62027f63940014eefe27d5e46409bc39f3c87f6394041ffe89bb45f46400b0e
8a41bbf7394044fb58c1ef5f4640bb9dd89eb4f839409f3e027f785e4640fd9fadde3cfd39403b7a
a7026e5f4640fb05a5c39e003a40e2218c9f465e46407e8d0ea37b063a409947fe60605f4640d129
2389b90c3a4062821abe055f464041857e01180d3a40da37f7570f5e4640dadfc380800d3a404982
7005945e46403750caff3f103a404913ef00cf5e4640ee240c5e4f103a407b57e883e55f4640a4cb
f67c28133a4073d9e89c1f604640029af27c61143a402148895d5b5f46402d058e5fd3153a4052f2
ea1c835f4640a55247228f163a40228d0a9cec5e4640561234c1cb183a40b29e5a7df55e4640b70f
d484481a3a409e996038575e46402b6503441c173a4014967840596146403a79ecc26a1a3a40b66a
d784346246409cdeaf5d351d3a408196ae609b604640b364e99ed01f3a40c5ef517f3d604640698e
965ef6233a40b3a487a155604640b98c857c2b243a40969867252d614640ac94f9a2982e3a409a2f
dae305604640fd698d9ce02d3a40e08e1b7eb760464066a5a4e25a2f3a4062821abe05614640eb6c
b2a1f6313a409a3e3be03a6046402235d7c45d3d3a40e2f198814a5f4640368c6c42b53d3a40e700
c11cbd5e4640ce309f07d23f3a40b05f5b3f7d5e4640312eb022fe413a401781b1be015f46402f1c
638275423a40c9acdee1f66046403b3a0900e9453a40d6d0b183ca604640745e4df96b463a40b47d
737f75624640813ca5deae4d3a40321d3a3d6f6246405d16fdfc52593a401a69a9bc9d5d464066c6
360422603a40d252793b425c4640a23eb37ff95c3a40439259bd435a4640d5b727a3255f3a40163c
308070594640addd60037d5c3a401bf33ae210584640c4622f6f69593a40a5d76663a557464071c4
44decd573a40ae8383bd0958464024a188a0c5543a403259dc7fe4564640ff9b72e030593a40a48c
b800b4554640db1a6c45f65d3a40fa997adda252464028dab0016c5a3a406edfa3fefa5146403602
db1c425b3a40b9196ec067514640344d3338a55e3a402920ed7f00524640cb15399f95603a407fc0
0303884f464082209b3f01663a404380b74002504640e603ecfe0c673a40dd88d2de604e4640de1e
6ee23f5f3a401bab7823734b4640b4c51fa056613a40af9811de9e4a46406b7f51ddb75c3a40ff59
f3e3af484640d12a8e5e685e3a409eb646046348464042284a9ddb5c3a401106499fd6474640fb7f
304b965b3a40da91ea3b3f48464043c2e160ca563a405e6743fe19464640db88111d5d593a406f4b
e482b3464640494e8127885e3a4082902c6082444640b60d8d823b5c3a40f4c308e15144464016d8
4d84685d3a40015130638a4346406e312999f7663a4093d217424e44464092ae83de76673a40295c
8fc275434640b213b93bc6653a4012691b7f22424640b1cae08332673a40d36a48dce3414640b1a4
c6df51693a405340daff8043464097562d44e26a3a409d9e7763c1424640
)

Original comment by andrixnet on 5 Jun 2014 at 4:29

GoogleCodeExporter commented 9 years ago 
Issue reported to MariaDB here: https://mariadb.atlassian.net/browse/MDEV-6310

Original comment by andrixnet on 11 Jun 2014 at 5:30

GoogleCodeExporter commented 9 years ago 
According to the resolution of this bug in MariaDB: 
https://mariadb.atlassian.net/browse/MDEV-6310
the original query is wrong. 

LINESTRING defines a 1-dimensional object. (does not have area).
POLYGON defines a 2-dimensional object. (has area).

SELECT WITHIN( GEOMFROMTEXT('POINT(1 1)'), GEOMFROMTEXT('LINESTRING(0 0, 0 2, 2 
2, 2 0, 0 0)') );

This defines a set of lines and within will only return "1" for a point on a 
line. 
LINESTRING does not define the area bounded by it. 

Correct queries: 
Variant 1: 
- use MBRWITHIN() which tests specifically for polygon within a polygon and 
converts the given shapes to polygons.
(Minimum Bounding Rectangle)

SELECT MBRWITHIN( GEOMFROMTEXT('POINT(1 1)'), GEOMFROMTEXT('LINESTRING(0 0, 0 
2, 2 2, 2 0, 0 0)') );

- Variant 2: 
- use POLYGON construct to define the shape, then WITHIN() function returns the 
expected result.

SELECT WITHIN( GEOMFROMTEXT('POINT(1 1)'), GEOMFROMTEXT('POLYGON((0 0, 0 2, 2 
2, 2 0, 0 0))') );

Both seem to work on MariaDB-5.5.38 and MySQL-5.1.56. 

OCRO: MariaDB-5.5.38
OCNL: MySQL-5.5.30
OCPL: ???

Both test query variants have been tested on OCRO and OCNL. 
Please evaluate the impact on OCPL and if ok, please update the queries that do 
region detection.

Original comment by andrixnet on 4 Aug 2014 at 4:04

GoogleCodeExporter commented 9 years ago 
Suggested solution by MariaDB developers is MBRWITHIN() as newer versions have 
refined the geometry extensions as described above. 

Possible affected files: 
chat\lib\class\CustomAJAXChat.php
GetRegions.php
region.php
util.sec\cache_locations\cache_location.class.php
util.sec\cron\modules\cache_location.class.php
util.sec\cron\modules\cache_npa_areas.class.php

Original comment by andrixnet on 4 Aug 2014 at 4:13

GoogleCodeExporter commented 9 years ago 
@LZA: please verify that MBRWITHIN() works properly on the mysql version that 
is installed on OCPL, such that we can change the queries to work properly over 
all database server versions. 

Thank you

Original comment by andrixnet on 11 Oct 2014 at 11:03

GoogleCodeExporter commented 9 years ago

Original comment by andrixnet on 11 Oct 2014 at 11:04

GoogleCodeExporter commented 9 years ago 
OCPL database version: 5.1.58-1~dotdeb.0-log

SELECT WITHIN( GEOMFROMTEXT('POINT(1 1)'), GEOMFROMTEXT('LINESTRING(0 0, 0 2, 2 
2, 2 0, 0 0)') );
result --> 1

SELECT MBRWITHIN( GEOMFROMTEXT('POINT(1 1)'), GEOMFROMTEXT('LINESTRING(0 0, 0 
2, 2 2, 2 0, 0 0)') );
result --> 1

SELECT WITHIN( GEOMFROMTEXT( 'POINT(1 1)' ) , GEOMFROMTEXT( 'POLYGON((0 0, 0 2, 
2 2, 2 0, 0 0))' ) ) ;
result --> 1

I hope you know what you are doing :)

Original comment by boguslaw...@gmail.com on 3 Nov 2014 at 11:37

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Try to replace MariaDb witch mysql. Maria db is not known database, and seems 
is not compatibile with mysql.

I had never problem with this issue on *ANY* version of mysql database. When I 
back home, I try to install last version (5.7) of mysql and check if it is 
working. But on version 5.6.20 works perfectly.

We cannot change mysql queries because your database is not compatibile with 
mysql.

Original comment by wloczynutka on 4 Nov 2014 at 8:27

GoogleCodeExporter commented 9 years ago 
MariaDB is a fork of MySQL. Its lead developer is Michael "Monty" Widenius, the 
founder of MySQL. 
Please read here about it and it's reasons for being: 
http://en.wikipedia.org/wiki/MariaDB

On the other hand I would argue that MariaDB behaves correctly and MySQL 
behaves wrong. 
Let me explain:
LINESTRING defines a set of lines. This has edges, vertices, length. It is not 
required to be a closed shape.
POLYGON defines a polygon. It is a simple and closed LINESTRING (linering) with 
the additional property of having an area. 

Doing comparison with MBR (Minimum Bounding Rectangle) works because this 
automatically creates bounding rectangles (polygons) for each geometry. However 
this has the undesirable effect of matching points outside the actual shape, 
but inside the minimum rectangle. 
A better choice could be ST_WITHIN, which compares actual shapes. Further tests 
are in order. 

To see the difference, try this on both MySQL and MariaBD, also draw it on a 
piece of paper. 
===========================================

set @g0 = GEOMFROMTEXT('LINESTRING(0 0, 3 0, 3 1, 2 1, 2 2, 0 2, 0 0)');
set @p0 = POLYGONFROMTEXT('POLYGON((0 0, 3 0, 3 1, 2 1, 2 2, 0 2, 0 0))');

set @g1 = GEOMFROMTEXT('POINT(0 2)'); -- on an edge
set @g2 = GEOMFROMTEXT('POINT(1 1)'); -- inside the shape
set @g3 = GEOMFROMTEXT('POINT(2.5 1.5)'); -- outside of shape, inside minimum 
bounding rectangle

select within(@g1, @g0); -- on edge
select within(@g2, @g0); -- inside shape
select within(@g3, @g0); -- outside shape, inside MBR

select MBRwithin(@g1, @g0); -- on edge
select MBRwithin(@g2, @g0); -- inside shape
select MBRwithin(@g3, @g0); -- outside shape, inside MBR

select ST_within(@g1, @g0); -- on edge
select ST_within(@g2, @g0); -- inside shape
select ST_within(@g3, @g0); -- outside shape, inside MBR

-- ******************************

select within(@g1, @p0); -- on edge
select within(@g2, @p0); -- inside shape
select within(@g3, @p0); -- outside shape, inside MBR

select MBRwithin(@g1, @p0); -- on edge
select MBRwithin(@g2, @p0); -- inside shape
select MBRwithin(@g3, @p0); -- outside shape, inside MBR

select ST_within(@g1, @p0); -- on edge
select ST_within(@g2, @p0); -- inside shape
select ST_within(@g3, @p0); -- outside shape, inside MBR

=================================================================
Note that ST_WITHIN() was not yet available in MySQL-5.1x

Results: 
-----------

MySQL-5.1.56: 
      all WITHIN and MBRWITHIN tests return 1 for both @g0 and @p0.

MariaDB-5.5.37: 
select within(@g1, @g0); -- on edge
1
select within(@g2, @g0); -- inside shape
0
select within(@g3, @g0); -- outside shape, inside MBR
0

select MBRwithin(@g1, @g0); -- on edge
1
select MBRwithin(@g2, @g0); -- inside shape
1
select MBRwithin(@g3, @g0); -- outside shape, inside MBR
1

select ST_within(@g1, @g0); -- on edge
1
select ST_within(@g2, @g0); -- inside shape
1
select ST_within(@g3, @g0); -- outside shape, inside MBR
1

*********************************************
select within(@g1, @p0); -- on edge
1
select within(@g2, @p0); -- inside shape
1
select within(@g3, @p0); -- outside shape, inside MBR
0

select MBRwithin(@g1, @p0); -- on edge
1
select MBRwithin(@g2, @p0); -- inside shape
1
select MBRwithin(@g3, @p0); -- outside shape, inside MBR
1

select ST_within(@g1, @p0); -- on edge
1
select ST_within(@g2, @p0); -- inside shape
1
select ST_within(@g3, @p0); -- outside shape, inside MBR
0

==============================================================

Conclusion:
-----------

Geometrically correct results are obtainable using WITHIN() and ST_WITHIN() on 
POLYGON data.

The difference between MySQL and MariaDB refers to results which are 
geometrically insufficient.

A major problem is the fact that NUTS_LAYER contains LINESTRING data and this 
would have to be converted. 

Even the tools that are supposed to import NUTS data (according to 
util.sec/import_nuts/README.txt) will still load LINESTRING data from the GIS 
file using an external tool (shp2mysql.pl). 
And the links provided in that README file are no longer available...

Original comment by andrixnet on 4 Nov 2014 at 1:10

GoogleCodeExporter commented 9 years ago 
And this is source of problem. MariaDB is fork of mysql, while ocpl code 
require mysql engine.

Original comment by wloczynutka on 4 Nov 2014 at 1:18

GoogleCodeExporter commented 9 years ago 
The core of the problem is not the flavour of MySQL or MariaDB, but rather both 
the queries and the dataset.

+--+
|  | *2
|  +--+
| *1  |
+-----+

Using within (mysql) mbrwithin (mysql and mariadb) yields TRUE for both 1 and 2 
(see above drawing) while geometrically this is not correct.

While there is a difference in the behaviour of MySQL and MariaDB regarding 
within() function and LINESTRING or POLYGON data, but it turns out this is not 
the main issue. 

Possible fixes: 
1) Change all queries to use MBRWITHIN() instead of WITHIN(). 
   No change to the data.
   No changes to database server software.

Results:
- apparently will work on both MySQL and MariaDB (all nodes)
- the results will not always be geometrically correct.

2) No changes to the code. 
   No change to the data.
   Replace mariadb with mysql on OCRO. 

Results: 
- apparently will work on OCRO as well
- the results will not always be geometrically correct.

3) Change to code where explicit there are LINESTRING definitions, adapt where 
POLYGON references are required, but continue to use WITHIN() function.
   Change the shape data in NUTS_LAYER table to define POLYGON instead of LINESTRING, adjust queries in the code if/where linestring is specified, 
continue to use WITHIN() function
   No changes to database server software.

Results:
- will work on all nodes
- results will be geometrically correct.

Original comment by andrixnet on 4 Nov 2014 at 1:34

GoogleCodeExporter commented 9 years ago 
I am currently studying a method of converting the nuts_layer shape data from 
LINESTRING to POLYGON. 

Note: the links refered in /util.sec/import_nuts/README.txt are no longer 
valid. 
The shp2mysql.pl script I managed to find elsewhere, but the source for GIS 
data, I could not find.

Original comment by andrixnet on 4 Nov 2014 at 5:23

GoogleCodeExporter commented 9 years ago 
The problem is even more complex.
All the discussion about mysql vs mariadb, within() and mbrwithin() are valid. 

Furthermore, the original implementation awknowledged the fact that database 
query returned results for minimum bounding rectangle match and implemented a 
separate polygon matching algorithm gis::ptInLineRing 
(/lib/gis/giss.class.php). 
This routine does the actual decision if a point is inside an arbitrary shape. 

Conclusion: shape comparison is done in PHP, and only rough rectangle matching 
is done at database level. 
This is what masked the problems I have found and described above. 

Based on this find and the differences between MySQL and MariaDB, replacing 
WITHIN() with MBRWITHIN() should be sufficient.

Original comment by andrixnet on 7 Nov 2014 at 11:03

GoogleCodeExporter commented 9 years ago 
GetRegions.php line 55:

-        $tmpqery = "SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM 
`nuts_layer` WHERE WITHIN(GeomFromText('POINT($lon  $lat)'), `shape`) ORDER BY 
`level` DESC";

+        $tmpqery = "SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM 
`nuts_layer` WHERE MBRWITHIN(GeomFromText('POINT($lon  $lat)'), `shape`) ORDER 
BY `level` DESC";

Tested and working.

Original comment by andrixnet on 7 Nov 2014 at 11:09

GoogleCodeExporter commented 9 years ago 
Given the complexity of this part of code and the fact that almost the same 
code found in GetRegions.phh is used in several other files, one of them which 
contains some hardcoded references to `pl`, I am studying if and how this code 
can be moved to a library function.

Original comment by andrixnet on 7 Nov 2014 at 11:13