Autor Zpráva
Petr Ká
Profil
#1 · Zasláno: 28. 4. 2017, 09:23:22 · Upravil/a: Petr Ká o okamžik později
Odpovědět Citovat
Ahoj,

řeším problém, kde potřebuji zjistit, zda bod leží v polygonu, mám k dispozici verzi MySQL 5.5 (takže funkce ST_Contains atd nemohu použít).
Spoustu hodin to nějakým způsobem řeším přes Contains, ale vrací mi NULL. Můžete prosím mrknout, co dělám špatně? 

SELECT
          Contains(
              GEOMFROMTEXT('POLYGON(49.761682 16.453246, 49.769536 16.493376, 49.749474 16.489616, 49.747345 16.451620)'),
              GEOMFROMTEXT(
                  CONCAT("POINT(",lat," ",lon,")")
              )
          )
Každý z GEOMFROMTEXT normálně vrátí geometrii, ale ten contain je prostě NULL...

P.S. je to jen výtažek dotazu v lat a lon jsou souřadnice

Děkuji
Kajman
Profil
#2 · Zasláno: 28. 4. 2017, 09:47:24
Odpovědět Citovat
Problém bude v polygonu, není to validní zápis. Konec musí být v začátkuku (tedy zopakovat uvodní souřadnice i na konci) a jsou tam dvojité závorky. Zkuste 

'POLYGON((49.761682 16.453246, 49.769536 16.493376, 49.749474 16.489616, 49.747345 16.451620, 49.761682 16.453246))'
Petr Ká
Profil
#3 · Zasláno: 28. 4. 2017, 09:55:25
Odpovědět Citovat
Kajman:
tisíceré děkuji, "It works!"!
Jen nechápu, proč zdvojené závorky? Jaký to má smysl?
Kajman
Profil
#4 · Zasláno: 28. 4. 2017, 10:02:37
Odpovědět Citovat
Vnitřní závorkou se uzavírají jednotlivé okruhy. Těch tam může být víc. Tady je jen jeden, tak to vypadá zvojeně.
Petr Ká
Profil
#5 · Zasláno: 28. 4. 2017, 10:08:52
Odpovědět Citovat
Kajman:
Chápu tedy dobře, že když budu mít více polygonů (navzájem se protínajících atd) jako je např. ZDE <= což je problém, který aktuálně řeším, tak je mohu zadat jen jako další "pole"? 

POLYGON(
    (x1 y1, x2 y2, x3 y3, x1 y1),
    (x1 y1, x2 y2, x3 y3, x4 y4, x5 y5, x1 y1),
    (x1 y1, x2 y2, x3 y3, x4 y4, x1 y1)
)
Kajman
Profil
#6 · Zasláno: 28. 4. 2017, 10:15:11 · Upravil/a: Kajman o 17 minut později
Odpovědět Citovat
Tak jsem to spletl, podle návodu
dev.mysql.com/doc/refman/5.5/en/gis-data-formats.html#gis-wkt-format
je v polygonu druhá závorka k vyříznutí oblasti z první závorky, kterou nechcete v polygonu počítat.

Na spojení polygonů je pak multipolygon. 


MULTIPOLYGON(
    ((x1 y1, x2 y2, x3 y3, x1 y1)),
    ((x1 y1, x2 y2, x3 y3, x4 y4, x5 y5, x1 y1)),
    ((x1 y1, x2 y2, x3 y3, x4 y4, x1 y1))
)

Edit: Ale vyřezávání díry do polygonu při porovnání asi moc nefunguje. Tohle vrací 1 
SELECT Contains(
         Geomfromtext('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))'),
         Geomfromtext('POINT(6 6)')
       ) obsahuje
FROM   DUAL
Petr Ká
Profil
#7 · Zasláno: 28. 4. 2017, 11:16:24 · Upravil/a: Petr Ká o minutu později
Odpovědět Citovat
Kajman:
Akorát teda, co to testuji, tak to hledání je dosti pomalé, když to použiju na 2.5 mega adres ve WHERE kluzoli. Je na to nějaký "hack"?
Pravdou je, že lat a lon mám v zvlášť sloupcích. Indexy jsou samozřejmostí.
Kajman
Profil
#8 · Zasláno: 28. 4. 2017, 11:19:55
Odpovědět Citovat
Zkuste přidat navíc sloupec s pointem a udělat nad ním index.
MySQL: Mapa - lepší 2 sloupce X a Y nebo jeden typu POINT?
Petr Ká
Profil
#9 · Zasláno: 28. 4. 2017, 11:43:51 · Upravil/a: Petr Ká o 30 minut později
Odpovědět Citovat
Kajman:
Jo, to celkem pomohlo... Z 6 až 15 sekund jsem se dostal stabilně na cca 4 sec., ale to je pořád dlouho...

