如何在SQL Server中存储Point数据类型？

How do I best store a Point data type in my SQL Server database?

如何在我的SQL Server数据库中最好地存储Point数据类型?

I was thinking of just making it nvarchar, then inserting a point-string like: 100,246 then just splitting the string when I read the value from the DB but is there a way to just insert as a Point?

我正在考虑将其设为nvarchar,然后插入一个点字符串,如:100,246然后只是在从数据库读取值时拆分字符串,但有没有办法只插入一个点?

4 个解决方案

#1

SQL Server did support Point datatype:

SQL Server确实支持Point数据类型:

Point

Create table:

CREATE TABLE [dbo].[PointTest]
(
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [MyPoint] [GEOMETRY] NOT NULL,
)

Insert (SRID = 4326):

插入(SRID = 4326):

INSERT INTO PointTest(MyPoint) values (GEOMETRY::Point(3, -4, 4326))

Select:

SELECT MyPoint.STX X, MyPoint.STY Y FROM PointTest;

SQLFiddle: link

For more information on Spatial Data Types:

有关空间数据类型的更多信息:

Spatial Data

#2

you can define 2 int variable for each dimension. in this way there is no need to split the value you read from DB.

您可以为每个维度定义2个int变量。这样就不需要拆分从DB读取的值。

X int,
Y int

#3

If you are talking about Geographical points, then maybe look at the geography data type for a "round-earth coordinate system" or the geometry data type for a "Euclidean (flat) coordinate system."

如果您正在谈论地理点,那么可以查看“圆形地球坐标系”的地理数据类型或“欧几里德(平面)坐标系”的几何数据类型。

The spatial data capabilities of SQL Server since around 2008 can help with various methods for interrogating spatial data including (but not limited to) point.

自2008年左右以来,SQL Server的空间数据功能可以帮助查询包括(但不限于)点的空间数据的各种方法。

#4

For geography points:

对于地理点:

create table foo (
  id int primary key,
  latlon [GEOGRAPHY]
);

insert into foo values (1, geography::Point(40.7, -74, 4326));

select 
  latlon.Lat as latitude,
  latlon.Long as longitude,
  latlon.ToString() as plaintext
from foo;

#1