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_Transforms does not make a noticable difference. I see a performance of 1 point processed per 10-15 seconds.