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 a newbie in postgis and I have a problem in query performance.

This my query:

SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1 
WHERE ST_Intersects ( ST_GeomFromText('a multiypolygon geom goes here',4326),position) 
ORDER BY userid, timestamp desc

and the problem is my multipolygon include VERY large polygons (600 pages long in word doc!) and it took more than 2 hours to execute!

Is there a way to optimize my query or use another way?

Please your help is greatly appreciated!

share|improve this question
add comment

3 Answers

What you should do is put your big multipolygon in a table as single polygons (with ST_Dump) and put an index on it. Something like:

CREATE TABLE big_polygon as
SELECT (ST_Dump( ST_GeomFromText('a multiypolygon geom goes here',4326))).geom as geom;

-- It is always great to put a primary key on the table
ALTER table big_polygon ADD Column gid serial PRIMARY KEY;

-- Create the index
CREATE INDEX idx_big_polygon_geom
on big_polygon
USING gist(geom);

-- To give the database some information about how the index looks
analyze big_polygon;

-- Then you go:
SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1, big polygon WHERE ST_Intersects ( big_polygon.geom,position) 
ORDER BY userid, timestamp desc;

That should be way faster for several reasons.

HTH

Nicklas

share|improve this answer
    
Thanks Nicklas for this great reply. Sorry I missed to mention that I have more than one polygons and they are already stored in a table with index. But I tought provide the geom data directly would be faster. However, I try the way you suggest but it's still take very long time! any other suggestion? –  Sara Feb 6 '12 at 12:23
    
@Sara . Ok, so you did try to split the multigeoemtries into single geometries as I suggest with ST_Dump? –  Nicklas Avén Feb 6 '12 at 12:41
    
How many user positions are we talking about? How many big polygons? What do you get from SELECT ST_npoints(geom) from big_polygons_table;? –  Nicklas Avén Feb 6 '12 at 12:42
    
Sorry my bad,let me explain more about my tables to make it more clear for you: I have table1 which includes a geom column that have about 230 rows and in each row there is a multipolygon(they represent countries so they varies in size) ,and have index in the_geom col . Table2 which includes position column (points) ,timestamp ,userid and id(pk) and 3 indexs created using(position, timestamp, userid) .this table is very large about 103496003 rows Tha max number of ST_npoints is 1440430 and the min number is 16. I'm sorry if I made you confuse but I really need your help! Thanks –  Sara Feb 7 '12 at 6:56
add comment

It depends what kind of quality - precision you need. You can obviously simplify the polygons by using: http://postgis.net/docs/ST_Simplify.html

What I did often during developement of my GIS application was to think about the best way to minimize data. Eg. preselect the polygons within the boundary box for example. - Depending on zoomlevel you don't need ultra precise results (st_simplify) so on...

Hope that helped you a bit!

share|improve this answer
    
Thanks Martin for your quick response. My problem is I need the result to be very accurate so I think this function will not help me here! but Thanks for the suggestion –  Sara Feb 6 '12 at 12:27
add comment

Depending on your postgres and/or sql expertise you have several options:

  1. analyze the query through the EXPLAIN command to find out if you're hitting a particular bottleneck. Warning: sometimes the output of EXPLAIN can be difficult to understand

  2. if you expect that most or a significant portion of the geometries in table1 do NOT intersect the multipolygon you could try to apply a preliminary condition against a simpler polygon (i.e. by breaking the multiploygon in smaller pieces) and then run the heavier multipolygon intersection only on those results. See below for an example.

  3. if and only if CPU is the bottleneck (i.e. the server is stuck computing intersections) I dully suggest you get a bigger, faster, more powerful CPU or rent a one-time High-CPU Instance off Amazon's EC2 and destroy it when you're done

Example query for item 2:

SELECT DISTINCT ON (st1.userid) st1.userid ,ST_AsText(st1.position), st1.timestamp  
FROM (
    select userid, position, timestamp from table1 
    WHERE ST_Intersects ( YOUR_MULTIPOL_BOUNDS_HERE,position)
) as st1 
WHERE ST_Intersects ( ST_GeomFromText('a multiypolygon geom goes     here',4326),st1.position) 
ORDER BY st1.userid, st1.timestamp desc

To improve performance you could also temporarily materialize subselect st1 as a table so that you can index it.

@Nicklas is right to point out in the comments that example for suggestion 2 should not help. He is right, but I think I'm (partly) right too.

In fact it seems a very similar question was asked (and answered) just last November on the postgis ML:

http://postgis.refractions.net/pipermail/postgis-users/2011-November/031344.html

and turns out the suggestion is to actually break up the polygon so that the index can most effectively filter out false intersections that would be otherwise triggered by a simple boundary check.

share|improve this answer
    
suggestion 2 should not help because that is exactly what the index is doing. So that construct will be just doing the same once again. –  Nicklas Avén Feb 6 '12 at 13:54
    
@NicklasAvén you're right, I amended the answer –  unicoletti Feb 6 '12 at 14:29
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.