Usa estensioni spaziali MySQL per selezionare punti all’interno del cerchio

Ho una tabella chiamata flags che contiene una colonna chiamata coordinates che è piena di ‘punti’ MySQL. Devo eseguire una query in cui ottengo tutti i flag all’interno di un cerchio in base a una posizione di latitudine e longitudine con raggio di 100 m.

Dal punto di vista dell’utilizzo, questo è basato sulla posizione dell’utente. Ad esempio, il telefono cellulare fornisce la posizione di latitudine e longitudine dell’utente e quindi lo passa a questa parte dell’API. Spetta quindi all’API creare un cerchio invisibile attorno all’utente con un raggio di 100 metri e quindi restituire i flag presenti in questa cerchia.

È questa parte dell’API Non sono sicuro di come creare perché non sono sicuro di come usare SQL per creare questo cerchio invisibile e selezionare i punti solo all’interno di questo raggio.

È ansible? Esiste una funzione spaziale MySQL che mi aiuterà a farlo?

Credo che la funzione Buffer() possa farlo ma non riesco a trovare alcuna documentazione su come usarla (ad esempio, esempio SQL). Idealmente ho bisogno di una risposta che dimostri come usare questa funzione o la più vicina ad essa. Dove sto memorizzando queste coordinate come punti geospaziali dovrei usare una funzione geospaziale per fare quello che sto chiedendo per massimizzare l’efficienza.

    Tabella delle bandiere:

    • id
    • coordinate
    • nome

    Riga di esempio:

    1 | [GEOMETRIA – 25B] | Tenacy AB

    Per la tabella delle bandiere ho posizioni di latitudine, longitudine e est e nord (UTM)

    La posizione dell’utente è solo di latitudine / longitudine standard, ma ho una libreria che può convertire questa posizione in UTM

    Non ci sono funzioni di estensione geospaziale in MySQL che supportano calcoli di latitudine / longitudine. Esiste MySQL 5.7 .

    Stai chiedendo cerchi di prossimità sulla superficie della terra. Nella tua domanda hai menzionato che hai valori lat / long per ogni riga nella tabella dei tuoi flags e anche valori proiettati Mercator (UTM) trasversali universali in una delle diverse zone UTM . Se ricordo che il mio Ordnance Survey del Regno Unito mappa correttamente, UTM è utile per localizzare gli oggetti su quelle mappe.

    È semplice calcolare la distanza tra due punti nella stessa zona in UTM: la distanza cartesiana fa il trucco. Ma quando i punti sono in zone diverse, quel calcolo non funziona.

    Di conseguenza, per l’applicazione descritta nella tua domanda, è necessario utilizzare la distanza del cerchio grande , che viene calcasting usando la haversina o un’altra formula adatta.

    MySQL, ampliato con estensioni geospaziali, supporta un modo per rappresentare varie forms planari (punti, polilinee, poligoni e così via) come primitive geometriche. MySQL 5.6 implementa una funzione di distanza non documentata st_distance(p1, p2) . Tuttavia, questa funzione restituisce le distanze cartesiane. Quindi è del tutto inadatto per calcoli basati sulla latitudine e sulla longitudine. Alle latitudini temperate un grado di latitudine sottende quasi il doppio della distanza superficiale (nord-sud) come grado di longitudine (est-ovest), poiché le linee di latitudine si avvicinano sempre più vicine ai poli.

    Quindi, una formula di prossimità circolare deve usare latitudine e longitudine autentiche.

    Nella tua applicazione, puoi trovare tutti i punti flags entro dieci miglia statute di un determinato latpoint,longpoint con una query come questa:

      SELECT id, coordinates, name, r, units * DEGREES( ACOS( COS(RADIANS(latpoint)) * COS(RADIANS(X(coordinates))) * COS(RADIANS(longpoint) - RADIANS(Y(coordinates))) + SIN(RADIANS(latpoint)) * SIN(RADIANS(X(coordinates))))) AS distance FROM flags JOIN ( SELECT 42.81 AS latpoint, -70.81 AS longpoint, 10.0 AS r, 69.0 AS units ) AS p ON (1=1) WHERE MbrContains(GeomFromText ( CONCAT('LINESTRING(', latpoint-(r/units),' ', longpoint-(r /(units* COS(RADIANS(latpoint)))), ',', latpoint+(r/units) ,' ', longpoint+(r /(units * COS(RADIANS(latpoint)))), ')')), coordinates) 

    Se si desidera cercare punti entro 20 km, modificare questa riga della query

      20.0 AS r, 69.0 AS units 

    a questo, per esempio

      20.0 AS r, 111.045 AS units 

    r è il raggio in cui si desidera cercare. units sono le unità di distanza (miglia, km, lunghezze, qualunque cosa tu voglia) per grado di latitudine sulla superficie della terra.

    Questa query utilizza un limite lat / long e MbrContains per escludere i punti che sono decisamente troppo lontani dal punto di partenza, quindi utilizza la formula della distanza del cerchio grande per generare le distanze per i punti rimanenti. Una spiegazione di tutto questo può essere trovata qui . Se la tua tabella utilizza il metodo di accesso MyISAM e ha un indice spaziale, MbrContains sfrutterà quell’indice per ottenere una ricerca veloce.

    Infine, la query sopra seleziona tutti i punti all’interno del rettangolo. Per restringere solo i punti nel cerchio e ordinarli per prossimità, avvolgi la query in questo modo:

      SELECT id, coordinates, name FROM ( /* the query above, paste it in here */ ) AS d WHERE d.distance <= dr ORDER BY d.distance ASC 

    Ciò presuppone che le coordinate nella tabella siano memorizzate come un tipo di dati POINT () in una colonna denominata “punto”. La funzione X (punto) e Y (punto) estraggono rispettivamente i valori di latitudine e longitudine dal valore del punto.

     SET @lat = the latitude of the point SET @lon = the longitude of the point SET @rad = radius in Kilometers to search from the point SET @table = name of your table SELECT X(point),Y(point),*, ( 6373 * acos ( cos ( radians( @lat ) ) * cos( radians( X(point) ) ) * cos( radians( Y(point) ) - radians( @lon ) ) + sin ( radians( @lat ) ) * sin( radians( X(point) ) ) ) ) AS distance FROM @table HAVING distance < @rad 

    Se vuoi farlo in miglia, sostituisci la costante 6373 con 3959

    Per coloro che desiderano ridurre la syntax della query, ecco un'implementazione comune di una funzione MySQL definita dall'utente per l'implementazione di una funzione di distanza basata sulle formule di Haversine.

     CREATE FUNCTION HAVERSINE ( coord1 POINT, coord2 POINT ) RETURNS DOUBLE DETERMINISTIC BEGIN DECLARE dist DOUBLE; SET rlat1 = radians( X( coord1 ) ); SET rlat2 = radians( X( coord2 ) ); SET rlon1 = radians( Y( coord1 ) ); SET rlon2 = radians( Y( coord2 ) ); SET dist = ACOS( COS( rlat1 ) * COS( rlon1 ) * COS( rlat2 ) * COS( rlon2 ) + COS( rlat1 ) * SIN( rlon1 ) * COS( rlat2 ) * SIN( rlon2 ) + SIN( rlat1 ) * SIN( rlat2 ) ) * 6372.8; RETURN dist; END 

    AGGIORNARE

    Utilizzare ST_Distance_Sphere () per calcolare le distanze utilizzando un lat / long

    http://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere

    I buffer non ti aiuteranno molto in MySQL <5.6, poiché il buffer è un poligono e le operazioni di poligono in MySQL <5.6 sono implementate come "Minimal Bounding Rectangles" (MBR), che sono piuttosto inutili.

    Dal momento che MySQL 5.6, sono state implementate le operazioni complete non-MBR st_* . Ma la soluzione migliore per te, in caso di cerchia, è usare la funzione non documentata st_distance :

     select * from waypoints where st_distance(point(@center_lon, @center_lat), coordinates) <= radius; 

    È stato difficile da trovare, dal momento che non è documentato 🙂 Ma è menzionato su questo blog , il cui autore ha anche riempito il bug segnalato . Ci sono comunque avvertimenti (citando il blog):

    Le cattive notizie sono:

    1) Tutte le funzioni usano ancora solo le coordinate del sistema planare. SRID diversi non sono supportati.

    2) Gli indici spaziali (RTREE) sono supportati solo per le tabelle MyISAM. Si possono usare le funzioni per le tabelle InnoDB, ma non utilizzerà le chiavi spaziali.

    Punto 1) significa che l'unità di distanza sarà uguale all'unità di coordinate (gradi in caso di WGS84). Se hai bisogno di una distanza in metri, devi usare un sistema di coordinamento proiettato (es. UTM o simile) che abbia unità corrispondenti a metri.

    Quindi, nel caso in cui non si desideri seguire questi avvertimenti, o nel caso di MySQL <5.6, sarà necessario scrivere la propria funzione di distanza personalizzata.

    Spero che la mia versione aiuti

     SELECT * FROM `locator` WHERE SQRT(POW(X(`center`) - 49.843317 , 2) + POW(Y(`center`) - 24.026642, 2)) * 100 < `radius` 

    dettagli qui http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql

    da: https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql

     SELECT id, ( 6371 * acos ( cos ( radians(78.3232) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(65.3234) ) + sin ( radians(78.3232) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 30 ORDER BY distance LIMIT 0 , 20; 

    (ricorda di sostituire tutte le costanti, questo esempio è per chilometri)

    Puoi usare:

     SELECT name, lat, lng FROM vw_mytable WHERE ST_Contains(ST_Buffer( ST_GeomFromText('POINT(12.3456 34.5678)'), (0.00001*1000)) , mypoint) = 1 

    L’espressione: 0,00001 * 1000 all’interno della frase sopra ti dà un cerchio con 1000 Metri di diametro, il suo essere applicato su una vista qui, la colonna nome è solo un’etichetta da puntare, il mypoint è il nome della mia colonna punti, lat è stato calcolato all’interno guarda con ST_X (mytable.mypoint) e lng con ST_Y (mytable.mypoint) e mi mostrano semplicemente i valori letterali di lat e lng. Darà a te tutte le coordinate che appartengono alla cerchia.

    per completezza, a partire da MySQL 5.7.6. puoi usare la funzione ST_Distance_Sphere che ottiene lo stesso risultato:

     SET @pt1 = ST_GeomFromText('POINT(12.3456 34.5678)'); SELECT * from (SELECT * ,(ST_Distance_Sphere(@pt1, location, 6373)) AS distance FROM mydb.Event ORDER BY distance) x WHERE x.distance <= 30; 

    In questo caso, forniamo il raggio approssimativo della Terra in chilometri (6373) e un punto (@ pt1). Questo codice calcolerà la distanza (in chilometri) tra quel punto (12.3456 long, lat 34.5678) e tutti i punti contenuti nel database in cui la distanza è 30km o meno.