SQL Geometry find all points in a radius

Question

I am fluent in SQL but new to using the SQL Geometry features. I have what is probably a very basic problem to solve, but I haven't found any good resources online that explain how to use geometry objects. (Technet is a lousy way to learn new things...)

I have a collection of 2d points on a Cartesian plane, and I am trying to find all points that are within a collection of radii.

I created and populated a table using syntax like:

Update [Things] set [Location] = geometry::Point(@X, @Y, 0)

(@X,@Y are just the x and y values, 0 is an arbitrary number shared by all objects that allows set filtering if I understand correctly)

Here is where I go off the rails...Do I try to construct some sort of polygon collection and query using that, or is there some simple way of checking for intersection of multiple radii without building a bunch of circular polygons?

Addendum: If nobody has the answer to the multiple radii question, what is the single radius solution?

UPDATE

Here are some examples I have worked up, using an imaginary star database where stars are stored on a x-y grid as points:

Selects all points in a box:

DECLARE @polygon geometry = geometry::STGeomFromText('POLYGON((' 
+ CAST(@MinX AS VARCHAR(10)) + ' '  + CAST(@MinY AS VARCHAR(10)) + ',' 
+ CAST(@MaxX AS VARCHAR(10)) + ' '  + CAST(@MinY AS VARCHAR(10)) + ', ' 
+ CAST(@MaxX AS VARCHAR(10)) + ' '  + CAST(@MaxY AS VARCHAR(10)) + ',' 
+ CAST(@MinX AS VARCHAR(10)) + ' '  + CAST(@MaxY AS VARCHAR(10)) + ',' 
+ CAST(@MinX AS VARCHAR(10)) + ' '  + CAST(@MinY AS VARCHAR(10)) + '))', 0);

SELECT  [Star].[Name]           AS [StarName],
        [Star].[StarTypeId]     AS [StarTypeId],        
FROM    [Star]
WHERE   @polygon.STContains([Star].[Location]) = 1

using this as a pattern, you can do all sorts of interesting things, such as defining multiple polygons:

WHERE   @polygon1.STContains([Star].[Location]) = 1
OR @polygon2.STContains([Star].[Location]) = 1
OR @polygon3.STContains([Star].[Location]) = 1

Or checking distance:

WHERE [Star].[Location].STDistance(@polygon1) < @SomeDistance 

Sample insert statement

INSERT [Star]
(
    [Name],
    [StarTypeId],
    [Location],
)
VALUES
(
    @Name,
    @StarTypeId,
    GEOMETRY::Point(@LocationX, @LocationY, 0),
)

Outburst

Answer 1

This is an incredibly late answer, but perhaps I can shed some light on a solution. The "set" number you refer to is a Spatial Reference Indentifier or SRID. For lat/long calculations you should consider setting this to 4326, which will ensure metres are used as a unit of measurement. You should also consider switching to SqlGeography rather than SqlGeometry, but we'll continue with SqlGeometry for now. To bulk set the SRID, you can update your table as follows:

UPDATE [YourTable] SET [SpatialColumn] = GEOMETRY.STPointFromText([SpatialColumn].STAsText(), 4326);

For a single radius, you need to create a radii as a spatial object. For example:

DECLARE @radiusInMeters FLOAT = 1000; -- Set to a number in meters
DECLARE @radius GEOMETRY = GEOMETRY::Point(@x, @y, 4326).STBuffer(@radiusInMeters);

STBuffer() takes the spatial point and creates a circle (now a Polygon type) from it. You can then query your data set as follows:

SELECT * FROM [YourTable] WHERE [SpatialColumn].STIntersects(@radius);

The above will now use any Spatial Index you have created on the [SpatialColumn] in its query plan.

There is also a simpler option which will work (and still use a spatial index). The STDistance method allows you to do the following:

DECLARE @radius GEOMETRY = GEOMETRY::Point(@x, @y, 4326);
DECLARE @distance FLOAT = 1000; -- A distance in metres   
SELECT * FROM [YourTable] WHERE [SpatialColumn].STDistance(@radius) <= @distance;

Lastly, working with a collection of radii. You have a few options. The first is to run the above for each radii in turn, but I would consider the following to do it as one:

