Obtain the coordinates within the geometry region range of sqlserver through the region coordinates.

Sqlserver currently supports geospatial fields, that is, geometryAPI address: msdn. microsoft. the comzh-cnLIBRARYcc280487.aspx creates the table and geometry fields as well as the insert point, line, and polygon SQL as follows: IFOBJECT_ID (dbo. spatialTable, U) ISNOTNULLDROPTABLEdbo. sp

Sqlserver currently can support geospatial fields, that is, geometry API address: http://msdn.microsoft.com/zh-cn/LIBRARY/cc280487.aspx to create a table and geometry field and insert point, line, polygon SQL: IF OBJECT_ID (dbo. spatialTable, U) is not null drop table dbo. sp

Sqlserver currently supports geospatial fields, that is, geometry.

API address:

Http://msdn.microsoft.com/zh-cn/LIBRARY/cc280487.aspx

The SQL statement for creating tables and geometry fields, as well as insertion points, lines, and polygon is as follows:

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL     DROP TABLE dbo.SpatialTable;GOCREATE TABLE SpatialTable     ( id int IDENTITY (1,1),    geom geometry,     adress varchar );GOINSERT INTO SpatialTable (geom)VALUES (geometry::STGeomFromText('POINT (20 180)', 4326));INSERT INTO SpatialTable (geom)VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 4326));INSERT INTO SpatialTable (geom)VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 4326));GO

Ps: 4326 is a space reference identifier (SRID). Generally, the value is 0 or 4326.

The geom field in the saved table is as follows:

Query statement

If we store many coordinate points in the database

Select the address in the range of the circular area, that is, all points from the center to the radius.

DECLARE @g geometry;set @g = geometry::STGeomFromText('POINT(104.12765 30.60445)', 4326)SELECT address,geom.STY,geom.STX from SpatialTable  where geom.STDistance(@g)<=0.005

PS: diatance is measured in miles.

Query Result

Select Polygon

DECLARE @g geometry;set @g = geometry::STGeomFromText('POLYGON ((104.12189573049204 30.608145728994504,104.12223905324595 30.60282680842528,104.13262456655161 30.603122311674902,104.13176625966685 30.610066378528995,104.12189573049204 30.608145728994504,104.12189573049204 30.608145728994504))',4326)SELECT address  from  SpatialTablewhere    geom.STIntersects(@g)=1

Query Result