SQL Geometry find all points in a radius

问题:

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
(
    @GameId,
    @Name,
    @StarTypeId,
    GEOMETRY::Point(@LocationX, @LocationY, 0),
)

回答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!!!!

回答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