SlowCycle.de

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.