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.

Hoping someone can see a faster way of doing this, because at the moment I'm averaging 9 hours per run for what I'm trying to do and well basically getting my behind chewed off by the boss :-)

The Scenario

We have a database running under PostgreSQL/postgis that's used to manage some mapping data for management of a transport fleet.

Within this database there are several tables but for my question here, we are interested only in "built up areas" (aka limitedzones) and "Movement Speeds" (aka speeds) - I can't go too much into detail because of NDA's etc, but "limitedzones" is a table that contains a number of

MULTIPOLYGONS

each representing a geographic area, and has an associated speed limit that a vehicle is allowed to travel whilst inside the bounds of that polygon.

"speeds" is a table of

POINTS

each point following the path of an allowed road and sampled with an allowed speed limit at 1 second intervals, representing a road that a vehicle is allowed to travel on.

At present some of these "allowed roads" cross through "Limited areas" where both the "allowed road" and "Limited Area" speed limit are the same. I've been tasked with removing these extra points from the "Allowed Roads" table where they match the conditions set out above.

What I've tried so far

My first attempt was to simply select the points, so lets say for 40 Mph and put them into a memory table, then loop over that table comparing them to the 'limitedareas' table using "ST_Intersects" this didn't work as expected, and failed to remove all the points.

It did however give me an idea of how many points I was looking at removing, and the count was easily in excess of 2 million.

After a bit more fiddling around, and a few more attempts I finally arrived at the following:

DELETE FROM speeds s
USING limitedzones lz
WHERE ST_Within(s.geometry,lz.geometry)
AND s.speed = lz.speed

and in a similar fashion used the following to interrogate & count:

SELECT s.* FROM speeds s
JOIN limitedzones lz ON ST_Within(s.geometry,lz.geometry)
WHERE s.speed = lz.speed
--LIMIT 10

Both of these work exactly the way I expect, but the select (When not limited) takes about 3 hours to run, and the delete over 9 hours!!

If at all possible I need to find a faster way of stripping these points out, as there is likely to be further work to add this into a regular update system, used ad-hoc on a frequent basis to keep the database in order.

I have good indexes both B-Tree (gist) and regular on all the columns, and when individual tables are used on there own, everything is nice and fast, so I know it's the join on 2 tables that's causing me the slowdown.

Any suggestions are welcome, and I'm even open to do this using a custom function / procedure if need be.

share|improve this question
    
I'd suggest you read Dan Tow's book SQL tuning, in addition to looking for a PostGIS approach. I'm working on a similarly large (though non-spatial) database and that book helped me get a query that was taking a week down to about 30 minutes. It seems likely that you'll need to approach this from several angles and straight SQL tuning should probably be one of them. –  pyrogerg May 9 '12 at 0:20
    
thanks pyrogerg, i'll look it up. –  shawty May 9 '12 at 17:37
add comment

2 Answers

I don't know much of anything about PostGIS, but I do know that I was having similar "Delete is taking days" problems with Oracle. I asked on stackoverflow (Optimal way to DELETE specified rows from Oracle) and the provided answer is orders of magnitude faster than the "obvious" way you and I would go with (delete from...).

My suggestion would therefore be something like:

CREATE TABLE PURGE_IDS NOLOGGING
AS
    SELECT s.ID FROM speeds s
    JOIN limitedzones lz ON ST_Within(s.geometry,lz.geometry)
    WHERE s.speed = lz.speed;

DELETE FROM speeds WHERE ID IN (SELECT ID FROM PURGE_IDS);

DROP TABLE PURGE_IDS;

Of course, this is assuming that PostGIS gets the same speedboost from that as Oracle gets. The problem then becomes making the SELECT faster which I can't help with (you may want to try the PostGIS mailing list for that).

I'd suggest testing the speed by running each of the commands seperately and timing them.


If that doesn't work, you may wish to ask on stackoverflow.com a purely postGreSQL question akin to my Oracle one and treat the SELECT as a different problem.

share|improve this answer
    
Yea I was thinking along similar lines myself of trying a temp table and filling it with ID's, I'm playing this morning with a stored proc (and some for loops) to collect ID's into a set and delete that way, will let you know how I get on. –  shawty May 1 '12 at 10:42
add comment
  • Are you sure you have spatial indexes on the speeds and limitedzones tables?

  • Have the tables been analyzed (gather statistics for the indexes to work efficiently)?

  • If you have a spatial indexes, is st_within using them? As far as I remember, it does not, so you can make sure the spatial index is used by adding the clause that requires BBOX intersection:

    DELETE FROM speeds s
    USING limitedzones lz
    WHERE s.geometry && lz.geometry and ST_Within(s.geometry,lz.geometry)
    AND s.speed = lz.speed
    
share|improve this answer
    
Yes 100% sure both tables have spatial indexes, but not sure if ST_Within is using them, will give the BBox way a try, but I always thought that the "geom && geom" syntax only worked correctly if both the BBoxes identically matched? Will also try to analyse, because not to sure on the status of those either. Tks. –  shawty May 1 '12 at 10:41
    
&& is the operation for intersection. For a geometry to be within another geometry, it is necessary for their geometries to intersect. –  diciu May 1 '12 at 10:49
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.