geospatial queries in SQL Server

I would like to hear more about this also. Thx!

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 --

I'm very interested in whatever you may find.  I've taken a different approach to these kinds of projects.  For various projects previously, I've batch geocoded addresses using the free geocoder provided by the US Census Bureau (https://www.census.gov/geo/maps-data/data/geocoder.html), then have either used publicly available lookup tables (census tract/block to City District in NYC) or -- at your suggestion -- have used QGIS to join an imported list/table with GIS geo-shapes to query Miami-Dade County districts and similar datasets.

Currently (still) in development, is a Service Interceptor that will geocode addresses using Google's Maps API (which is included as part of the non-profit Google Grant) whenever an address is added or updated.  We can then send that geocoded lat/long to any of the publicly available GIS datasets (typically available from your county or city).  The Service Interceptor will facilitate those lookups and then write back lat/long to the [geo_location] field in [T_ADDRESS] and the data we're interested in to specific keywords/attributes.  I'm hoping to have that ready to demo by TLCC 2019 and happily share.

DGomez

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?

In what language did you code your geocoder of Tessitura Data?

Are you willing to share?