CODEWITHSUNDEEP
c#.netsqlsql-server
YosiFZ
YosiFZ

Reputation: 7900

Querying GPS Coordinates

I want to build database with sql server 2008 that include columns of gps coordinate- 

latitude

and

longitude

and i want to build a query that will be something like :

"Give me all rows that are within 5(for example) metres of this coordinate"

It is possible in c#??

Upvotes: 1

Views: 1434

Answers (3)

Dejan Marjanović
Dejan Marjanović

Reputation: 19380

I know this is MySQL, but 1. Earth isn't flat 2. you want circle, not square... @radius should be in kilometers, 5 meters is too precise... 1 degree is roughly 111.2 kilometers (my assumption). If you want miles, change that to 69. http://en.wikipedia.org/wiki/Latitude#Meridian_distance_on_the_sphere

SELECT *, 
(SQRT(POW((lat - @lat), 2) + POW((lon - @lon), 2)) * 111.2) AS radius 
FROM table 
WHERE POW((lat - @lat), 2) + POW((lon - @lon), 2) < POW((@radius / 111.2), 2) 
ORDER BY radius ASC

I used this formula to calculate nearby radar checkpoints...

enter image description here

Upvotes: 1

SomeGuy
SomeGuy

Reputation: 495

If all you have is lats and longs, it's gonna be kind of slow, but a query like this should do it:

select latitude, longitude where (latitude - @lat)^2 + (longitude - @long)^2 <= @dist

@lat and @long being the coordinate, and @dist being the maximum distance allowed.

Edit: Debracey and I think it might be more effective to merge our two techniques. So first you would do a query using the bounding box, which is a really quick calculation, then you would take the (hopefully much smaller) sample that passes the bounding box test and test them against my query.

Upvotes: 1

debracey
debracey

Reputation: 6597

C# really isn't the problem, the problem is the database itself. MS SQL Server 2008 and higher has spatial support as described in this article:

http://msdn.microsoft.com/en-us/magazine/dd434647.aspx

Without using spatial extensions, you'd have to manually calculate the bounding box yourself (which would probably include points > 5 meters from the coordinate.) Then you'd have to restrict the results to make sure they are within 5 meters of the point in question.

Here's a little pic of the manual (ie. somewhat painful) calculation:

---------------------
|        ^          |
|     +5m lat       |
|                   |
|-5m lon *   +5m lon|
|                   |
|     -5m lat       |
|        v          |
---------------------

The (*) is the point in question. The problem with doing the calculation manually is that the points near the corners of the box are (probably) > 5 m away from the (*) point. You could calculate the bounding circle, but that's going to increase the complexity of your SQL query.

So - in short - you really need a DB with spatial support.

Upvotes: 4

Related Questions