Aktuální SQL vypadá tedy teď takto: 
SELECT
    data.*,
    ROUND( 6371 * acos( cos( radians(49.757596) )
                  * cos( radians( data.lat ) )
                  * cos( radians( data.lon ) - radians(16.469670) )
                  + sin( radians(49.757596) )
                  * sin( radians( data.lat ) ) ) , 3) AS vzdalenost
FROM (
        SELECT
            id,
            (lat/1000000) AS lat,
            (lon/1000000) AS lon,
            CONCAT(IF(nazev_ulice="",nazev_casti_obce,nazev_ulice)," ",cislo_domovni,IF(cislo_orientacni<>"",CONCAT("/",cislo_orientacni),"")) AS adresa,
            nazev_obce,
            psc
        FROM `cr_data`
        WHERE            
            (
                nazev_casti_obce LIKE "%male namesti%" OR
                nazev_ulice LIKE "%male namesti%"
            )
            AND
            Contains(
                  GEOMFROMTEXT('MULTIPOLYGON(((49.761682 16.453246, 49.769536 16.493376, 49.749474 16.489616, 49.747345 16.451620, 49.761682 16.453246)))'),
                  gps_point
            ) = 1
            
        LIMIT 15
    ) data
ORDER BY vzdalenost ASC
Nemáte ještě nějaké tipy?


Kajman:
Tak jsem se dostal na 3 sekundy
Stačilo přes WHERE vyfiltrovat adresy pomocí GEO až pak přes HAVING hledat dle názvu - nechápu proč, ale něco to ušetřilo
Kajman
Profil
#10 · Zasláno: 28. 4. 2017, 12:33:51
Odpovědět Citovat
Jaký je explain?
Petr Ká
Profil
#11 · Zasláno: 28. 4. 2017, 12:41:16 · Upravil/a: Petr Ká o 5 minut později
Odpovědět Citovat 
EXPLAIN SELECT
    data.*,
    ROUND( 6371 * acos( cos( radians(49.757596) )
                                  * cos( radians( data.lat/1000000 ) )
                                  * cos( radians( data.lon/1000000 ) - radians(16.469670) )
                                  + sin( radians(49.757596) )
                                  * sin( radians( data.lat/1000000 ) ) ) , 3) AS vzdalenost
FROM (
    SELECT
        id,
        CONCAT(IF(nazev_ulice="",nazev_casti_obce,nazev_ulice)," ",cislo_domovni,IF(cislo_orientacni<>"",CONCAT("/",cislo_orientacni),"")) AS adresa,
        CONCAT(CONCAT(IF(nazev_ulice="",nazev_casti_obce,nazev_ulice)," ",cislo_domovni,IF(cislo_orientacni<>"",CONCAT("/",cislo_orientacni),"")), ", ",nazev_obce," ",psc) AS cela_adresa,
        nazev_obce,
        nazev_casti_obce,
        nazev_ulice,
        psc,
        lat,
        lon
    FROM `cr_data`
    WHERE
        Contains(
              GEOMFROMTEXT('POLYGON((49.799238 16.403944, 49.825491 16.437074, 49.837228 16.501791, 49.763325 16.568567, 49.732267 16.535265, 49.733048 16.386575, 49.799238 16.403944))'),
              gps_point
        ) = 1
    HAVING
        (
            nazev_casti_obce LIKE "%male names%" OR
            nazev_ulice LIKE "%male names%"
        )
    LIMIT 15
) data
ORDER BY vzdalenost ASC

1    PRIMARY    <derived2>    ALL                    8    Using filesort    
2    DERIVED    cr_data    ALL                    2301329    Using where



EDIT:
Je zvětšený polygon a má víc bodů (reálně to tak bude - rozvozové zóny)
- ten LIKE tam taky nebude, prave delam druhou tabulku na FULLTEXTOVE vyhledavani, tak tam bude WHERE id IN (_FULLTEXT_HLEDANI_)
snad to tomu pomůže ještě
Kajman
Profil
#12 · Zasláno: 28. 4. 2017, 12:50:54
Odpovědět Citovat
A udělal jste na tom sloupci gps_point spatial index? Podle explainu to nevypadá.
Petr Ká
Profil
#13 · Zasláno: 28. 4. 2017, 12:52:45
Odpovědět Citovat
Kajman:
Je tam klasický index.
Když chci vytvořit SPATIAL tak: 
#1464 - The used table type doesn't support SPATIAL indexes
tabulka je InnoDB
pcmanik
Profil
#14 · Zasláno: 28. 4. 2017, 12:56:19
Odpovědět Citovat
Petr Ká:
Spatial index nad InnoDB tabuľkami ide až od verzie 5.7.5
Kajman
Profil
#15 · Zasláno: 28. 4. 2017, 12:56:43
Odpovědět Citovat
Kombinace innodb a spatial indexů je až později. Pokud opravdu nelze povýšit verzi. Udělejte si pomocnou myisam tabulku, kde bude jen id a gps_point.
Petr Ká
Profil
#16 · Zasláno: 28. 4. 2017, 12:56:53 · Upravil/a: Petr Ká o 34 minut později
Odpovědět Citovat
pcmanik:
Právě, taky to čtu.. mám k dispozici pouze 5.5


