CODEWITHSUNDEEP
sqlsql-servert-sqlgeospatialgeography
Petr Mánek
Petr Mánek

Reputation: 1066

Geographic data on Microsoft SQL Server

I have a SQL Server table with geographical points of type [latitude, longitude]. Currently, I have only few points, however, over time, I expect my table to grow significantly.

Later, I will need to query my table by proximity to another point. Is there any standardized way to do this efficiently? Some kind of geospatial index for example? What are your best practices?

The query I will need is going to look like this in pseudocode:

-- lat0, lng0 and radius0 are search parameters
-- distance() is function which computes distance between two points
SELECT id, lat, lng 
FROM myTable 
WHERE distance(lat, lng, lat0, lng0) < radius0

Upvotes: 0

Views: 325

Answers (2)

duffn
duffn

Reputation: 3760

To add to alroc's answer, you can alternatively create a computed persisted column with an index.

ALTER TABLE my_table ADD geo AS GEOGRAPHY::Point (
        latitude
        ,longitude
        ,4326
        ) persisted;
CREATE spatial INDEX SI_my_table__geo ON my_table (geo);

SELECT TOP 5 @point.STDistance(geo) as distance_meters 
    ,id
    ,location_name
    ,latitude
    ,longitude
FROM my_table;

Upvotes: 2

alroc
alroc

Reputation: 28154

As marc_s mentioned, SQL Server 2008+ has geospatial types which you can use. If you want to keep latitude & longitude separate on myTable but also have a geospatial column which represents those coordinates, you can use a computed column (but be aware of possible performance issues resulting from it) so you don't have duplicated data on the table itself.

[latlong] AS ([geography]::Point([Latitude],[Longitude],(4326)))

Then, to find any points within a radius of another point, you can use the STDistance method of the geography type.

declare @radius int = 100000;
declare @p1 geography = geography::STGeomFromText('POINT(-122.33365 47.612033)',4326);
select * from myTable where @p1.STDistance(latlong) <= @radius;

Note that distances are calculated in meters.

Upvotes: 2

Related Questions