December 6, 2013 at 12:45 pm
Ok,
Been chewing on this for several hours..
I have a closed polygon that coincidentally is in the shape of Iowa 🙂 I have a point that is within the state and a point WELL outside it, but I get weird results that I don't expect when I try to get it to tell me that the point is within the polygon. I'm starting to think I am approaching this in my mind wrong.
Here is some basic code, sorry about the long coordinates data.
DECLARE @g geography,
@pIn geography,
@pOut geography
SET @g = geography::STPolyFromText('POLYGON((-91.119987 40.705402, -91.129158 40.682148, -91.162498 40.656311, -91.214912 40.643818, -91.262062 40.639545, -91.375610 40.603439, -91.411118 40.572971, -91.412872 40.547993, -91.382103 40.528496, -91.374794 40.503654, -91.385399 40.447250, -91.372757 40.402988, -91.385757 40.392361, -91.418816 40.386875, -91.448593 40.371902, -91.476883 40.390968, -91.490158 40.390762, -91.500221 40.405117, -91.527534 40.410126, -91.529449 40.435043, -91.538689 40.441246, -91.533051 40.455399, -91.579224 40.463718, -91.585869 40.484478, -91.616699 40.504833, -91.622375 40.532864, -91.691917 40.551640, -91.689796 40.581165, -91.716812 40.593399, -91.741547 40.609749, -91.946198 40.608234, -92.192993 40.600060, -92.361328 40.599548, -92.646240 40.591438, -92.717621 40.589645, -93.100731 40.584335, -93.370056 40.580482, -93.562691 40.580807, -93.786079 40.578445, -94.017830 40.574024, -94.238159 40.570972, -94.484993 40.574215, -94.639633 40.575756, -94.920364 40.577229, -95.217171 40.581898, -95.382294 40.584335, -95.767204 40.589046, -95.757271 40.620903, -95.767723 40.643116, -95.876335 40.730434, -95.851509 40.792599, -95.846153 40.848331, -95.834114 40.870300, -95.836258 40.901108, -95.837318 40.974258, -95.860611 41.002651, -95.859253 41.035004, -95.878517 41.065872, -95.857986 41.109188, -95.876396 41.164204, -95.859512 41.166866, -95.858910 41.180538, -95.915810 41.194065, -95.921959 41.207855, -95.910690 41.225246, -95.929939 41.302059, -95.910912 41.308472, -95.897301 41.286865, -95.888817 41.301392, -95.942604 41.340080, -95.939766 41.394810, -95.934776 41.462387, -95.952896 41.472393, -96.006607 41.481960, -96.013161 41.493000, -95.996399 41.511524, -95.993675 41.528111, -96.004303 41.536671, -96.049881 41.524342, -96.085548 41.537529, -96.091644 41.563152, -96.080544 41.576008, -96.111015 41.599014, -96.099014 41.654690, -96.119972 41.684105, -96.121910 41.694923, -96.085266 41.704998, -96.099480 41.731575, -96.099030 41.752987, -96.076126 41.791481, -96.135330 41.862633, -96.159676 41.904163, -96.145576 41.924919, -96.147034 41.966267, -96.184921 41.980698, -96.202545 41.996628, -96.235794 42.001270, -96.238426 42.028450, -96.265182 42.048908, -96.284821 42.123463, -96.351860 42.168194, -96.363205 42.214050, -96.337402 42.229530, -96.332352 42.260315, -96.342575 42.282089, -96.368393 42.298031, -96.389473 42.328796, -96.423866 42.349285, -96.411453 42.380924, -96.417320 42.414783, -96.397583 42.441799, -96.395767 42.467407, -96.439087 42.489246, -96.479935 42.517136, -96.489029 42.564034, -96.500633 42.573891, -96.488190 42.580486, -96.512535 42.629761, -96.540855 42.662411, -96.562729 42.668518, -96.626228 42.708359, -96.640396 42.748608, -96.632668 42.776840, -96.600563 42.799564, -96.587334 42.835388, -96.572815 42.834354, -96.555901 42.846668, -96.537201 42.896915, -96.543953 42.913876, -96.514626 42.952393, -96.516838 42.986469, -96.498711 43.012062, -96.519699 43.051521, -96.479263 43.061897, -96.461784 43.075596, -96.460495 43.087887, -96.451195 43.126324, -96.472801 43.209099, -96.486931 43.217926, -96.558289 43.225506, -96.566673 43.239651, -96.559250 43.253281, -96.570404 43.263630, -96.578812 43.290092, -96.540245 43.307678, -96.522575 43.356987, -96.524734 43.384247, -96.557388 43.400749, -96.588791 43.435562, -96.583473 43.481945, -96.597992 43.499874, -96.460136 43.499744, -96.060738 43.498566, -95.866615 43.498978, -95.464493 43.499577, -95.396278 43.500370, -94.920197 43.499413, -94.859573 43.500072, -94.454987 43.498146, -94.246544 43.498993, -93.973717 43.500343, -93.653481 43.500809, -93.500618 43.500534, -93.054184 43.501495, -93.027016 43.501316, -92.557831 43.500294, -92.452995 43.499496, -92.077370 43.499187, -91.730217 43.499603, -91.610954 43.500656, -91.223434 43.500835, -91.235771 43.464710, -91.210785 43.424076, -91.198112 43.370537, -91.176918 43.353970, -91.078369 43.313320, -91.066299 43.280704, -91.068924 43.257919, -91.161224 43.147594, -91.168442 43.082905, -91.159622 43.081200, -91.152084 43.001331, -91.138992 42.925907, -91.093300 42.871452, -91.081902 42.783375, -91.066040 42.744923, -90.999054 42.707066, -90.919281 42.680683, -90.892418 42.678246, -90.745483 42.657005, -90.694664 42.637932, -90.664253 42.571392, -90.639091 42.555714, -90.625580 42.528561, -90.638329 42.509361, -90.651772 42.494698, -90.648346 42.475643, -90.605827 42.460560, -90.563583 42.421837, -90.491043 42.388783, -90.441597 42.360073, -90.427681 42.340633, -90.417984 42.263924, -90.407173 42.242645, -90.367729 42.210209, -90.323601 42.197319, -90.230934 42.159721, -90.191574 42.122688, -90.176086 42.120502, -90.166649 42.103745, -90.168098 42.061043, -90.150536 42.033428, -90.142670 41.983963, -90.154518 41.930775, -90.195839 41.806137, -90.255310 41.781738, -90.304886 41.756466, -90.326027 41.722736, -90.341133 41.649090, -90.339348 41.602798, -90.348366 41.586849, -90.423004 41.567272, -90.434967 41.543579, -90.454994 41.527546, -90.540840 41.525970, -90.600700 41.509586, -90.658791 41.462318, -90.708214 41.450062, -90.779900 41.449821, -90.844139 41.444622, -90.949654 41.421234, -91.000694 41.431084, -91.027489 41.423508, -91.055786 41.401379, -91.073280 41.334896, -91.102348 41.267818, -91.101524 41.231522, -91.056320 41.176258, -91.018257 41.165825, -90.990341 41.144371, -90.957787 41.104359, -90.954651 41.070362, -90.960709 40.950504, -90.983276 40.923927, -91.049210 40.879585, -91.088905 40.833729, -91.092751 40.761547, -91.119987 40.705402))', 4326);
SET @pIn = geography::STPointFromText('POINT( -90.6204165 41.5795478)',4326)
SET @pOut = geography::STPointFromText('POINT( -80.6204165 31.5795478)',4326)
SELECT Poly = @g,
pIn = @pIn,
pOut = @pOut
SELECT DistanceInMetersIn = @g.STDistance( @pIn ),
DistanceInMetersOut = @g.STDistance( @pOut ),
STIntersectsIn = @g.STIntersects( @pIn ),
STIntersectsOut = @g.STIntersects( @pOut )
SELECT STIntersectionIn = @g.STIntersection( @pIn ).ToString(),
STIntersectionOut = @g.STIntersection( @pOut ).ToString()
GO
Here is the second and third result sets and they confuses me immensely.
DistanceInMetersIn DistanceInMetersOut STIntersectsIn STIntersectsOut
---------------------- ---------------------- -------------- ---------------
7863.61743130497 0 0 1
(1 row(s) affected)
STIntersectionIn STIntersectionOut
-------------------------- -------------------------------
GEOMETRYCOLLECTION EMPTY POINT (-80.6204165 31.5795478)
(1 row(s) affected)
As I read that there is a distance of about 7864 meters, this is close to what I would expect, so that's ok. The point outside I would expect a distance as well so that is confusing.. Then we have the intersects, it says that the point inside does NOT intersect but the one outside DOES, this is backed up by the intersection values. WHAT?!?
So am I crazy or am I just not thinking about this from the right direction. In my mind I see a closed polygon, I want to see if that point is inside that polygon, and I'm not seeing that here..
CEWII
December 6, 2013 at 2:59 pm
After MUCH hair being pulled out (which is a problem, see my avatar picture) I finally found the issue..
Thank you Pro T-SQL 2012 Programmer's Guide by Jay Natarajan (et all)
Page 267
HEMISPHERE AND ORIENTATION
You need also to specifiy the right ring orientation. So why is ring orientation so important, and what is the
“right” ring orientation? To answer these questions, you have to ask yet another question: “What is the inside
of a Polygon?” You might instinctively say that the inside of a Polygon is the smallest area enclosed by the
coordinates you supply. But you could end up in a situation where your Polygon should be the larger area
enclosed by your coordinates. If you created a border around the North Pole, for instance, is your Polygon
the area within the border or is it the rest of the Earth minus the North Pole? Your answer to this question
determines what the “inside” of the Polygon really is.
The next step is to tell SQL Server where the inside of the Polygon lies. SQL Server’s geography instance
makes you define your coordinates in counterclockwise order, so the inside of the Polygon is everything that
falls on the left-hand side of the lines connecting the coordinates.
...........
This eliminates any ambiguity from your Polygon definitions.
(All rights remain with the author and I am only claiming fair use)
It seems that my polygon is defined in the clockwise manner which apparently means that my polygon is defining the OUTSIDE of the polygon, this means that the results I was getting make COMPLETE sense now.. It also means that some of my data is defined clock-wise and other is defined counter clock-wise.. Sooooooo I'll have to fix that..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply