Geography::STIntersects doesn't contain point

Posted on 2009-04-22
Last Modified: 2012-05-06
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]

Open in new window

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?
    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.
    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?
    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.
    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.
    LVL 15

    Author Closing Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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…
    This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now