# question

## Finding Distances between Lat/Long using STDistance

I have two tables that each have a latitude and Longitude. One table has 1691 (TableA) records and the other 5778 (TableB) records. Each record has a unique latitude and Longitude. I need to found the distance between every point in TableA to That of TableB and only display any records where the distance is only 10 meters or less apart. I'm currently using TSQLs geography function STDistance to do this. The whole process takes about 50 minutes. Is there a faster way? I'm using a cross join between the two tables so the ending record set is 9,770,598 records that need to be processed. Any help would be appreciated.,

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
The numbers listed here are a very limited testing set. TableA actually has close to 100000 and TableB over 3,000,000. This process will need to run every night to compare the new records added to TableB to TableA. As a record within the 10 meters is found in TableB then the matching recorded in TableA is marked Verified. New records can get added to TableA as well and those will need to be checked against TableB. So as you can see, this is a long term process. Again, any help would be appreciated.
1 Like 1 ·
·
Interesting question! If this processing is largely a one-off exercise then 50 minutes is arguably not too high a price to pay. What I mean is that once you've calculated the distance between point x and point y you theoretically never need to calculate it again, they'll always be the same distance apart (plate tectonics notwithstanding). Store the output of your priming round of cals (the ids of pairs less than 10m apart) in a permanent table. Would it be fair to say that there are a lot fewer than 10 million pairs that meet the 10m criteria? As new rows get added to table A and Table B they can have their calcs done incrementally and the output added to the third table. Obviously I suggest this without having any idea how many (if any) rows are going to be added to table A and B over time.
0 Likes 0 ·
·
@Grant-Fritchey How do we get this moved out of the Meta-Askssc questions? Seems to be in the wrong spot.
0 Likes 0 ·
·
I would love to move this to the correct area. This area was the only choice I was given when I added the post.
0 Likes 0 ·

·

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
I agree with @GPO basic assessment, reduce the number of places where you need to run the spatial functions as a first step. The second step would be to make sure you have a spatial index in place and that it's being used. You'll have to experiment with grid sizes and distribution for your data.
2 Likes 2 ·
·
That is actually an interesting concept that I hadn't thought of. I will see what I can do with that.
0 Likes 0 ·
·
Let us know how you get on. Best of luck.
0 Likes 0 ·
·
Thank you Grant Fritchey. I had never heard of "Spatial Indexes" until I read your post. That took my run time from 59 minutes down to just 12 seconds. Yes, I said seconds. We are good to go now and thank you all for the help.
0 Likes 0 ·
·
Excellent. Be sure to do the experimentation with the different grid sizes as I mentioned. You'll find that you may need different indexes depending on if the query is including or excluding more data. Spatial indexes get pretty weird.
0 Likes 0 ·