DECLARE #radiiCollection TABLE
(
    [RadiusInMetres] FLOAT,
    [Radius] GEOMETRY
)

INSERT INTO #radiiCollection ([RadiusInMetres], [Radius]) VALUES (1000, GEOMETRY::Point(@xValue, @yValue, 4326).STBuffer(1000));
-- Repeat for other radii

SELECT
    X.[Id],
    MIN(R.[RadiusInMetres]) AS [WithinRadiusDistance]
FROM
    [YourTable] X
    JOIN
    #radiiCollection RC ON RC.[Radius].STIntersects(X.[SpatialColumn])
GROUP BY
    X.[IdColumn],
    R.[RadiusInMetres]

DROP TABLE @radiiCollection;

The final above has not been tested, but I'm 99% sure it's just about there with a small amount of tweaking being a possibility. The ideal of taking the min radius distance in the select is that if the multiple radii stem from a single location, if a point is within the first radius, it will naturally be within all of the others. You'll therefore duplicate the record, but by grouping and then selecting the min, you get only one (and the closest).

Hope it helps, albeit 4 weeks after you asked the question. Sorry I didn't see it sooner, if only there was only one spatial tag for questions!!!!

Raver

Interesting 2nd solution, I need to play with that a little and think about it. Two minor points: I am pretty sure that unless you are working on a curved surface, you do not want to be using GEOGRAPHY 4326, which is a non Euclidean surface. This is a flat space problem, so using GEOMETRY is probably the correct choice. Second, in your sample you suggest using a sql temp table, when it would be much better (faster) to use a table variable. Temp tables write to disk, table vars are in memory. – Outburst

@MadTigger I couldn't agree more on using table variables, but if I'm honest I didn't spend any time on optimisation, more the theory to help you on your way - given that having been 4 weeks you may have already solved it! :-) Forgive the assumption on 4326, again, if you're working on a flat-space problem you're completely right to not use 4326 and to use Geometry. I'm just so used to people using Geometry as a cop-out for actual "Earth" problems when they should use Geography and your question wasn't clear on that matter. The assumption habit is one I should kick. – Raver

Answer 2

Sure, this is possible. The individual where clause should be something like:

DIM @Center AS Location
-- Initialize the location here, you probably know better how to do that than I.
Dim @Radius AS Decimal(10, 2)
SELECT * from pointTable WHERE sqrt(square(@Center.STX-Location.STX)+square(@Center.STX-Location.STX)) > @Radius 

You can then pile a bunch of radii and xy points into a table variable that looks like like:

Dim @MyCircleTable AS Table(Geometry Circle) 
INSERT INTO @MyCircleTable (.........)

Note: I have not put this through a compiler, but this is the bare bones of a working solution.

Other option looks to be here: http://technet.microsoft.com/en-us/library/bb933904.aspx

And there's a demo of seemingly working syntax here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6e1d7af4-ecc2-4d82-b069-f2517c3276c2/slow-spatial-predicates-stcontains-stintersects-stwithin-?forum=sqlspatial

The second post implies the syntax:

SELECT Distinct pointTable.* from pointTable pt, circletable crcs
WHERE crcs.geom.STContains(b.Location) = 1

Ables

This is how you would do it if you were going to use the basic SQL data types. However, I am asking about using the SQL geometry objects specifically because they use quadtrees or other spacial indexing. Your solution will fall completely apart if you wanted to search a db that had a few hundred thousand points in it, where the an object that uses spatial partitioning would have no problem at all. It gets even worse worse with your solution as i add additional radiai, as I would have to check the distance to each point in the db for each radius I was going to look at. Thanks for trying, though – Outburst

The server isn't magic, you're going to be doing a huge number of calculations anyways. You're going to want a data warehouse or calculated field if these are huge tables and server load / uptime are important. That said, solution updated to contain a SQL-Native possibility. – Ables

That isn't correct. The point of spatial indexing (BSP trees, quadtrees, etc.) is to avoid having to do all the math by quickly pruning the search space down to relevant data. Spacial indexing, when properly used is very, very fast.That being said, the links you found were very helpful. I'm going to write up and post a synopsis of how to do this for future readers. Thanks for the assistance. – Outburst