0

I have 2 queries that I would like to make work together:

1) one query sums the number of geometry points within a certain distance of another and returns results only where the count is greater 6 points;

2) one query returns the unique ID for all points within that distance (without a count and so also without a minimum number of records)

I would like to generate a single query that returns the new_ref from table t2 for all (and only) the records that are summed in the first query. (Ideally the output would be as columns in a single row, but at the moment I can’t even get the records listed in a single column against multiple rows – so this is my first aim and I could leave the pivoting bit until later).

Obviously, the system is identifying the records to count them, so I would think it should be possible to ask which records they are…

Adding in the sum statement to the second query nullifies the results. Should I be structuring this as a sub-query and, if so, how would I do this?

Query 1 is:

DECLARE @radius as float = 50   
SELECT 
  t1.new_ref,
  t1.hatrisref,
  SUM
  (CASE WHEN t1.geolocation.STDistance(t2.Geolocation) <= @radius
   THEN 1 Else 0
   End) Group size'    
FROM table1 as t1,
     table1 as t2  
WHERE
  [t1].[new_ref] != [t2].[new_ref] 
GROUP BY
  [t1].[new_ref],
  [t1].[hatrisref]
HAVING
  SUM(CASE WHEN
    t1.geolocation.STDistance(t2.Geolocation) <= @radius
    THEN 1 Else 0
  End) >5
ORDER BY
  [t1].[new_ref],
  [t1].[hatrisref]

query 2 is:

DECLARE @radius as float = 50
SELECT 
  t1.hatrisref,
  t1.new_ref,
  t2.new_ref
FROM table1 as t1,
     table1 as t2  
WHERE
  [t1].[new_ref] != [t2].[new_ref] 
  and 
  t1.geolocation.STDistance(t2.Geolocation) <= @radius
GROUP BY
  [t1].[new_ref],
  [t1].[hatrisref],
  t2.new_ref
ORDER BY
  [t1].[hatrisref],
  [t1].[new_ref],
  t2.new_ref
Richard Neish
  • 8,414
  • 4
  • 39
  • 69
somanyquestions
  • 1
  • Welcome to Stack Overflow. I have edited the formatting on your question. You can take a look at https://stackoverflow.com/editing-help for more suggestions on improving the readability of your questions. Have fun! – Richard Neish Oct 08 '14 at 15:57

1 Answers1

0

Yes, a sub-query would work:

SELECT ...
FROM table1 as t1,  table1 as t2  
WHERE t1.new_ref != t2.new_ref 
  and t1.geolocation.STDistance(t2.Geolocation) <= @radius
and 5 < (select count(*)
         from table1 as t3
         WHERE t1.new_ref != t3.new_ref 
         and t1.geolocation.STDistance(t3.Geolocation) <= @radius
        )

See this SQL Fiddle for a simplified example

Darius X.
  • 2,886
  • 4
  • 24
  • 51
  • Thanks for teh answer Darius - this hsould work but for some reason I am having trouble with the query not working with my data at the moment. I am looking into it and will post again when I have solved the problem – somanyquestions Oct 14 '14 at 14:06
  • I was getting an error message regarding using outer references in the **GROUP BY** clause, but when I changed the **GROUP BY** to reference t3 instead of t1, this runs. However, I am not getting any results returned when I would expect to. I am still working on what the issue is... – somanyquestions Oct 14 '14 at 14:22
  • Sorry cut/paste. Actually the group by is not required. Also, the WHERE clause of the nested query should also use t3. – Darius X. Oct 14 '14 at 19:10
  • In SQL Server, you can count and then compare that. I edited the query and also linked to a SQLFiddle that demonstrates the concept. – Darius X. Oct 15 '14 at 19:38