According to this Boundless link, it turns out that in PostGIS, spatial query on table with spatial index uses two pass system: with the first pass approximates spatial relationship with bounding box.

Now i was wondering if such approach will be effective for a PostGIS table with geometry type "ST_POINT", as bounding box of a point IS essentially the point itself.

With this in mind, I run an ST_INTERSECT query by Jmeter to 2 tables with exactly the same set of points, and exactly the same intersect BOX. The only difference is one table has spatial index and the other don't.

The result surprises me: the one with spatial index performed much faster.

In my machine, the average response times are:

  • with spatial index: 911 ms
  • without spatial index: 62038 ms

Now the question: why does spatial index still matter for a table with only points?

up vote 8 down vote accepted

The description from Boundless is very simplified. It somehow misses the first important step: The index will be able to filter most points/bounding boxes before running any calculation because of their location in the R-Tree.

R-Trees break up data into rectangles, and sub-rectangles, and sub-sub rectangles, etc. The index will look in which rectangle the data lies, and filters any points that are outside this rectangle, then the same with the sub-rectangles inside that rectangle only and so on...

For a better understanding of Indexes/R-Trees yo might start reading the wikipedia article on that topic.

  • i have a question here. I am trying to spatial join a points feature with a polygons feature using postgis function ST_Intersects. And i am running out of memory. I already have index on polygon feature. Does in anyway spatial index on point feature help to reduce the use of memory due to reduced searches ? – Jio Jun 18 at 10:57
  • I think you should ask a new question with a bit more details. But first why not just try? – RoVo Jun 18 at 11:03

In your case points do not have any other index and query probably does not even try to use other indexes. BBOX queries can be pretty fast if X and Y coordinates are stored into separate, indexed fields and queries are made to include where X > xmin and X < xmax and Y > ymin and Y < ymax.

There are still loads of old databases which have location points stored only as X and Y coordinates. However, using geometry data type also for points makes it much easier to use the advanced spatial ST-functions. It is not only the speed of BBOX selection that counts.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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