通过

Find the nearest bank subsidary using geography datatype in SQL Server 2008

  • 项目

In this example we will explore how to calculate the nearest point of interest of e.g. a business relative to our current location. For example we are at MS Austria subsidiary in Vienna and we want to go to the bank and therefore we are interested where the nearest Raiffeisen Bank subsidiary is. Finally we would like to expand this query and get the three nearest Raiffeisen Bank subsidiaries ranked by the shortest distance.

As in the previous post, we created a table called my_locations in which we stored our locations – e.g. MS Austria. For this example we also created a second table called raiffeisen_filialen where we stored the data for our points of interest - the Raiffeisen Bank subsidiaries – we stored id, name, telephone number, postal code and, of course, the geo coordinates which we found out using MS Virtual Earth (see the previous posting).

Like in the previous example we declared a variable called @ms_at to which we assigned the geo coordinates of MS Austria in Vienna.

DECLARE @ms_at geography;

SET @ms_at = (select geo_coordinates from my_locations l where l.name like '%Microsoft Österreich');

Starting from here we would like to get all Raiffeisen Bank subsidiaries in the range of five km. We can get these using the following query.

select r.name

from raiffeisen_filialen r

where r.geo_coordinates.STIntersects(@ms_at.STBuffer(5000)) = 1

STBuffer (see https://msdn.microsoft.com/en-us/library/bb933965.aspx) enables us to specify a distance in meters and to check whether our point of interest is located within this distance ratio.

The query above returns:

row id

name

1

Favoritenstraße 104

2

Wienerbergstraße 3

3

Richard-Strauss-Strasse 16

Now we would like to know the nearest bank subsidiary referring to our location MS Austria and for the range of 5000 meters from poi. The following query shows us how:

select ranked.name, ranked.distance from

(select to_be_ranked.name, to_be_ranked.distance,RANK() OVER (ORDER BY to_be_ranked.distance) AS 'Rank'

 from

(select near_offices.name, near_offices.geo_coordinates.STDistance(@ms_at) as distance from

(select r.id, r.name, r.geo_coordinates from raiffeisen_filialen r where r.geo_coordinates.STIntersects(@ms_at.STBuffer(5000)) = 1) near_offices) to_be_ranked) ranked

where ranked.Rank = '1'

Here the query returns only the bank subsidiary and the distance which was ranked first. The subsidary is:

row id

name

distance

1

Wienerbergstraße 3

1250,6417936807

Now we want to expand the query to return a selection of three nearest bank subsidiaries in the range of 5000 meter from our location, which is MS Austria.

select ranked.name, ranked.distance from

(select to_be_ranked.name, to_be_ranked.distance,RANK() OVER (ORDER BY to_be_ranked.distance) AS 'Rank'

 from

(select near_offices.name, near_offices.geo_coordinates.STDistance(@ms_at) as distance from

(select r.id, r.name, r.geo_coordinates from raiffeisen_filialen r where r.geo_coordinates.STIntersects(@ms_at.STBuffer(5000)) = 1) near_offices) to_be_ranked)

ranked

where ranked.Rank in( '1', '2', '3')

row id

name

distance

1

Wienerbergstraße 3

1250,6417936807

2

Richard-Strauss-Strasse 16

2953,38884146169

3

Favoritenstraße 104

4767,92534842965

This information can now be further processed as needed, e.g. displayed using MS Virtual Earth or combined with other location data.

 

Near Raiffeisen Subsidaries

Comments