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 have a few MULTIPOLYGON geometries that are very complex to work with, and I need to find POINT geometries from a ~40M rows that are inside these MULTIPOLYGON geometries.
Right now I'm working with ST_Within but things are going on extremely slowly.

I don't need all of the POINT geometries, just a few of them, but they all have to be inside of the MULTIPOLYGON (I need a few points per geometry).

I thought that if there was something reverse to an envelope, that is to find the largest box that is within a geometry.

I couldn't find it in the docs or Google or here.

Is it possible to create such a box? If so, how?

I do not want to use simplify since it is:

  1. Not accurate enough.
  2. Still slow.
share|improve this question
add comment

3 Answers 3

Try to dump the multipolygons to polygons. Build gist index and see what happens.

CREATE TABLE single_polygons AS
SELECT (ST_Dump(geom)).geom geom FROM multipolygon_table;

CREATE INDEX idx_sp_geom
ON single_polygons
USING GIST(geom);

ANALYZE single_polygons;

EDIT Of course you want an index. The index finds all points inside the bounding box of the multipolygon very fast. Then a function is rechecking which of those points inside the bounding box actually is inside the geometry.

The reason I suggest splitting the MultiPolygons to Polygons is that the bounding box will be unnessecary expanded and catch unnessecary many points to recheck if it is a MultiPolygon since the index is built on the bouning box around the whole MultiPolygon.

If you only wants a few points you can always limit the answer to the amount of points you want.

SELECT p.geom, p.id 
FROM point_table p, single_polygons sp 
WHERE ST_Intersects(p.geom, sp.geom) 
LIMIT 10;
share|improve this answer
    
I have used an index in the first attempt to obtain the data, it didn't substantially change the time it took to get the data. The idea to to split the multypolygon to individual polygons is a good idea, I will try it in my next attempt, though it'll be a while from now. –  David Kohen Mar 19 '13 at 6:57
add comment

A GiST index on the MULTIPOLYGON geometries will speed up the search. Sometimes use of the ST_DWithin function in a CTE will further cut down the search space, but in this case I don't think it will help you any more than the spatial index alone.

share|improve this answer
    
There are ~400 multipolygons, which I go over one by one, an index won't help me here. –  David Kohen Mar 13 '13 at 17:41
    
Why don't you want an index? I will update my answer –  Nicklas Avén Mar 13 '13 at 23:36
add comment

Create a grid of regular polygons small enough to ensure that every MULTIPOLYGON will contain at least one. Then query the MULTIPOLYGONS with ST_Within against the grid with a LIMIT 1. Finally, run your point ST_Within query against the selected grid polygon.

Perhaps any ST_Within query against your complex geometry is too expensive, but it might work.

share|improve this answer
add comment

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.