Solved

# Geography::STIntersects doesn't contain point

Posted on 2009-04-22
1,535 Views
I've got a table of Geography polygons base on a minimum & maximum lat & lon. When using STIntersects on the polygon for a point lying on the border of the polygon the value returned is false.

This is completely expected behaviour as a line joining two points on the same latitude will use the Great Circle as it's the shortest distance between the two, causing it to arc away from the equator.

There are two obvious solutions:
Use Geometry as the field's datatype, though I'd prefer not to do this as I lose the Geography functionality.
Convert the Geography to a Geometry, and do the comparison. Doing this will lose any indexing benefits.

Is there a 3rd more efficient solution that I've missed out? I think I know what the answer will be, but it'd be nice to get a different perspective.
``````-- Basic are covering british isles

SET @region = Geography::STGeomFromText('POLYGON((

-11 49,

2 49,

2 61,

-11 61,

-11 49

))', 4326);

-- Point on the lower bound of the polygon

SET @point = Geography::STGeomFromText('POINT(

-4.5 49

)', 4326);

SELECT @region, @point,

@region.STIntersects(@point) AS [Geog],

Geometry::STGeomFromText(@region.STAsText(), 4326).STIntersects(Geometry::STGeomFromText(@point.STAsText(), 4326)) AS [Geom]
``````
0
Question by:oobayly

LVL 3

Expert Comment

Are you trying to find the point on the lower bound that is at lat -4.5 or are you trying to determine if -4.5 49 is on the lower bound of your polygon?  You already know the answer of the latter.

To find the point at lat -4.5 on the lower bound of your polygon, intersect that lower bound at -4.5.

What is the practical purpose of what you are trying to do?
0

LVL 15

Author Comment

The latter. The practical reason for this is the following:
I have a set of polygons describing country bounds, so given a point I can tell what country it is. For something like the US / Canada border which follows the 49th parallel, a point midway along the border (49.5N 105W) will appear to be inside the the US rather than Canada.

Adding points along the boundary will reduce the likelihood of errors, but will bloat the data.

Interestingly enough, the execution plan didn't appear to change that much when comparing the Geography search to the casted Geometry search. In both cases 96% of the cost was done on a filter, so either my indexes aren't set up correctly or my concerns about losing indexing benefits are unfounded.
0

LVL 41

Accepted Solution

It seems you would need some special SRID for Canadian border :-)

The geometry substitution is great simplification for borders on parallels but it is more likely exception. Not so rare, of course. I would rather prefer more points (depending on accuracy required) because it is easier for coding.

The casted geometry is not possible to use everywhere. How do you implement these exceptions?
0

LVL 15

Author Comment

Strange, my last post isn't there. Can you give an example of a Geometry that cannot be converted to a Geography? So far I've received no exceptions, however you did make me realise that a Polygon crossing the 180th meridian will cause issues.
Think I'll have to go with the adding points method.
0

LVL 41

Expert Comment

I don't have exact data and/or coordinates but it seems to me some borders which looks as a line on the map (examples are in Africa or even California - Nevada border) could rather use geographical system than pure geometry. But who knows? Of course, the equator and the 180th meridian is a problem. Fortunately, it is far away from my location.

I am just starting to study this new SQL feature and trying to export it to other (not so sopfisticated) database system.
0

LVL 15

Author Closing Comment

Made me think about why Casting a Geography to Geometry was not the way to go.
0

## Featured Post

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…