0

I am new in this and would be happy if someone can provide links or keywords to google.
I will have tens of thousands events with geo data values like points, lines and polygons and need a fast hit test algorithm that will find all places that overlap a given point/line/polygon with a given approximation. At the same time I need to filter these events by variuos properties, so the query should filter both geodata and custom fields. For example, I'd like to know what cities, rivers or mountain regions located in N kilometers from specific place. Need data persisted in the SQL server but can think of a different way. What is a common approach for such tasks?

LINQ2Vodka
  • 2,996
  • 2
  • 27
  • 47

1 Answers1

0

The SQL way seems to be quite common, and not so hard to use.

Once your data are saved, you can extract point of interest in relative distance with function like :

SELECT tblcity.city, tblcity.latitude, tblcity.longitude, 
truncate((degrees(acos( sin(radians(tblcity.latitude)) 
* sin(radians(45.266708)) 
+ cos(radians(tblcity.latitude)) 
* cos(radians(45.266708)) 
* cos(radians(-73.616257 - tblcity.longitude) ) ) ) 
* 69.09*1.6),1) as distance 
FROM tblcity HAVING distance < 10 ORDER BY distance desc

Taken from https://stackoverflow.com/a/2695446/6660122

Edit : As you already know SQL and need extra functionnality that SQL can offer, i would definitively go this way !

Community
  • 1
  • 1
technico
  • 1,192
  • 1
  • 12
  • 22
  • I know how to program it in SQL, but will this work fast (milliseconds) on tens thousand records? – LINQ2Vodka Aug 03 '16 at 16:54
  • Well, i depends on many factors, but 10000 lines is not a great challenge to deal with. I believe it will be fast enough for many common projects. – technico Aug 03 '16 at 17:01