Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 8 down vote favorite
2

I have a spatial table in a postgres/postgis database. Every row in it represents a Polygon. It is of following form:

+----+--------+
|gid |   way  |
+----+--------+
|241 | 01030..|

The geometric column is "way" which contains the geometry for a polygon. In WKT it is: POLYGON(('....')). I am doing a lot of ST_Contains queries on this table, to test whether two polygons are contained in each other e.g.:

Select ST_Contains(a.way, b.way) From table AS a, table AS b Where a.gid = 15 And b.gid = 16

I was wondering how to speed up this query and added a spatial index on the table:

CREATE INDEX table_way_gist ON table USING gist(way);

But actually I dont see a speed up. I create the index AFTER I filled the table with all polygons BEFORE I do the ST_Contains queries. Should the index be added before filling a table? Are there special requirements on the table to work with the index? The projection (srid) of the geometric column way is set to 900913.

I am using: psql (PostgreSQL) 9.1.4 / POSTGIS="1.5.3"

Thx for help.

share|improve this question

2 Answers

up vote 10 down vote accepted

The most efficient index for the query expressed in your question is the one on gid as it is the only column that appears in a where expression:

 CREATE INDEX table_gid ON table (gid);

You can safely drop the gist index as it will only consume space and slow inserts/updates/deletes down.

Long explanation

As I said the most effective index in your case is the one on gid as it will allow the db engine to retrieve rows faster (with retrieval usually being the slowest part of the process). After that it will probably better compute the result of the

  ST_Contains(a.way, b.way)

espression without looking at the index. The reason is that the query planner will likely estimate that the extra cost of looking up the gist index on both columns versus looking up the a.way and b.way values directly is not worth the effort as the total number of rows to look up is probably very small especially if the index is unique.

As a rule of thumb remember that the planner will probably favor a table scan over an index scan for small datasets (dataset sizes are estimated by looking at the table statistics).

share|improve this answer
That makes the issue more clear to me. I will try it. So if I put the ST_Contains() query into the WHERE clause, the spatial index should actually be helpful? I think I have to reorganize my script to call ST_Contains within WHERE clause. At the moment I am iterating through all polygons and always test two of them seperately. – MichiMichbeck Dec 20 '12 at 9:57
up vote 8 down vote

As unicoletti said, the gist index in the geometry column would only work if you use ST_Contains() in the WHERE expression.

For instance, if you would like to know all polygons that contain one another, you could use something like this: 

SELECT a.gid, b.gid
FROM table AS a, table as b
WHERE a.gid != b.gid and ST_Contains(a.way, b.way)

In this case, depending on the size of your table and the complexity of your geometries, the gist index should provide a significant speed up, as ST_Contains will start by filtering the polygons by comparing their boundary boxes before actually check their complete geometries. You can see a small explanation in the OpenGeo Tutorial.

share|improve this answer
Yes I see, I need this query to involve the index boundary test. Thx Alexandre. (I'll mark unicoletti as solution, as he was fast and cleared the issue to me) – MichiMichbeck Dec 20 '12 at 10:46

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.