0

I have a data source with multiple co-ordinates (can range from 1 to 10000) in one MS SQL DB whose radius can vary dynamically based on the users selection (Source A). Based on the all these co-ordinates I need to find intersecting co-ordinates (around 30000 co-ordinates) from another MS SQL DB (Source B). The radius from the second database is static (range is from 5 miles to entire US). Source A and Source B aren't aware of each other.

Currently, we are passing the first set of co-ordinates from the first DB to second DB. Then, we create a circle for the data from Source A and find intersections between the circles between circles on Source A and Source B. Source B is already storing a Spatial Area (circle) in the table so we don't have to recompute it. This is taking more than 2 minutes. We are trying to optimize it so that the time can be brought down to 1-2 secs. Wondering if anyone has ran into this problem before and optimized it?

P. Sharma
  • 71
  • 5
  • I'm having a hard time visualizing what your data actually is. You say that each of the pieces of data is a coordinate, but then mention radii which implies that they're geographic areas. Also, the silly question that I'd have is: can you bring one data set near the other (worst case scenario putting them in a temp table) so you can do a join between them? – Ben Thul Feb 20 '17 at 06:13
  • Let me clarify a bit more - I have one set of location each of which can have radius based on users preference. Lets say the locations are Orlando, Miami, NYC, etc. They can only match with locations that are within the radius selected. The other sets of location also has radius based on users preferences. Lets say this set has LA, San Fransisco, Las Vegas, etc. So based on the locations and radius from first set, we need to pull ANY matching locations from second set (which basically is an EXISTS condition in Sql Server). Also to your point I have both the data sets together. – P. Sharma Feb 21 '17 at 01:03
  • Also, the radii for the first set is selected by the user, hence it is not being saved as a Geography Area, but for the second set it is not dynamic hence it is being saved as a Geography Area by using STBuffer. – P. Sharma Feb 21 '17 at 01:06
  • Is the user's preference for the first set static insofar as a given user might say "I want things within 10 km regardless of the location"? Or is the user able to set a tolerance _for each_ location? – Ben Thul Feb 21 '17 at 04:02
  • The user will select sets of location from first set and say find me all locations that is (5,10,20,...200 KM) from the other set. The catch is the locations on other set also have some radius outside of which they won't go. – P. Sharma Feb 22 '17 at 00:45
  • Then I think this is just a relatively simple spatial query. Can you provide your schema in the question as well as what you've already tried? – Ben Thul Feb 22 '17 at 05:02

0 Answers0