How to alter this PostGIS ST_distance_sphere query to give the answer for all points in the table, not just one?

Question

I have the following query which returns the expected result for a single point. I would like to alter this query to return the answer for every point in the table.

SELECT count(case when(
                   ST_Distance_Sphere(
                       (select the_geom 
                        from brc_property where property_r=20), the_geom) < 500000) 
                   then 1 else null end) 
FROM brc_property

Answer 1

It looks like you are compliating things.

If I understand you right your query could look something like:

SELECT count(*) FROM brc_property a INNER JOIN brc_property b ON
ST_Distance_Sphere(a.the_geom, b.geom)< 500000 where b.property_r=20;

but I think you should do instead:

SELECT count(*) FROM brc_property a INNER JOIN brc_property b ON 
ST_DWithin(a.the_geom::geography, b.geom::geography,500000) where b.property_r=20;

since that would use spatial indexes.

And if you want all combinations of distances:

SELECT count(*) FROM brc_property a INNER JOIN brc_property b ON
ST_DWithin(a.the_geom::geography, b.geom::geography,500000) where 
b.property_r!=a.property_r 
group by a.id;

The best is to group by a unique id, but you can group by the geom instead. THen the bounding box will be used for the grouping. That can be a problem if it would have been polygons or linestrings since they can have different geometries but identical bboxes.

HTH

Nicklas