Kajman:
Ok, vyzkouším


Kajman:
MILUJU VAS! Jsem na 0.1 sec!

Kam se posílaj donaty? "You saved my day!"

BTW řešení pro ostatní:
Udelal jsem si tabulku, kde mám už celou zformátovanou adresu nad ní FULLTEXT index, pak sloupec POINT a nad ním SPATIAL index 
CREATE TABLE `cr_data_search` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `addr_id` int(11) NOT NULL,
 `cela_adresa` varchar(256) NOT NULL,
 `gps_point` point NOT NULL,
 PRIMARY KEY (`id`),
 KEY `addr_id` (`addr_id`),
 SPATIAL KEY `gps_point` (`gps_point`),
 FULLTEXT KEY `cela_adresa` (`cela_adresa`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf

Do ní jsem si naládoval data
POZOR: Nelze vytvořit SPATIAL index, když je tabulka prazdna, nebo kdyz sloupec POINT je prázdný!
Index tvořit až po naplnění daty

No a pak už je to jednoduché :-): 
SELECT
        id,
        CONCAT(IF(nazev_ulice="",nazev_casti_obce,nazev_ulice)," ",cislo_domovni,IF(cislo_orientacni<>"",CONCAT("/",cislo_orientacni),"")) AS adresa,
        CONCAT(CONCAT(IF(nazev_ulice="",nazev_casti_obce,nazev_ulice)," ",cislo_domovni,IF(cislo_orientacni<>"",CONCAT("/",cislo_orientacni),"")), ", ",nazev_obce," ",psc) AS cela_adresa,
        nazev_obce,
        nazev_casti_obce,
        nazev_ulice,
        psc,
        lat,
        lon,
        ROUND( 6371 * acos( cos( radians(49.757596) )
                                  * cos( radians( lat/1000000 ) )
                                  * cos( radians( lon/1000000 ) - radians(16.469670) )
                                  + sin( radians(49.757596) )
                                  * sin( radians( lat/1000000 ) ) ) , 3) AS vzdalenost
    FROM `cr_data` data
    RIGHT JOIN (
            SELECT
                addr_id,
                MATCH(cela_adresa) AGAINST ('5 kvetna 8') AS shoda
            FROM `cr_data_search`
            WHERE
                Contains(
                    GEOMFROMTEXT('MULTIPOLYGON(((49.799238 16.403944, 49.825491 16.437074, 49.837228 16.501791, 49.763325 16.568567, 49.732267 16.535265, 49.733048 16.386575, 49.799238 16.403944)))'),
                    gps_point
                )=1 AND
                MATCH(cela_adresa) AGAINST ('5 kvetna 8' IN BOOLEAN MODE)
            ORDER BY shoda DESC
        ) filter
        ON filter.addr_id = data.id
ORDER BY vzdalenost ASC
LIMIT 15

EXPLAIN: 
1    PRIMARY    <derived2>    ALL                    16    Using temporary; Using filesort    
1    PRIMARY    data    eq_ref    PRIMARY    PRIMARY    4    filter.addr_id    1        
2    DERIVED    cr_data_search    ALL    cela_adresa    cela_adresa    0        2303388    Using where; Using filesort

Ještě nějaký nápad? :)
Kajman
Profil
#17 · Zasláno: 28. 4. 2017, 14:00:31
Odpovědět Citovat
Petr Ká:
„Ještě nějaký nápad?“

Po přesunu na verzi 5.7 použít jen jednu tabulku a funkci st_distance na výpočet vzdálenosti pro řazení (pokud nejsou souřadnice blízko pólů). Pak by se přesnější vzdálenost na kouli mohla počítat jen pro těch 15 řádků.
Petr Ká
Profil
#18 · Zasláno: 28. 4. 2017, 14:04:36
Odpovědět Citovat
Kajman:
„pokud nejsou souřadnice blízko pólů“
Jde jen o ČR :)

Pokusím zatlačit na dodavatele VMS, aby nám nahodil novější verzi ¨

Každopádně DĚKUJI
Časová prodleva: 7 let

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm:

0