1

I have two tables, one with 66 thousand points and other with 5.5 thousand polygons. I'm trying to correlate which point is in which polygon and it is taking a long time. I want to know if spatial indexing should improve the query speed.

The polygon layer is already simplified and I've already created the spatial index for both the coordinates layer and the polygons layer.

I've tried this on PostGIS with SQL queries and in QGIS with it's Point in Polygon algorithm. Both of them take several minutes to process.

Does spatial indexing really help with this kind of problem, or just the nearest neighbors or intersections kinds of problems?

  • 1
    This blog post by Nathan Woodrow says that spatial indexing will speed up spatial operations using Python. Presumably it would have the same effect on spatial operations using the built-in tools. But it seems quite simple for you to test. Several minutes of processing time is ideal for this kind of test. It's short enough that you can repeat a few times for testing, but long enough to make any difference easily measurable. – csk Jul 11 at 15:27
  • 2
    Yes. Spatial indexing will speed up this process or any other spatial query. BTW, what is a "long time"? I wouldn't expect this to take longer than a couple minutes on any modern machine. Maybe we could help to optimize your query. – Tom Jul 11 at 15:42
  • 3
    ...if you' d post it into your question. Any question concerning query speed should also include the EXPLAIN ANALYZE <query> output. Make sure you ran standard maintenance on your tabes, i.e. VACUUM ANALYZE <table>; – ThingumaBob Jul 11 at 15:45
  • In addition, you can always do a comparison of query times between having and not having a spatial index. – vagvaf Jul 12 at 4:53
  • Be sure to have the good indexes (if it's a 4326 geom, you can also manipulate this as geography and they need specific index). Be sure to analyse your tables, and look at the explain to see the problem. Maybe you can show us your request, and how you made your indexes, so we can see if we can help? Typically it's not the same think if you try to find the first polygon in wich your point is, or if you try to find all the polygons. – robin loche Jul 19 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.