I'm querying the database for searching polygons that intersect with others, but is very slow research, wanted to increase the speed.

I thought of using sphinx, solr, elastic search googled but did not find much content on georeferencing, wanted to know if it is possible to manipulate polygons in these tools

Im using st_intesects on where clause, using buffer in a polygon because i want search intersetions > 50m

  • Judging on the description of your problem, you are not necessarily looking for a PostGIS solution, or are you?
    – underdark
    Commented Oct 26, 2012 at 13:35
  • What is your algorithm now? If possible, post an EXPLAIN SQL, maybe it's not the way, but the db settings making the things slow...
    – Styp
    Commented Oct 26, 2012 at 13:47
  • 1
    Using PostGIS ST_Touches would detect the polygons postgis.refractions.net/docs/ST_Touches.html - indexing your polygon table will increase the speed.
    – Mapperz
    Commented Oct 26, 2012 at 13:57
  • im using st_intesects on where clause, using buffer in a polygon because i want search intersetions > 50m Commented Oct 26, 2012 at 17:37

2 Answers 2


You can refine your query. EXPLAIN ANALYSE is your friend. Try to build efficient queries. if for limit 1 the querry need .5 secs for 3000 polygons you need 1500 secs -> 25 minutes for all.

Some example that I am using (of course some of them maybe be wrong) :

  1. WHERE (a.geom && b.geom) -> makes the query run only the boundry box of both the shapes, not in the entire bontry of your geom collumn.
  2. WHERE ST_DISTANCE(a.geom,b.geom) < 10000 Do we care about the geometries that are less than 10km (for example) from a.geom? No, but forces postgis to use the index which has huge impact.

Also the book Postgis In Action in chapter 9, explains very nicely on how to write efficient spatial queries.


Searching a table within itself for items within 50m of a candidate (assuming you have a spatial index on that table)

SELECT a.name, a.id
FROM mytable a, mytable b
WHERE ST_DWithin(a.geom, b.geom, 50)
AND b.name = 'Fred';

Don't use buffer in queries, use distance tests. And use ST_DWithin (true/false, index-enabled) rather than ST_Distance.

  • nice but this not is my unique problem, in my databse have 800000 polygons, very large Commented Oct 26, 2012 at 17:52
  • This is the problem as you described it. And as long as you have an index in your geometry the number of polygons in your table is not the issue. Commented Oct 28, 2012 at 4:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.