A few things, first if you're only counting the empty locations within 500 feet, just select the empty locations, and use
sum(isEmpty). isEmpty should be a bool:
ALTER TABLE locations_list ALTER TYPE isEmpty bool; This is what your select should look like,
SELECT a.pin, count(*)
FROM locations_list a
JOIN locations_list b
AND a.pin != b.pin
GROUP BY a.pin;
Second, you're casting both
b.geom to geography?
If they're not already geography just use geometry. Check ST_Dwithin In fact, it's likely faster to cast to geometry, if needed, to do this.
For Geometries: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.
That said, if you're going to play around with this, you may want to check out the last argument,
For geography units are in meters and measurement is defaulted to use_spheroid=true, for faster check, use_spheroid=false to measure along sphere.
Also when you say you have an index, do you have a btree index on
a.pin, and a GIST index on
locations_list.geom? You should also consider
locations_list.geom. Just to make sure, did you
VACUUM ANALYZE after you loaded the data?
It may be worth stressing this grows exponentially. So it's
count(locations_list) ** 2 . If you've got 15k rows, that's 225M comparisons: it's not going to be fast. Look at cutting the set in half first (if possible). That's how this is normally done.