空间数据和SQL Server：非常慢的查询

Currently I am using SQL Server and having issues, i have a table Adress with 12M of addresses; when i look for one addresses; its take 3 or 4 minutes to find it. i have to look for more than 40000 adressess averyday in my table. so Sql server dont help me to resolve this problem. i think about using an other SGBD maybe MongoDB, but i dont have any idea if it will work or not. my goal is to find every query in less than 2 sec.

目前我正在使用SQL Server并遇到问题，我有一个带有12M地址的表Adress;当我寻找一个地址;它需要3或4分钟才能找到它。我必须在我的桌子上每天寻找超过40000次的追踪。所以Sql server不帮我解决这个问题。我认为使用其他SGBD可能是MongoDB，但我不知道它是否会起作用。我的目标是在不到2秒的时间内找到每个查询。

My query :

我的查询：

SELECT TOP 1 adresse 
FROM   (SELECT 
       geography::Point(p.latitude, p.longitude,4326).STDistance(geography::Point(Cast(Replace('28,5259799957275', ',', '.') AS FLOAT)
       , Cast(Replace('28,5259799957275', ',', '.') AS FLOAT), 4326)) AS rn, 
p.adresse 
FROM   [adressespositions] p) ph 
WHERE  ph.rn < 10

My table :

我的桌子：

CREATE TABLE [dbo].[adressespositions](
    [idgeocodage] [int] IDENTITY(1,1) NOT NULL,
    [latitude] [float] NULL,
    [longitude] [float] NULL,
    [adresse] [nvarchar](100) NULL,
    [dateajout] [datetime] NULL,
    [source] [nvarchar](100) NULL,
    [idsource] [int] NULL
) ON [PRIMARY]

the RAM of my server : 128 GB.

我的服务器的RAM：128 GB。

how can i make this query very fast ? or i have to use an other SGBD ?

我怎样才能使这个查询非常快？或者我必须使用其他SGBD？

2 个解决方案

#1

Add a new column with the GPS point in GEOGRAPHY type (instead of recreating it on every SELECT). Then create a SPATIAL INDEX on that column and performance should spike.

使用GEOGRAPHY类型的GPS点添加新列（而不是在每个SELECT上重新创建它）。然后在该列上创建一个SPATIAL INDEX，性能应该会高峰。

ALTER TABLE dbo.adressespositions ADD GPS GEOGRAPHY

UPDATE T SET
    GPS = GEOGRAPHY::Point(T.latitude, T.longitude, 4326)
FROM
    dbo.adressespositions AS T

CREATE SPATIAL INDEX SI_adressespositions_GPS ON dbo.adressespositions (GPS)

Creating the spatial index might take a while if you have many records. Also, a spatial index requires a primary key on that table (will have to create one if you don't have one already).

如果您有许多记录，则创建空间索引可能需要一段时间。此外，空间索引需要该表上的主键（如果您还没有主键，则必须创建一个主键）。

Then you change your select like the following:

然后你改变你的选择如下：

DECLARE @FilterGPS GEOGRAPHY = GEOGRAPHY::Point(
    Cast(Replace('28,5259799957275', ',', '.') AS FLOAT),
    Cast(Replace('28,5259799957275', ',', '.') AS FLOAT), 
    4326)

SELECT TOP 1
    P.adresse,
    Distance = P.GPS.STDistance(@FilterGPS)
FROM
    [adressespositions] AS P
WHERE
    P.GPS.STDistance(@FilterGPS) < 10
ORDER BY
    2 ASC

#2

Instead of using float for latitude and longitude why not change your table structure with spatial datatypes like Geography or Geometry. Then after you can use spatial index on newly created table.

为什么不使用Geography或Geometry等空间数据类型更改表结构，而不是使用float作为纬度和经度。然后，您可以在新创建的表上使用空间索引。

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-spatial-index-transact-sql?view=sql-server-2017

#1