SQL Koordináták Geo keresgélés

Adatbázis: SQL, XML DB

Játszásiból elkezdtem sql táblában tárolt koordinátákkal szórakozni.

Kitalálmányoztam, hogy amit próbálnék megoldani, azt biztosan lehetséges elegánsabban is megoldani.
Feladvány nagyon egyszerűnek látszik, de tisztán sql nem tudtam megoldani.
Tábla tartalmaz lat lon párokat, amik közül ki kell szűrni azt a pontot, ahol adott távolságon belül a legtöbb pont található.

Remélem emészthető a megfogalmazás, próbáltam nagyon egyszerűsíteni.

Addig eljutottam, hogy ezt "erőből" scripttel meg tudom csinálni.
Ez nem volt túl nagy bonyolultságú feladvány.
Amire már jó ideje kuglizgatok, az, hogy miként lehetne magát az sql t megkérni a feladvány végrehajtására...
Még a futásidő sem egy nagy kérdés, mert maximum naponta egyszer kell és majdnem mindegy, hogy 1 sec vagy 1 min...

Biztos vagyok, hogy ez megoldható. Arra jöttem rá, hogy vagyok annyira kétségbeesett, hogy itt feltegyem a kérdést. Vállalva a következményeket. :D

  • 1995 megtekintés

( uid_395 v | 2017. 10. 28., szo – 17:08 )

"Biztos vagyok, hogy ez megoldható. Arra jöttem rá, hogy vagyok annyira kétségbeesett, hogy itt feltegyem a kérdést. Vállalva a következményeket. :D"

^^ ez jó volt ^^ :))

sorry, off, a témához nem tudok hozzászólni érdemben :)

( harlequin | 2017. 10. 28., szo – 17:09 )

Ha koordinátákkal dolgozol, mindenképp jól jönnek a spatial extensionok MySQL alatt, de méginkább a postgres+postgis kombó.

Igen. Majdnem.

Ahogy nézem, idáig jutottam el én is.
Amikor megadom a pontot, akkor megkapom az eredményt. Ez nem nagy truvaj.

Az igazi kérdés az, hogy a táblában levő x százezer közül én visszakapjak egy ID -t ami az adott sugarú körön belül a legtöbb pontot tartalmazza maga körül.
Scriptel ezt könnyű ahogy írtam ez nem volt gond megoldani, de szerintem nem elegáns, és biztosan megoldható sql belül is, nagyon csodálkoznék, ha nem.

http://karikasostor.hu - Az autentikus zajforrás.

Valahogy így értettem, csak az egyszerűség kedvéért a példába az "a" oszlop-beli számoktól adott távolságon belül (<4) lévő többi szám darabszámát nézem: 

CREATE TABLE szamok (a INT);
INSERT INTO szamok VALUES (1);
INSERT INTO szamok VALUES (2);
INSERT INTO szamok VALUES (1);
INSERT INTO szamok VALUES (4);
INSERT INTO szamok VALUES (2);
INSERT INTO szamok VALUES (9);
INSERT INTO szamok VALUES (1);
INSERT INTO szamok VALUES (7);
SELECT a, (SELECT count(*) FROM szamok AS sz WHERE abs(szamok.a-sz.a)<4) AS kozeli FROM szamok ORDER BY kozeli DESC LIMIT 1;

Üdv,
Marci

( uid_17626 | 2017. 10. 29., v – 12:59 )

Kurvára nem értek hozzá, de:
Nem lehet, hogy itt nem az euklideszi geometria kell, hanem a Bolyai–Lobacsev féle?
Kis távolságnál még a kis hiba nem észrevehető (annyira), na meg hely függő is.

Lásd:
Déli sarktól elindulok:
- észak felé 1 egységet.
- nyugatra fordulok és haladok 2 egységet
- délnek fordulok és haladok egy egységet
Visszatértem a kiindulópontra.
(A síkon 2 egység messze lennék tőle.)

( bbking | 2017. 10. 29., v – 13:26 )

Erre érdemes felhő szolgáltatásokat megnézni, a Bluemixnél van GIS szolgáltatás ami ennél sokkal többet tud, arra nem is lesz szükséged. Lehet erre az Analytics-et is használni, amikor neked nem kell az sql-el vesződni, hanem angol nyelven féltett kérdéseket tud értelmezni a rendszer és arra válaszokat adni akár grafikonok formájában. Biztos Azure -on is van ilyen.
Bluemixnél arra kell ügyelni, hogy az oszlopok angol nyelvű oszlopneveket tartalmazzanak, különben még nem tudja ezeket értelmezni
Van erre egy példám ha érdekel

--------
HOWTO: Zentyal+Zarafa+Setup+Outlook+Thunderbird+mobilephone sync

( Jason v | 2017. 10. 29., v – 14:49 )

Kifejezetten SQL-t akarsz gyakorolni?
Én is szórakoztam ilyenekkel régen.

Vagy spatial kiterjesztésekkel elkezdesz foglalkozni, vagy magadnak megírod.
Én az utóbbit választottam, ma már az előbbit tenném XD

1. Generáld le az összes pont-pont távot, de úgy, hogy a R távon belülieket nézed, eleve.
Én ezt úgy csináltam, hogy először is a befoglaló négyzetre szűrtem, s utána számoltam "igazi" gömbfelszíni távolságot.
(aki ezt a lépést tovább tudja egyszerűsíteni, kap tőlem egy szatyor szatyrot)
2. COUNT(), GROUP BY, ORDER BY.
3. Profit!

( mrceeka v | 2017. 10. 29., v – 17:05 )

MS-SQL-ben (Microsoft Azure SQL Database) összeraktam a dolgot, egy példán keresztül mutatom be.

Létrehoztam egy táblát a településeknek: 

CREATE TABLE [dbo].[Telepulesek](
	[TelepulesNev] [varchar](100),
	[Latitude] [float],
	[Longitude] [float],
)

Betöltöttem a magyar településeket, földrajzi koordinátáikkal: 


...
INSERT INTO [dbo].[Telepulesek] ([TelepulesNev] ,[Latitude] ,[Longitude]) VALUES  ('Zsombó',46.3278333333333,19.9741666666667)
INSERT INTO [dbo].[Telepulesek] ([TelepulesNev] ,[Latitude] ,[Longitude]) VALUES  ('Zsujta',48.5006666666667,21.28)
INSERT INTO [dbo].[Telepulesek] ([TelepulesNev] ,[Latitude] ,[Longitude]) VALUES  ('Zsurk',48.3988333333333,22.2233333333333)

Hozzáadtam a geolokációt mezőként a táblához: 


ALTER TABLE [dbo].[Telepulesek]
ADD [GeoLocation] GEOGRAPHY

Ezután kiszámoltam a földrajzi koordinátákból a geolokációt: 


UPDATE [dbo].[Telepulesek]
SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                    CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

Majd lekérdeztem azt a települést, ami körül 10 kilométeren belül a legtöbb település van: 


SELECT TOP 1 TelepulesNev, 
             (
               SELECT count(*) 
               FROM [dbo].[Telepulesek] AS tel 
               WHERE tel.geolocation.STDistance(tel2.GeoLocation) <= 10000
              ) as kozeli 
       FROM [dbo].[Telepulesek] as tel2 
       ORDER BY kozeli DESC

A válasz: 


TelepulesNev	kozeli
Babarcszolos	38

Most már ezt is tudjuk.

Üdv,
Marci