I have one dataset A with around 6K point geometries and another dataset B with around 600K point geometries.
I am trying to come up with an efficient way to update dataset A with an integer representing the number of objects from dataset B that are within a certain distance (100 meters) from the point in A.
The strategy I came up with did not complete overnight, so I am thinking I could be doing this more efficiently. (Or maybe this is just that expensive an operation?).
I am running this on my X220 i5 laptop with SSD and 8GB RAM, Ubuntu 12.10, PostgreSQL 9.1, Postgis 2.0.
This is the function I use. nbi.testpoints
is the small dataset, nbi.bridges
is the bigger dataset. Both tables have spatial indexes on them.
CREATE OR REPLACE FUNCTION getnearbycounts() RETURNS VOID
AS
$$
DECLARE
node nbi.testpoints%rowtype;
BEGIN
FOR node IN SELECT * FROM nbi.testpoints
LOOP
UPDATE nbi.testpoints SET closenbicount = (SELECT count(1) FROM nbi.bridges WHERE
ST_Transform(node.geom, 3785) <#> ST_Transform(nbi.bridges.geom, 3785) < 100)
WHERE nbi.testpoints.id = node.id;
RAISE NOTICE 'node % done', node.id;
END LOOP;
END;
$$
LANGUAGE PLPGSQL;
I am doing the transform to a meter-based coordinate system to be able to give a distance threshold in meters, but replacing that with an approximation in degrees and doing away with the two ST_Transform
s does not make a noticable difference. I see a performance of 1 point processed per 10-15 seconds.