How to efficiently request large polygons with PostGIS

Question

I work with radio cards that seem too fragmented for an efficient request. The response time is 20-40 seconds when I ask if one point is inside the multipolygon (I tested "inside" / "contains" / "overlap"). I am using PostGIS, with GeoDjango, to abstract requests.

The polygon column has a GiST index, and I tried VACUUM ANALYZE. I am using PostgreSQL 8.3.7. and Django 1.2.

Maps are spread over large geographic areas. They were originally created by a radar that supports topography, and therefore the cells of the cells / polygons are fragmented .

My goal is to request points inside multipolygons (i.e. houses that may or may not be covered by signals).

All radio communication cards consist of 100,000 and 300,000 peaks (total), with a huge number of polygons. Some maps have less than 10 polygons. From there, it jumps between 10,000 and 30,000 polygons. The ratio of polygons to vertices does not seem to affect the time it takes to complete the queries.

I use the projected coordinate system and use the same system for both home and radio networks. Qgis shows that the radio segments and maps are correctly placed in the area.

My test requests consist of only one house for one radio card. I tested queries such as "inside" / "contains" / "overlaps," and the results are the same:

• The second second answer if the house is "far from" the radio card (I assume that this is because it is outside the frame that is automatically used in the request).

• The response time is 20-40 seconds if the house / point is close or is on the radio map.

Do I have alternative ways to optimize queries, or should I somehow modify / simplify the source material? Any advice is appreciated.

Answer 1

Hello

The first thing I would like to do is split the multipolygons into single polygons and create a new index. Then the index will work much more efficiently. Now the whole multipolygon has one big bounding box, and the index cannot say anything only if the house is inside the bounding box. Thus, smaller polygons in relation to the entire data set, more efficient use of the index. There are even methods for separating single polygons into smaller ones with a grid to get the index part of the query even more efficient. But, first of all, you need to split several polygons into single ones using ST_Dump (). If you have many attributes in one table, it would be wise to put this in another table and only save the identifier indicating which radio fan it belongs to. Otherwise, you will get a lot of duplicate attribute data.

NTN Niklas