How to get places in radius of a point using SQL Server

   

      I am sure you have seen web applications that let users to draw a random shape on a map then make a search for data only in that area. Like Zillow, you can draw an area on the map, and zillow will return houses for sale in that area. Have you ever wonder how they do that? What kind of language, technology, server do you need to pull that? Well I am not sure what zillow uses but if you like to do that all you need is SQL Server.

      SQL Server can do this since the version 2008, and I am going to focus on how to make a search by a spatial area in this post. City of Columbus in Ohio has a great spatial data library about the city.  I am going to use their Park Facilities data for the demo. Let's say we have a website and we want to show our users what is available around them for entertainment. To be able to make this search, First we need the location of the user. You can use HTML Geolocation feature to get the location of the user and pass it to SQL Server.

     Our user lives in Columbus, OH  and application is passing (40.1, -82.8) for user's coordination to SQL Server. Also user selects 5 miles as radius. So we need to find the coordination and draw a circle around it with 5 miles radius.

     I am going to use a temp table to demo this. First I want to save the location of the spatial objects. I am going to download the list and insert it in my table. I need to convert latitude and longitude to Point spatial type in my table too.

declare @temp as table

(lat float, long float, geog geography , name varchar(50))
insert into @temp (lat, long, name) 

values (-83.06782185,39.96144104, 'Holton Park'),
(40.10595374,-82.88387596,'HOOVER RESERVOIR PARKLAND'),
(40.1039777,-82.88219327, 'HOOVER RESERVOIR PARKLAND'),
(40.10913971,-82.8769412,'HOOVER RESERVOIR PARKLAND'),
(40.10928623,-82.87629968,'HOOVER RESERVOIR PARKLAND'),
(40.11783272,-82.8853883,'HOOVER RESERVOIR PARKLAND'),
(40.14469348,-82.88017223,'HOOVER RESERVOIR PARKLAND'),
(40.11002278,-82.88630708,'HOOVER RESERVOIR PARKLAND'),
(39.92019155,-82.81945647,'INDEPENDENCE PARK'),
(39.920108,-82.81836414,'INDEPENDENCE PARK'),
(39.92070726,-82.81886529,'INDEPENDENCE PARK'),
(39.88256672,-82.98969455,'INDIAN MOUND PARK'),
(39.88247799,-82.98993637,'INDIAN MOUND PARK'),
(39.88278409,-82.98937254,'INDIAN MOUND PARK'),
(39.88289472,-82.9894962,'INDIAN MOUND PARK'),
(39.88217589,-82.98985534,'INDIAN MOUND PARK'),
(39.88215826,-82.98951341,'INDIAN MOUND PARK'),
(39.88215063,-82.98934169,'INDIAN MOUND PARK'),
(39.88220025,-82.98915799,'INDIAN MOUND PARK'),
(39.88218796,-82.98896148,'INDIAN MOUND PARK'),
(39.8828107,-82.99031854,'INDIAN MOUND PARK'),
(40.03818824,-82.93402999,'INNIS PARK'),
(40.03742607,-82.93359652,'INNIS PARK'),
(40.03776786,-82.93362842,'INNIS PARK'),
(40.03838667,-82.93364717,'INNIS PARK'),
(39.93813987,-82.97142492,'KARNS PARK'),
(40.0279455,-82.96736138,'KENLAWN PARK'),
(40.02813507,-82.96715248,'KENLAWN PARK'),
(40.13381819,-82.98738695,'LAZELLE WOODS PARK')

update @temp set geog = geography::Point(lat,long, 4326)

    Now, let's select all rows in this table and see what we have. If you click on Spatial Results, you will see the parks as dots. You may need to zoom in.

    Next, I need to create user's location and draw a circle around with 5 miles radius. I am going to use another spatial function named STBuffer() to draw the circle around user's location point.

STBuffer(meters in float)

   It returns a geography object that represents the union of all points whose distance from a geography instance is less than or equal to a specifies value. We need to convert 5 miles to meters since STBuffer() accepts meters unit. Following code creates the area that we are going to use for the search. I am creating the user's location first (40.1, -82.8) then draw a circle with 5 miles radius around it and save it as @userloc

declare @miles int = 5 / 0.0006213712 

select @miles

declare @userloc geography = geography::Point(40.1, -82.8, 4326).STBuffer(@miles)

select @userloc

Here is the results

    Now what? Well we have the location of the user, we draw 5 miles radius around user's location so we know the area. We have the parks in our table. Now we can make a search if there is any parks are located in this area. To do that, I am going to use another spatial function named STIntersects(). This function returns 1 is a geography instance intersects another geography instance.

Select * from @Temp where @userloc.STIntersects(geog) = 1

      It looks like there are 6 parks close to the user. SQL Server will return this information and you can list the parks as text or you can convert this data to GeoJson and display it on a map in the client side.