Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
Judging on the description of your problem, you are not necessarily looking for a PostGIS solution, or are you? –  underdark Oct 26 '12 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 Oct 26 '12 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 Oct 26 '12 at 13:57
    
im using st_intesects on where clause, using buffer in a polygon because i want search intersetions > 50m –  Ciro Vargas Oct 26 '12 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.

share|improve this answer

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.

share|improve this answer
    
nice but this not is my unique problem, in my databse have 800000 polygons, very large –  Ciro Vargas Oct 26 '12 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. –  Paul Ramsey Oct 28 '12 at 4:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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