geospatial queries in SQL Server

Hi Tom -

There's a decent tip here that explains how to find the nearest neighbor, which I hope might get you started:  docs.microsoft.com/.../query-spatial-data-for-nearest-neighbor

Here's the query I worked out on our Tessitura system.  We don't have much of anything in the 'geo_location' field but if you do, this might get you started as well.  It finds the top 7 closest matches - I imagine you'll want to tinker with that.

USE [impresario]
GO

DECLARE @g geography = 'POINT(-71.057083 42.361145)'; -- Boston MA coordinates

SELECT TOP(7) geo_location.ToString(), city
FROM t_Address
WHERE geo_location.STDistance(@g) IS NOT NULL
ORDER BY geo_location.STDistance(@g);

Hi Tom -

There's a decent tip here that explains how to find the nearest neighbor, which I hope might get you started:  docs.microsoft.com/.../query-spatial-data-for-nearest-neighbor

Here's the query I worked out on our Tessitura system.  We don't have much of anything in the 'geo_location' field but if you do, this might get you started as well.  It finds the top 7 closest matches - I imagine you'll want to tinker with that.

USE [impresario]
GO

DECLARE @g geography = 'POINT(-71.057083 42.361145)'; -- Boston MA coordinates

SELECT TOP(7) geo_location.ToString(), city
FROM t_Address
WHERE geo_location.STDistance(@g) IS NOT NULL
ORDER BY geo_location.STDistance(@g);

Heather Sheridan (Past Member)

Cool!  I'm actually interested in working with geography Points for addresses of schools.  I can see putting this information in the T_Address.geo_location. Then I want to Join to geography polygons for counties and political districts. 

It appears that SQL Server provides a .STWithin() function. I'm trying to figure out:

1. Will .STWithin() take a geography point and determine if it is within a geography polygon?
   1. From the SQL server documentation, I think the answer is yes.
2. What is the best way to store geography polygons in Tessitura?
   1. Can this be stored as a polygon in the T_Address.geo_location the field that already exists in Tessitura?
      1. Then maybe I create Constituents for county or political district? with their polygon in the geolocation of their primary address of a special address type?
         1. Or is that taking things too far?
   2. Or are there different types of geography columns that I will need to use for geography polygons?
   3. I would ultimately like to use geographic polygons in the typical Tessitura ways.
      1. Tessitura Lists Criteria
         1. Imagine looking up donors who are in politicians districts... 
         2. Finding Donors near to or closest to schools?
      2. Query Items
         1. Return County based on the geocoding of an address
         2. Return a Political address based on a geocoding of a constituents primary address
      3. Tessitura Analytics
3. Finally, I've been wondering if I have to install any additional software into SQL Server 2016 in order to get the geography features working?
   1. Apparently, in an earlier version of SQL server (say 2008) one had to do some additional software installations to get geographic features working.
   2. I'm on RAMP so I don't know if these features are fully turned on in our databases
      1. I've taken your code above and run it in our RAMP copy of Tessitura and it works without error. 
         1. So, for now, I'm going to conclude that I have what is needed in my Tessitura Database to do this kind of work. :-)

Thoughts?

Anyone else interested in working on these experiments with me?