CODEWITHSUNDEEP
sql-serverbigdataspatial-query
XXIsmail
XXIsmail

Reputation: 13

spatial data and sql server : very slow queries

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.

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.

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

Upvotes: 1

Views: 1853

Answers (2)

Sameer
Sameer

Reputation: 381

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.

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

Upvotes: 2

EzLo
EzLo

Reputation: 14189

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.

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

Upvotes: 4

Related Questions