I need to get a count of neighbors within 100m for each point in a set of 250 million points. The result would have the id of each point accompanied by the number of neighbors around them. Can this be done in postgis in a few hours (the table has an indexed geometry column)? How long should it take and what would be the most optimized query?
The query will take quite a while, for sure, w/ 250M points (perhaps too long). I'm assuming here that your points are in a metric coordinate system. Looks like this:
SELECT a.gid, count(*) FROM pts a JOIN pts b ON ST_DWithin(a.geom, b.geom, 100) WHERE a.gid != b.gid GROUP BY a.gid;