Timeline for How to optimize spatial join?
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 4 at 14:06 | vote | accept | gotqn | ||
| Feb 1 at 10:02 | answer | added | gotqn | timeline score: 0 | |
| Jan 12 at 11:52 | comment | added | Stephen Morris - Mo64 | I guess thete are many more users than locations (could be wrong) if you convert the locations to circles ( temp table ? ) and then search which points / users are within the circles that is often faster | |
| Jan 12 at 6:24 | comment | added | gotqn | @StephenMorris-Mo64 Yes, it is the first variant01 in my example. | |
| Jan 11 at 19:39 | comment | added | J.D. | @gotqn Ah I think I understand. You're not trying to find how many users who are currently within a fixed distance of a business. Rather you want to know which users current position + their range (a dynamic distance) would overlap a specific business? | |
| Jan 11 at 19:09 | comment | added | gotqn | Well, we have users with travel range. And we need to find all locations in their range. That's it. | |
| Jan 11 at 18:17 | comment | added | J.D. | @gotqn If I get time I will try playing with it. But by your description it sounds like you're trying to do the same thing just in the opposite direction. Idk why that would be any different programmatically. I would also be surprised if PostgreSQL was measurably more performant for this use case than SQL Server, but anything's possible. | |
| Jan 11 at 18:09 | comment | added | gotqn | @J.D. you can play with my sample data - I believe it is self explaining; there a lot of geospatial functions and some other settings on the spatial indexes which I try, but nothing helped... I guess that the engine might be just not good with these types of queries, which is again valid answer for me - to stop trying optimize this | |
| Jan 11 at 18:05 | comment | added | gotqn | @IanTurton it's not so easy to change the used database in my case :-) it's 15+ years app :-) so, in postgres such queries are fast? | |
| Jan 11 at 17:28 | comment | added | J.D. | @IanTurton If you have 20 years experience in SQL Server, I'm surprised you're unsure of what spatial operators it offers. | |
| Jan 11 at 17:27 | comment | added | J.D. | @gotqn Admittedly I'm not an expert on geospatial querying, having only done it a handful of times. So I'd have to sit down and spend some time closely looking at your example. But by your verbal description, I don't understand the difference of looking for "all instances of ObjectA by geography relative to ObjectB" vs "all instances of ObjectB by geography relative to ObjectA" (to generalize my understanding of your goal)? | |
| Jan 11 at 17:24 | comment | added | Ian Turton | I have 20 years of experience in trying to make both DB go fast for spatial queries and I have never got good performance in sql-server (and don't even mention oracle) | |
| Jan 11 at 17:21 | comment | added | J.D. | @IanTurton Perhaps a little pre-mature to say switching database systems will improve performance when you're unfamiliar with the one being switched from. | |
| Jan 11 at 17:18 | comment | added | Ian Turton |
Usually I would use ST_Dwithin but I don't know if sql-server has that operator - if you really want speed then it might be worth switching to postgres with postgis extension.
|
|
| Jan 11 at 15:11 | comment | added | Stephen Morris - Mo64 | Have you tried buffering the location points to turn them into polygons and then doing a Contains search ? | |
| Jan 11 at 15:02 | history | asked | gotqn | CC BY-SA 4.0 |