Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
Hi Nicklas, the first two queries work just fine if I remove ::Geography due to the projection. With the final query it only returns one result. What I need is a list of results with an answer per point. Hopefully this clarifies my question. Thanks for helping, it is much appreciated. –  user611478 Mar 12 '13 at 14:05
    
@user611478 What you seem to query in your example is every point that is within 500000 meters from point with property_r=20. That ca n be 0 points or every point in your table. What do you mean when you say htat you want one such answer for every point? Are your real question "What is the closest point to each of my points"? You are right geograpghy should not have been in the first example, just in the second. –  Nicklas Avén Mar 12 '13 at 14:25
    
In my query I count the number of points within 500000 metres. What I want is a similar list of counts for each property eg Property 1 has 10 properties within 500000, property 2 has 6 properties within 500000 metres and so on. The reason being that instead of having to run the original query multiple times for each property I can get all the results from one query. Thanks again for taking the time to help. –  user611478 Mar 12 '13 at 15:03
    
sorry, now I get you. I was on wrong planet. I will update, it is just to group the last query. –  Nicklas Avén Mar 12 '13 at 20:38
    
Works perfectly! Thank you. –  user611478 Mar 13 '13 at 11:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.