spatial data and sql server : very slow queries

Question

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 ?

Check Query Spatial Data for Nearest Neighbor. There must be a Point column in the table with a spatial index and the query must be written in a specific way. Without a spatial index all spatial databases will be slow — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 7, 2018 at 12:14

EzLo · Accepted Answer · 2018-05-07 11:54:22Z

4

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

answered May 7, 2018 at 11:54

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Panagiotis Kanavos Over a year ago

This probably won't use the spatial index based on the docs. There's no need to filter by the distance when the query orders by the distance and selects the TOP 1 result

EzLo Over a year ago

@PanagiotisKanavos I believe the engine is smart enough to figure out that ORDER BY 2 ASC actually means the STDistance() function. It can be explicitly set if otherwise tho. If you don't filter you might get results with distance higher than what you want.

Panagiotis Kanavos Over a year ago

No it isn't, check the docs. This code is similar to the example used for a query that can't use a spatial index. There must be a null-check to filter out rows that may return a NULL distance at least.

EzLo Over a year ago

It uses the index properly, just validated it. The OP will let us know if not otherwise.

Panagiotis Kanavos Over a year ago

Yes, because P.GPS.STDistance(@FilterGPS) < 10 does filter out NULLs.

|

Sameer · Accepted Answer · 2018-05-07 12:07:19Z

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.

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

edited May 7, 2018 at 12:07

answered May 7, 2018 at 11:53

Sameer

4014 silver badges12 bronze badges

2 Comments

Panagiotis Kanavos Over a year ago

This isn't an answer.

Panagiotis Kanavos Over a year ago

Post all that in the answer, not the comments. Check EzLo's answer

Collectives™ on Stack Overflow

spatial data and sql server : very slow queries

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related