Adott pont vonzáskörzetében való keresés MySQL segítségével

Azt a feladatot kaptam, hogy saját adatbázisból vendéglátó-egységeket tegyek kereshetővé, és a találatokat jelenítsem meg egy térképen. Hogy ne legyen olyan egyszerű a feladat, további kérés volt, hogy Budapesten a szomszédos kerületek találatait is mutassam meg, egyébként pedig 50 km-es körzetben lévőket. Bár a megoldás nem kapcsolódik közvetlenül a Java-hoz, mégis úgy gondoltam talán érdekes lehet. Igyekeztem úgy kivitelezni a dolgot, hogy a MySQL adatbázisból ne kerüljön ki olyan érték, amelyet nem kell megjeleníteni.

Lássuk először az 50 km-es körzet problémáját:
Először is létrehoztam két triggert, amik a vendéglátó-egység beszúrása vagy módosítása után futnak le, és egész egyszerűen annyit tesznek, hogy a beállított longitude és latitude értékből a coordinate mezőben eltárolnak egy POINT objektumot. A POINT beépített MySQL objektum, így értelem-szerűen a mező típusának is az van deklarálva.

DELIMITER //

CREATE TRIGGER RefreshHorecaPointInsert BEFORE INSERT ON `horeca`
FOR EACH ROW
BEGIN
    SET NEW.`coordinate` = POINT(NEW.`longitude`, NEW.`latitude`);
END//

CREATE TRIGGER RefreshHorecaPointUpdate BEFORE UPDATE ON `horeca`
FOR EACH ROW
BEGIN
    SET NEW.`coordinate` = POINT(NEW.`longitude`, NEW.`latitude`);
END//

DELIMITER ;

A következő lépés már igen egyszerű, mivel a MySQL (is) rendelkezik beépített geometriai funkciókkal. Feladatunk annyi, hogy a POLYGONE objektumot felhasználva az első találat koordinátái köré egy tetszőleges sokszöget rajzoljunk. Bár az 50 km-es körzet meghatározásához egy kört kellene rajzolni, de annak bonyolultsága és gyakorlati haszna között fennálló aránytalanság miatt, én a négyszöget választottam.

String polygone = "POLYGON((" + (horeca.getLongitude() - 0.32) + " " + (horeca.getLatitude() - 0.22) + ", " + (horeca.getLongitude() + 0.32) + " " + (horeca.getLatitude() - 0.22) + ", " + (horeca.getLongitude() + 0.32) + " " + (horeca.getLatitude() + 0.22) + ", " + (horeca.getLongitude() - 0.32) + " " + (horeca.getLatitude() + 0.22) + ", " + (horeca.getLongitude() - 0.32) + " " + (horeca.getLatitude() - 0.22) + "))";
String query = "SELECT h FROM Horeca h WHERE MBRContains(PolygonFromText('" + polygone + "'), coordinate)";

Megjegyzem a geometriai funkciók elméletileg 5.0-ás verzió óta léteznek MySQL-ben, elég sok helyen olvastam, hogy hibásan működik, meg, hogy aki ilyet akar használjon PostgreSQL-t. Én is belefutottam olyan kellemetlenségbe, ami miatt frissíteni kellett a szervert 5.1.x-re (a SELECT POINT(12, 12) lefutott, de a UPDATE horeca SET coordinate = POINT(longitude, latitude) már nem).

A Budapest szomszédos kerületeinek kérdés-köre nem egy bonyolult dolog, pusztán azért tárgyalom, hogy teljes legyen a kép. Az adatbázis kímélése érdekében ismét egy triggert írtam, ami budapesti cím beszúrása vagy szerkesztése esetén az irányító számból eltárolja a középső 2 karaktert, mint kerületet. Az egységes kereshetőség érdekében hagytam minden kerületet két karakteresre.

DELIMITER //

CREATE TRIGGER SetDistrict BEFORE INSERT ON `horeca`
FOR EACH ROW
BEGIN
    IF NEW.`postal_code` < 2000 THEN
        SET NEW.`district` = SUBSTR(NEW.`postal_code`, 2, 2);
    END IF;
END;

DELIMITER ;

Ezek után létrehoztam egy táblát, amely a szomszédos kerületek mátrixát tartalmazza.

INSERT INTO `district_neighbors` (`reference`, `neighbor`) VALUES
('01', '02'),('01', '12'),('01', '11'),('01', '05'),('02', '12'),('02', '03'),('02', '05'),('02', '13'),('03', '04'),('03', '13'),('04', '13'),('04', '14'),('04', '15'),('05', '06'),('05', '07'),('05', '13'),('06', '07'),('06', '13'),('06', '14'),('07', '08'),('07', '09'),('07', '14'),('08', '09'),('08', '10'),('08', '14'),('09', '10'),('09', '11'),('09', '19'),('09', '20'),('09', '21'),('10', '14'),('10', '16'),('10', '17'),('10', '18'),('10', '19'),('11', '12'),('11', '21'),('11', '22'),('13', '18'),('13', '19'),('13', '20'),('13', '21'),('14', '15'),('14', '16'),('15', '16'),('17', '18'),('18', '19'),('18', '20'),('18', '23'),('19', '20'),('20', '21'),('20', '23'),('21', '22'),('21', '23');

Az alábbi lekérdezéssel pedig könnyedén kinyerhető adott kerület és a vele szomszédosak az adatbázisból.

String query = "SELECT h FROM Horeca h WHERE district = :district OR district IN (SELECT IF(reference = :district, neighbor, reference) FROM district_neighbors WHERE reference = :district OR neighbor = :district)";