SlowCycle.de » Geo Koordinaten Umkreis Suche

Geo Koordinaten Umkreis Suche

Geo Koordinaten Umkreis Suche unter Verwendung von Spatial (R-Tree) Indizes
und der Mysql Geometry Funktionen.

Für die Suche wird eine Tabelle mit folgender oder ähnlicher Struktur benötigt:
Dump incl. Testdaten (geo.sql)


CREATE TABLE `geo` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `coordinate` geometry NOT NULL,
   `zip` int(5) unsigned zerofill NOT NULL,
   PRIMARY KEY  (`id`),
   SPATIAL KEY `coordinate` (`coordinate`),
   KEY `zip` (`zip`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Die folgende SQL Function bekommt die Geographische Länge und Breite, den Umkreisradius in KM und die Anzahl
der Ecken des Umkreises (Genauigkeit) übergeben. Als Rückgabe liefert die Funktion ein MySQL GEOMETRY Object
welches auf die Tabelle `geo` angewendet werden kann.

DROP FUNCTION IF EXISTS GEOPOLYGON;
DELIMITER $$

CREATE FUNCTION GEOPOLYGON(lat DOUBLE,lon DOUBLE,radius MEDIUMINT,edges TINYINT) RETURNS GEOMETRY
DETERMINISTIC
BEGIN

DECLARE i MEDIUMINT DEFAULT 1;
DECLARE a,b,c DOUBLE DEFAULT NULL;
DECLARE result VARCHAR (2048) DEFAULT NULL;

IF (edges<3 || edges>360 || radius>500) THEN
    RETURN NULL;
END IF;

SET result=CONCAT(lat+radius/111.12,' ',lon,',');

WHILE (i<edges)
    SET c=RADIANS(360/edges*i);
    SET a=lat+COS(c)*radius/111.12;
    SET b=lon+SIN(c)*radius/(COS(RADIANS(lat + COS(c)*radius/111.12/111.12))*111.12);
    SET result=CONCAT(result,a,' ',b, ',');
    SET i=i+1;
END WHILE;

RETURN GEOMFROMTEXT(CONCAT('POLYGON((',result,lat+radius/111.12,' ',lon,'))'));
END;

$$
DELIMITER ;

Der Query in dem die Function GEOPOLYGON() verwendet wird, liefert
als Ergebnis eine Liste mit geo.id und geo.zip.
Im Funktionsaufruf kann Umkreis und in der WHERE Bedingung des sub- selects die Postleitzahl
des Mittelpunktes vom Umkreis definiert werden.


SELECT
	id,	zip
FROM geo
WHERE CONTAINS
(
	(
	SELECT
		GEOPOLYGON(X(coordinate),Y(coordinate), 5, 12)
	FROM geo
	WHERE zip = 06116 LIMIT 1
	), coordinate
);
+-------+-------+
| id    | zip   |
+-------+-------+
| 18617 | 06188 |
| 10723 | 06184 |
|  8090 | 06124 |
| 11434 | 06116 |
| 11435 | 06128 |
| 11436 | 06130 |
| 15246 | 06122 |
| 15991 | 06118 |
| 15992 | 06126 |
| 17664 | 06120 |
| 17665 | 06110 |
| 19574 | 06114 |
| 19947 | 06112 |
| 15654 | 06188 |
|  8092 | 06108 |
| 20785 | 06132 |
+-------+-------+
16 rows in set (0.00 sec)

MySQL implementiert die Funktion CONTAINS() unter anderen zurzeit noch nicht entsprechend der Spezifikation. Die, die implementiert werden, geben dasselbe Ergebnis wie die entsprechenden MBR-basierten Funktionen zurück.

MBR steht für “minimum bounding rectangle” (kleinstes umschließendes Rechteck).
Das berechnete Polygon welches die GEOPOLYGON Funktion zurück liefert ist also eigentlich nur das kleinstmögliche Rechteck, zumindest solange bis MySQL die Funktionen entsprechend implementiert hat, was gerade so um das Polygon herum passt.

2 Kommentare

  • 1. Carsten schrieb am 9th Dezember 2008 um 09:20 :

    Hallo,
    eben wollte deine Umkreissuche nachempfinden (bin eben an einer ähnlichen Geschichte), aber bei “CREATE FUNCTION GEOPOLYGON” erhalte ich folgende Fehlermeldung, die ich mir aber nicht erklären kann:
    ERROR 1064 (42000): You have an error in … use near ‘SET c=RADIANS(360/edges*i);
    SET a=lat+(COS(c)*radius/111.12);
    SET b=lon+’ at line 12

    Gruß Carsten

  • 2. Ronny schrieb am 9th Dezember 2008 um 10:33 :

    Ja sorry,

    da hat ein “DO” hinter dem WHILE gefehlt.

    Gruß Ronny

Gib deinen Kommentar ab

XHTML: Du kannst folgende Tags für deinen Kommentar nutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strong>