locked
How do you create a buffer of a geometry in SQL Server 2012? RRS feed

  • Question

  • Hello,

    I am new to SQL Server 2012 and spatial data. I built a geometry object of coordinates from a polygon that I stored in a database.  The code is below:

    DECLARE @digb GEOMETRY;
    SET @digb = GEOMETRY::STGeomFromText('POLYGON ((-79.927138 40.362654, -79.91883 40.364224, -79.915966 40.361067, -79.917897 40.359266, -79.927398 40.360142, -79.927138 40.362654))', 4269);

    I am using SRID 4269 (Latitude/Longitude NAD83).  I have no issues getting this geometry to load properly when the query is executed. (shown below)

    My problem is that I am unable to create a valid buffer of 150 feet on this geometry.  I have tried using these 3 statements below (I assumed BufferWithCurves() would be the one that would work); however, ALL of them returned to an invalid buffer of the polygon (shown below):

    -     DECLARE @digbb GEOMETRY = @digb.STBuffer(2);
    -     DECLARE @digbb GEOMETRY = @digb.BufferWithCurves(2)
    -     DECLARE @digbb GEOMETRY = @digb.BufferWithTolerance(@BufferSize,250.0,1);

    

    Does anyone have any idea of how creating this buffer of a geometry object (polygon mentioned above) in SQL Server 2012? 

    Thanks in advance for any help.
    Nick


    • Edited by NickWM Thursday, September 25, 2014 9:03 PM updated title
    Thursday, September 25, 2014 9:03 PM

