Fastest way to remove matched points

Question

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.

Answer 1

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.

Answer 2

• 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