November 5, 2015 at 11:06 am
I have a table that contains spatial points and the name of the polygon they belong to (geography and varchar columns). I need to write a function to accept a point and determine which polygon contains that point. How can I write the query so that it will search through each polygon (derived from the geography points found in the table) and return the name of the polygon that contains that point?
November 6, 2015 at 3:26 am
See this discussion for how to build polygon from points
Also i'd aggregated polygon expression from points with FOR XML PATH('') rather them loop.
All the rest is rather straightforward.
November 8, 2015 at 1:30 am
If the points in the table are not ordered, or if the polygon's points do not adhere to the rules documented in https://msdn.microsoft.com/en-us/library/bb895267.aspx, or if a geography polygon's points are ordered in a clockwise direction, the functional derivation of a polygon from the table's points may not return what is expected. If there was a need to add a new point to a polygon name within that table, a derivation of a polygon could fail the next time the function/procedure is invoked, and that could in turn cause a search for a point to fail (or return incorrect results). In response to which the points in the table would to be manually reordered or edited, so that the derived polygon is ensured to be a valid polygon. Keep in mind that MakeValid does not necessarily make a valid polygon. It instead makes a valid geography instance, such as a collection of linestrings and points (as opposed to a polygon). You may discover you are only able to offer deterministic searches by instead persisting a polygon as a spatial type (related to its name), as opposed to persisting a set of points and 'its' name.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply