0

I have a table in SQL Server that contains GPS "tracks" stored as latitude / longitude Decimal columns. A track is simply a series of connected points that are recorded by the GPS.

When adding a new track I need to query the database to see if it matches any existing tracks. Since recorded GPS coordinates are not exactly the same, it must allow for an error margin. Can anyone suggest an efficient way to do this?

Darshan Dave
  • 645
  • 2
  • 9
  • 32
D. English
  • 1,671
  • 1
  • 8
  • 7
  • I might be missing something, but could you just defining a distance calculation function of some kind and simply use `where distance(knownLat, knownLong, newLat, newLong) <= MARGIN;`? I'm not much of a SQL expert, so I can't speak to efficiency. Either way, potentially useful reading: https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula – CollinD Apr 05 '18 at 21:33
  • The easiest way to allow for an error is just to round or drop significant digits. If its 46.4234234 and 46.42342565 and you want it to be equal just round 3 places. – Holmes IV Apr 05 '18 at 21:33
  • Thanks but distance function on every location wouldn't be very efficient on a large dataset. – D. English Apr 06 '18 at 00:37
  • Here are the primitives you need to do the math - built in to SQL Server (usually). https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/ogc-methods-on-geography-instances A trick is to get your points into geometry / geography types (like a best fit line of breadcrumbs) and then run the built in OGC operations against the points. Understanding the literal string "POINT (42 11)" and "POLYGON ( 40 70, 90 70, 90 90, 40 70))" and creating lines to do the math on may get you started. You may end up storing actual POLYGON tracks in the db and then do OGC STG ops on tracks not points – Sql Surfer Apr 06 '18 at 02:03
  • @SqlSurfer thanks, I'll look into the spatial sql – D. English Apr 06 '18 at 18:30

1 Answers1

0

Try this

SELECT * FROM (SELECT * FROM `tracks` as table1 
WHERE `longitude` BETWEEN '75.294472'-0.01 AND '75.294472'+0.01) as table2
WHERE `latitude` BETWEEN '19.881256'-0.01 AND '19.881256'+0.01

here 0.02 is the margin

table1 contains all the rows that satisfies the longitude condition which are again filtered in table2 satisfying the latitude condition

Arun Amatya
  • 51
  • 2
  • 9