[MySQL] ip to country met openGIS en polygons

[js303]

Ik kwam deze interessante tutorial tegen over het snel kunnen opvragen van countries bij een bepaald IP adres mbv van MySQL's openGIS implementatie.

De 'gebruikelijke' maar trage methode in een notedop: SELECT country FROM ws_ipcountry WHERE ip_from < INET_ATON('217.166.147.53') AND INET_ATON('217.166.147.53') < ip_to

Zijn er mensen die hier ervaringen mee hebben? De SELECT query uit de tutorial geeft een syntax error, en heb de MySQL documentatie erop nageslagen maar ik kom er niet uit:

SQL:

SELECT countrycode 
FROM ws_ipcountry 
WHERE MBRContains(
    ip_poly, 
    PointFromWKB(
        Point(
            INET_ATON('217.166.147.53'), 
            0
        )
    )
)

de fout lijkt hem te zitten in het gedeelte MBRContains(a,b). want de volgende query werkt wel:

SQL:

SELECT PointFromWKB(
        Point(
            INET_ATON('217.166.147.53'),  
            0
        )
    )

ik heb dus een tabel ws_ipcountry, hierin staat een polygoon-kolom van alle IP ranges, volgens de volgende berekening

code:

ip_poly = GEOMFROMWKB(
        POLYGON(
            LINESTRING(
                POINT(ip_from, -1), 
                POINT(ip_to, -1), 
                POINT(ip_to, 1), 
                POINT(ip_from, 1), 
                POINT(ip_from, -1) 
            )
        )

zaakje draait op MySQL 4.1, waarbij ik de tabel op MyISAM heb staan aangezien 4.1 alleen openGIS voor dit type opslag support.

[ Voor 7% gewijzigd door js303 op 27-11-2007 13:47 ]

[js303]

Moeilijk verhaal of niet interessant?

[Japius]

Ik maak zelf gebruik van de GeoLite Country van MaxMind. Vincent Delau (?) heeft een eenvoudige copy-paste tutorial geschreven die voor mij prima werkt.

Het enige waar ik niet zeker over ben, is of alle landen wel kloppen. Daarnaast wil ik eigenlijk ook per plaats segmenteren, maar ik kan nergens een mooie free database vinden.

In ieder geval: in plaats van

code:

SELECT country FROM ws_ipcountry WHERE ip_from < INET_ATON('217.166.147.53') AND INET_ATON('217.166.147.53') < ip_to

gebruik ik

code:

SELECT country FROM my_table WHERE INET_ATON('my_ip') BETWEEN start AND end;

[js303]

Hoi, ook de BETWEEN maakt weinig uit qua performance, ik heb dat deze ook al geprobeerd. ik maak inderdaad ook gebruik van maxmind's geolite csv bestand.

Maar ik ga die tutorial wel even uitproberen, hoewel op het eerste gezicht de tabelstructuur en select query redelijk overeenkomt.

[ Voor 18% gewijzigd door js303 op 01-12-2007 15:27 ]

[Voutloos]

Between zal niet uitmaken, de optimizer vind beide vormen equivalent. Toch kan de query met gemak beter, al was het maar door een triviale LIMIT 1 toe te voegen.

[js303]

@ voutloos: ik weet niet op welke query je precies doelt, maar punt is dat ik juist lijsten wil opvragen van meerdere ip's / bezoekers en hun country (met een LIMIT van bijv. 0,20), en daar gaan zowel de BETWEEN en > methoden de mist in qua performance. dus ik wil juist de openGIS variant aan de praat krijgen waar ik nog steeds niet uit ben

[ Voor 4% gewijzigd door js303 op 07-12-2007 18:43 ]