SQL Geometry find all points in a radius

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 metersDECLARE @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 radiiSELECT    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!!!!

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 crcsWHERE crcs.geom.STContains(b.Location) = 1