Finding lat/longs within/nearest to polygon in SQL Server

Question

I'm a beginner using SQL Server 2012. I'd like to determine if a set of lat/longs from table A in one database fall within a polygon in table B of another database; or else the closest polygon to those lat/longs. I understand this has been asked before, but I could use some guidance on this unique scenario.

Table A contains two sets of location data - one set is one column of geometry created using a first set of lat/longs; the second set of location data is comprised of two columns containing lat/long geography values of a second set of lat/longs. Hope that makes sense. Essentially three columns: Geometry, Latitude, Longitude

Table B contains the polygons in geometry format.

I've been able to do a spatial join using the geometry column from Table A on the geometry column of Table B using:

SELECT  
    TableB.ID
    ,TableA.*

FROM    
    TableB JOIN TableA ON TableA.geometry_column.STWithin(TableB.geometry_column)=1

Now I'm stuck. My questions are:

How do I match the geography lat/longs from Table A to the geometry column of Table B? I know I need to convert, but which way? Do I create a temporary table with my converted data?
I'm assuming I can use STIntersect or STWithin to find the lat/longs in the polygon; is there an operation to find the nearest polygon to the lat/longs?
How can I embed different selections within my query in order to spit out a single table with my results grouped by TableB ID? Ideally I'd like to see TableB IDs with corresponding TableA Geometry, Table A Lat/Longs within, and Table A lat/longs nearest to.

Hope this all makes sense. This is a five-minute manual operation in ArcGIS. I'd like to know how to do it in SQL. Hopefully someone is willing to take a crack at it! I know I have a lot of reading to do. Cheers, and thank you for all the info on here that has helped me so far.

-CM

cungiderm cungiderm · Accepted Answer · 2016-08-02T07:15:44

To answer your question:

Your query is almost correct, just change STWithin to STIntersects. With this, all points in table A that is either within and on the boundary of polygon in table B is returned.
Yes, you are correct. So to find nearest polygon, you need nearest neighbour query.

SELECT TableA.*, fnc.id FROM TableA CROSS APPLY ( SELECT TOP 1 id FROM TableB WHERE TableA.Location.STDistance(TableB.Polygon) IS NOT NULL ORDER BY TableA.Location.STDistance(TableB.Polygon) ASC ) fnc;
You will have to do step 1 and 2 separately and gather it in a staging table.

I hope this helps. :)

EDIT: Almost forgot. The nearest neighbours query is from https://alastaira.wordpress.com/2012/02/22/updating-a-sql-server-table-with-nearest-neighbours-optimised-for-sql-server-2012-sql-azure/ I just modify the query to match your schema.

Finding lat/longs within/nearest to polygon in SQL Server

1 Answers