Answers

  • Hi Nick,

    There's a couple of things you need to do. First, SQL Server has two spatial data types, geometry and geography. When using the spatial library, geometry objects don't take SRID into account vis-à-vis unit of measure. It's for your reference in this case. They deal with arbitrary units for calculation (i.e. Euclidean geometry), that is, geometry object wouldn't see these as latitudes and longitudes but as arbitrary unit. Geography would see this as a polygon on the earth using latitudes and longitudes.

    Note that if you present these coordinates to a map program that does, itself, take the numbers as latitudes and longitudes, the map program will render them correctly. But to do calculations like distance, buffering of a certain number of feet or meters, you need geography. And the unit of measure for SRID 4269 is meters, so you need to convert your 150 feet to meters.

    One more thing. When using a polygon and geography, the points have to be in a specific order (search for "ring order" or "left foot rule" on this forum or http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Spatial_Denali_CTP1.docx ). The example I always use is that this rule determines whether you're trying to describe the country of Iceland or "everywhere on earth that isn't Iceland". This example spatial instance has the ring order wrong for geography type  (note: geometry type doesn't care about ring order), so you can fix it with the ReorientObject method.

    Put this all together and you get (using your example):

    -- use geography type
    DECLARE @digb GEOGRAPHY;
    SET @digb = GEOGRAPHY::STGeomFromText('POLYGON ((-79.927138 40.362654, -79.91883 40.364224, -79.915966 40.361067, -79.917897 40.359266, -79.927398 40.360142, -79.927138 40.362654))', 4269);
    -- fix the ring order
    SET @digb = @digb.ReorientObject();
    --SELECT @digb, @digb.STIsValid();

    -- 0.3048 meter per foot
    DECLARE @meter_per_foot FLOAT = 0.3048;
    DECLARE @digbb GEOGRAPHY = @digb.STBuffer(150*@meter_per_foot);
    --SELECT @digbb, @digbb.STIsValid();

    -- draw a nice picture with both of them
    SELECT @digb UNION ALL SELECT @digbb;

    Hope this helps, write back if you have any questions, Bob


    Thursday, September 25, 2014 10:25 PM
  • You need to do what the error message says and change your database compatibility level to 110 (SQL Server 2012). SQL Server 2008 did not support geography with the wrong ring orientation and  it appears that you have an older database that was moved to SQL Server 2012 without updating the database compatibility mode (it's still at 100). And after you've inserted the rows, make sure you check for and correct the ring orientation with ReorientObject().

    Cheers, Bob

    • Marked as answer by NickWM Monday, September 29, 2014 7:28 PM
    Monday, September 29, 2014 4:41 PM
  • I already answered this on this thread (the post that starts with "let me answer the ReorientObject() one first").

    • Marked as answer by NickWM Tuesday, September 30, 2014 1:31 PM
    Monday, September 29, 2014 8:42 PM

All replies

  • Hi Nick,

    There's a couple of things you need to do. First, SQL Server has two spatial data types, geometry and geography. When using the spatial library, geometry objects don't take SRID into account vis-à-vis unit of measure. It's for your reference in this case. They deal with arbitrary units for calculation (i.e. Euclidean geometry), that is, geometry object wouldn't see these as latitudes and longitudes but as arbitrary unit. Geography would see this as a polygon on the earth using latitudes and longitudes.

    Note that if you present these coordinates to a map program that does, itself, take the numbers as latitudes and longitudes, the map program will render them correctly. But to do calculations like distance, buffering of a certain number of feet or meters, you need geography. And the unit of measure for SRID 4269 is meters, so you need to convert your 150 feet to meters.

    One more thing. When using a polygon and geography, the points have to be in a specific order (search for "ring order" or "left foot rule" on this forum or http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Spatial_Denali_CTP1.docx ). The example I always use is that this rule determines whether you're trying to describe the country of Iceland or "everywhere on earth that isn't Iceland". This example spatial instance has the ring order wrong for geography type  (note: geometry type doesn't care about ring order), so you can fix it with the ReorientObject method.

    Put this all together and you get (using your example):

    -- use geography type
    DECLARE @digb GEOGRAPHY;
    SET @digb = GEOGRAPHY::STGeomFromText('POLYGON ((-79.927138 40.362654, -79.91883 40.364224, -79.915966 40.361067, -79.917897 40.359266, -79.927398 40.360142, -79.927138 40.362654))', 4269);
    -- fix the ring order
    SET @digb = @digb.ReorientObject();
    --SELECT @digb, @digb.STIsValid();

    -- 0.3048 meter per foot
    DECLARE @meter_per_foot FLOAT = 0.3048;
    DECLARE @digbb GEOGRAPHY = @digb.STBuffer(150*@meter_per_foot);
    --SELECT @digbb, @digbb.STIsValid();

    -- draw a nice picture with both of them
    SELECT @digb UNION ALL SELECT @digbb;

    Hope this helps, write back if you have any questions, Bob


    Thursday, September 25, 2014 10:25 PM
  • Bob,

    Wow. Thanks for the fast response. I appreciate your effort to help explain to me the differences between GEOMETRY and GEOGRAPHY.  I am a beginner to the spatial world; however, trying to learn as I go with limited documentation out here online about things.

    Is the "ReorientObject()" step always required?  I am leaning with No simply because if I take that step out I still get the same results. Further, when I tried running this test with a different geography object (of an entire municipality (lots more points making up the polygon) I got returned to me a square box.  Then, when I commented out the "ReorientObject()" line, my second test with the entire municipality worked (as shown below):

    I am currently investigating taking the MapInfo .TAB based file format (currently storing all of our points, lines, and polygon objects) and using free software from MapInfo called "Easy Loader" software to upload the information into SQL Server 2012 database as a spatial data (GEOMETRY) format. I guess this is why I originally assumed I needed to use the GEOMETRY type in my query. 

    From what I am understanding, I think I only need to use the GEOMETRY object to be available on importing my data into the database.  From there, any analysis or spatial querying I want to do, I should extract the points into a GEOGRAPHY type in order to either buffer, perform intersections tests, etc?


    Thanks again for your help. I may be posting some more questions on here as I go, if that is OK w/ you?

    Nick



    • Edited by NickWM Friday, September 26, 2014 2:50 PM add more information and a question for Bob regarding ReorientObject()
    Friday, September 26, 2014 1:45 PM
  • Bob,

    Hello again. I have another question I just now thought of.... 

    Since my spatial data is all uploaded as a GEOMETRY datatype, does that mean I need to convert each GEOMETRY into a GEOGRAPHY object prior to performing any spatial analysis on it?  

    The reason I ask is because the software I use to upload the data into SQL Server 2012 has some options for me to pick and choose if I want (besides GEOMETRY shown in the pic below):

    The "Always Geometry" is what was selected when I uploaded my data up until this point.  Again, I appreciate any insight you may have.  

    Have a good day!

    Nick

    Friday, September 26, 2014 2:02 PM
  • Hi Nick,

    Let me answer the ReorientObject() one first, because it's most crucial. Using ReorientObject everywhere is absolutely not a good thing to do. Getting data from an unknown source, you may get instances where the ring order is wrong or where it's right. You don't want to make the right ones wrong. In your example, I happened to notice (because I keyed it in, ran it, and looked at the spatial results tab; the object took up almost all of the globe) that the order was wrong for that specific instance and visually decided to use it.

    What I usually do as an approximation is use the fact that SQL Server has a FULLGLOBE object and I'm not working with instances over half the globe large. So I instanciate one of those (declare @whole_world geography = STGeomFromText('FULLGLOBE', your_srid_goes_here); ) then compare the STArea of my instance to the area of half the globe. If my instance's area is greater, I'm assume the ring point order is incorrect and use ReorientObject(), otherwise don't. There's exact code for this somewhere in this forum (probably more than once) and also (I think) on my blog.

    If you working with instances that may be close to or more than half the globe large (e.g. the habitat of some of the whale species) then you need to do a visual confirmation. ;-) 

    Hope this helps. More info coming... Bob

    Friday, September 26, 2014 4:09 PM
  • Hi Nick,

    BTW, please do ask questions on the forum, that's what it's for. I only meant "if my explanation in that specific answer was unclear, let me know and I'll clarify". Feel free to start a new thread for each topic because that way, it's might be easier for folks to search on.

    Specific answers:

    I haven't worked directly with the MapInfo loader but if you're working with SQL Server, I assume what they may be doing is looking for "geodesic SRIDs" that SQL Server supports (the ones listed in sys.spatial_reference_systems) and loading those as geography, else geometry. That may be a good thing to do.

    If you want to decide yourself, here's some (short) background.

    Only SQL Server (I think PostGIS might have added it) has separate geography and geometry types. Everyone else always uses "geometry" and gives you (maybe) the ability to convert between SRIDs in the database. SQL Server doesn't have this built-in; you'd need to use a library like PROJ4.

    You can use geometry type to refer to geodesic SRIDs, but it doesn't have a way to "take the area using spatial instances in decimal degrees and come up with meters". So, if you only use it to display on a map, it will display correctly. Or close enough. Same with intersects, intersection and the like. But anything like area, distance, or, in your case buffer using a specific number of meters, you need to use geography.  You could have gotten somewhat similar-looking results with geometry by making your input to STBuffer a sufficiently small fraction, but it would be a guess on your part, not a "150 feet" buffer.

    You can't CAST/CONVERT between geometry and geography because they didn't want people to assume that these would do SRID conversion. However, there is a codeplex library "SQL Server Spatial Tools" (http://sqlspatialtools.codeplex.com/) that has two methods: VacuousGeometryToGeography and vice-versa, that does the equivalent (but optimized) of taking one type, spitting it out in well-known text format, then using it to initialize the other type. Or you can do this in your own code.

    In general, geography is for anything geodesic; geometry is for planar or projected coordinate systems (e.g state plane coordinate systems in US, that don't use Long/Lat). But geography is slightly slower in calculations (like intersects) because it's more complex than simple Euclidian geometry. And folks in other platforms are used to using "geometry" and having the platform do the (sometimes fairly expensive) conversions on-the-fly. Or maybe you don't care about the area, distance, etc.

    Even in the geography type, WKT specifies (Long Lat) not (Lat Long). This is because the OGC standard doesn't distinguish and calls things (X Y), and if you think about it, (X Y) is (Long Lat) not (Lat Long). The SQL Server geography-specific method MakePoint has latitude first as a parameter. OGC or SQL/MM standard methods and property names start with "ST" in SQL Server (e.g. STIntersects); SQL Server-specific methods and properties do not (e.g. MakePoint, ReorientObject).

    Finally, attempting to do spatial calculations with unlike SRIDs (e.g. intersects between SRID 4269 and 4326 instance) returns NULL.  

    One more post coming on this thread with some info/links.

    Cheers, Bob


    • Edited by Bob Beauchemin Friday, September 26, 2014 5:13 PM Added more info
    Friday, September 26, 2014 4:40 PM
  • Hi Nick,

    Just a few links to get you started. Remember that the features have evolved over time (e.g. SQL Server 2008 didn't have fullglobe or ReorientObject(); attempting an instance larger than half a hemisphere caused an error). SQL Server 2012 adds a lot of features over SQL Server 2008.

    Whitepaper on SQL Server and spatial coordinate systems (read this first):
    http://msdn.microsoft.com/en-us/library/cc749633(SQL.100).aspx

    Anything these folks post is good (not by any means a complete list):
    Ed Katibah (aka Spatial Ed) - Blog: http://blogs.msdn.com/b/edkatibah/
    Isaac Kunen - Blog: http://blogs.msdn.com/b/isaac/
    Alastair Aitchison - Blog: http://alastaira.wordpress.com/ Book: http://www.apress.com/9781430234913

    Nice starter blog series on SQL Server (2008) and spatial:
    http://blogs.msdn.com/b/davidlean/archive/2008/11/01/sql-2008-spatial-samples-part-1-of-9-how-to-learn-sql-spatial.aspx

    My blog: http://www.sqlskills.com/blogs/bobb/

    Cheers, Bob

    Friday, September 26, 2014 5:03 PM
  • Bob,

    I believe I was on the route of running my own code so that I could have both the GEOGRAPHY and the GEOMETRY within the same table row (that way I have the option of which to use).

    I was going to populate the table by just have 3 columns (Description, GEOMETRY, and GEOGRAPHY)

    Then, my INSERT INTO the SQL table would be something like:

    SELECT 'Description Test', geography::STGeomFromText('POLYGON ((-75.850022 40.32459,-75.85036 40.324647,-75.848542 40.32605,-75.847962 40.325551,-75.849438 40.324573,-75.850022 40.32459))', 4269), ????

    I got confused on what I was use to build my GEOMETRY column.  Again, this would be me manually building the table by this SQL statement (instead of using the Easy Loader) product.

    Any idea if I am on the right track with that?

    • Proposed as answer by Bob Beauchemin Saturday, September 27, 2014 3:47 AM
    • Unproposed as answer by Bob Beauchemin Saturday, September 27, 2014 3:48 AM
    Friday, September 26, 2014 9:10 PM
  • Sorry, I was in the midst of typing and hit "propose as answer" by mistake.

    Sure, easy one. To create your 3rd column, just use geometry::STGeomFromText with the same text as the second column. So:

    SELECT 'Description Test', geography::STGeomFromText('POLYGON ((-75.850022 40.32459,-75.85036 40.324647,-75.848542 40.32605,-75.847962 40.325551,-75.849438 40.324573,-75.850022 40.32459))', 4269), geometry::STGeomFromText('POLYGON ((-75.850022 40.32459,-75.85036 40.324647,-75.848542 40.32605,-75.847962 40.325551,-75.849438 40.324573,-75.850022 40.32459))', 4269) );

    Cheers, Bob

    Saturday, September 27, 2014 3:53 AM
  • Bob,

    I am unfortunately still receiving an error when I try the query mentioned above. I have the following SQL:

    DECLARE @geom GEOMETRY; DECLARE @geog GEOGRAPHY;
    SET @geom = geometry::STGeomFromText('POLYGON ((-75.850022 40.32459,-75.85036 40.324647,-75.848542 40.32605,-75.847962 40.325551,-75.849438 40.324573,-75.850022 40.32459))', 4269);
    SET @geog = geography::STGeomFromText('POLYGON ((-75.850022 40.32459,-75.85036 40.324647,-75.848542 40.32605,-75.847962 40.325551,-75.849438 40.324573,-75.850022 40.32459))', 4269);
    SELECT 'Test', @geom, @geog, NULL;

    The error message occurs on the SET geography line, and SQL reports is as followed: 

    Msg 6522, Level 16, State 1, Line 3
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
    Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.
    Microsoft.SqlServer.Types.GLArgumentException: 
       at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)
       at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)
       at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
       at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

    Any idea as I thought the "4269" gave the SRID desired for the geography object?

    Thanks!

    Monday, September 29, 2014 4:05 PM
  • Bob,

    One more note, when I removed the GEOGRAPHY object all together from the final SELECT statement, I was able to see the GEOMETRY object created without an issue (screen shot attached below).  

    Therefore, the GEOGRAPHY line is definitely the issue for some reason??

    I wanted to pass the information along incase it helped you in any way.

    Thanks,
    Nick

    Monday, September 29, 2014 4:23 PM
  • You need to do what the error message says and change your database compatibility level to 110 (SQL Server 2012). SQL Server 2008 did not support geography with the wrong ring orientation and  it appears that you have an older database that was moved to SQL Server 2012 without updating the database compatibility mode (it's still at 100). And after you've inserted the rows, make sure you check for and correct the ring orientation with ReorientObject().

    Cheers, Bob

    • Marked as answer by NickWM Monday, September 29, 2014 7:28 PM
    Monday, September 29, 2014 4:41 PM
  • Bob,

    I just wrote a long reply and lost it all when I first chose to "Mark as answer". :( 

    To keep it short, I am looking to understand more as to why I need to apply "ReorientObject()" because when I ran the "STIsValid()" function it reported a "1". So since it is valid, why would I still want to ReorientObject, or is that just something that is always performed when storing a GEOGRAPHY object in SQL?

    Thanks,
    Nick

    Monday, September 29, 2014 7:30 PM
  • I already answered this on this thread (the post that starts with "let me answer the ReorientObject() one first").

    • Marked as answer by NickWM Tuesday, September 30, 2014 1:31 PM
    Monday, September 29, 2014 8:42 PM
  • Bob,

    My apologies.  I ended up using this code below (dividing full glob into 1/4 size) because I am only working with the state of Pennsylvania.  The code is below incase anyone reading this thread can use it in the future:


    Thanks again for all your help. I hope I can gather more knowledge as I work with spatial data and I will be sure to start a new thread on any new issues I may encounter.

    Nick

    Tuesday, September 30, 2014 1:31 PM
  • No apology needed, I'm happy to help. There are also references (blogs-whitepapers) in this thread if you want to explore this in more detail.

    Cheers, Bob

    Tuesday, September 30, 2014 8:32 PM