Refactoring a SQL Script

Question

I've written this SQL Script:

    DECLARE @location geography  
    DECLARE @radius int  

    SET @location = (SELECT Location FROM Hydrants WHERE HydrantId = 2)  
    SET @radius = (SELECT Radius FROM Hydrants WHERE HydrantId = 2)  

    SELECT * 
    FROM Sites 
    WHERE @location.STDistance(location) < @radius 
    ORDER BY SiteId ASC

I did this to refactor it

SELECT * 
FROM Sites, Hydrants 
Inner Join Hydrants.Location.STDistance(Sites.Location) < Hydrants.Radius  
WHERE Hydrants.HydrantId = 2 
ORDER BY Sites.SiteId ASC;

but without luck.

Any advice will be welcome.

What is the problem? My mind reading device is on the fritz today. — JohnFx
– JohnFx, Commented Feb 23, 2012 at 20:24
Usually it's a good idea to include the specific RDBMS you're using. Is this SQL Server, if so, what version? — Dan
– Dan, Commented Feb 23, 2012 at 20:25
@DanNewhouse I think only SQL Server has the geography data type, starting with 2K8. — Yuck
– Yuck, Commented Feb 23, 2012 at 20:34

Julien May · Accepted Answer · 2012-02-23 20:58:32Z

2

Dont know the geographic features of sql 2008 but when seeing your code, the following might work:

SELECT
    Sites.*
FROM Sites
INNER JOIN Hydrants
    ON Hydrants.Location.STDistance(Sites.location) < Hydrants.Radius
WHERE Hydrants.HydrantId = 2 
ORDER BY Sites.SiteId ASC;

edited Feb 23, 2012 at 20:58

answered Feb 23, 2012 at 20:44

Julien May

2,06115 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

toy4fun Over a year ago

Thanks but this gives me both sites and hydrants in the same line. I only need the sites in the given radius from the hydrants.

Joachim Isaksson · Accepted Answer · 2012-02-23 20:52:52Z

0

I've never worked with the geography types, but this is the correct syntax of a join that does the same as your original query;

SELECT Sites.*
FROM Sites 
JOIN Hydrants 
  ON Hydrants.Location.STDistance(Sites.Location) < Hydrants.Radius  
WHERE Hydrants.HydrantId = 2 
ORDER BY Sites.SiteId ASC;

edited Feb 23, 2012 at 20:52

answered Feb 23, 2012 at 20:43

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Collectives™ on Stack Overflow

Refactoring a SQL Script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related