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.