locked
Yet another Invalid Geography Instance question RRS feed

  • Question

  • Hi folks,
       I'm using a website (link: http://www.spatialwiki.com/) to visually create some polygons on the planet.

    eg 1. http://www.spatialwiki.com/GetDrawingContent.aspx?id=1348b97f-f235-4265-8550-dd15259fa03b
    eg 2. http://www.spatialwiki.com/GetDrawingContent.aspx?id=7fe0370e-c06e-4009-92e7-19c86f491405

    This site has the ability to export to sql2008 .. awesome!

    Downside is that it exports to a geometry data type :( so when i try and do the following, it fails.

     
    DECLARE @Geog1 AS GEOGRAPHYGEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((-122.435174 37.762628, 
    -122.436342 37.762089, 
    -122.437317 37.761876, 
    -122.438400 37.761699, 
    -122.439449 37.761398, 
    -122.440067 37.761152, 
    -122.441047 37.761051, 
    -122.442562 37.761066, 
    -122.444741 37.759649, 
    -122.444909 37.759172, 
    -122.444700 37.758668, 
    -122.446528 37.758819, 
    -122.446882 37.760432, 
    -122.446343 37.760452, 
    -122.446404 37.761179, 
    -122.446828 37.761802, 
    -122.445279 37.761909, 
    -122.443749 37.763297, 
    -122.443092 37.763508, 
    -122.442931 37.764051, 
    -122.443285 37.764671, 
    -122.443278 37.765350, 
    -122.441265 37.765343, 
    -122.439489 37.766644, 
    -122.438428 37.766768, 
    -122.437348 37.766854, 
    -122.437163 37.767245, 
    -122.435605 37.767333, 
    -122.435174 37.762628))', 4326) 
     
     

    what's the best way to get it from geom to geog? instert it into a temp geom table and then insert that into a geo with the correct SRID?

    -Pure Krome-
    Monday, March 16, 2009 7:08 AM

All replies

  • Exporting lat/long coordinates using the geometry datatype? Hang your head in shame, spatialwiki...!

    The easiest way to get from geometry to geography is via the WKB representation. If you add in a STUnion() to the first point of the geometry it will force the ring orientation to be correct for the geography datatype, and you may as well throw in a MakeValid() for good measure. So you end up with this:

    /* Yucky original geometry instance */  
    DECLARE @Geom AS geometry 
    SET @Geom = geometry::STGeomFromText('POLYGON((-122.435174 37.762628, -122.436342 37.762089, -122.437317 37.761876, -122.438400 37.761699, -122.439449 37.761398, -122.440067 37.761152, -122.441047 37.761051, -122.442562 37.761066, -122.444741 37.759649, -122.444909 37.759172, -122.444700 37.758668, -122.446528 37.758819, -122.446882 37.760432, -122.446343 37.760452, -122.446404 37.761179, -122.446828 37.761802, -122.445279 37.761909, -122.443749 37.763297, -122.443092 37.763508, -122.442931 37.764051, -122.443285 37.764671, -122.443278 37.765350, -122.441265 37.765343, -122.439489 37.766644, -122.438428 37.766768, -122.437348 37.766854, -122.437163 37.767245, -122.435605 37.767333, -122.435174 37.762628))', 4326)  
     
    /* Groovy new geography instance */ 
    DECLARE @Geog geography 
    SET @Geog = geography::STGeomFromWKB(@Geom.STUnion(@Geom.STStartPoint()).MakeValid().STAsBinary(), @Geom.STSrid) 
     
    /* Ooooh... shiny! */ 
    SELECT @Geog 

    Note: In general you shouldn't really be converting between geometry and geography - they're different datatypes designed for storing different types of data. However, in this case it's ok because the original geometry instance used angular coordinates based on a geodetic spatial reference system (4326) and should really have used the geography datatype anyway...

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, March 16, 2009 9:15 AM
    Answerer
  • Thanks again Tanoshimi :)

    re: shame + spatialwiki ---- i couldn't agree more. In fact, I had to read the export data a number of times because I just couldn't believe what i was reading!

    Ok - lets go over the tricky bit -> paragraph 2.

    You're creating a GEOGRAPHY from a well known binary. kewl. So you generate this WKB from the GEOGRAPHY. So far i've understood that. But it's the union that i'm stuck on. What u've done is (if i understand this right...)

    1) join the current GEOMETRY with a NEW GEOMETRY, which is the first point of the previous GEOMETRY.
    2) Make this result valid (while where here / because we can / to be safe)
    3) get binary representation of this.

    i can't see how, by adding the union of the existing GEOMETRY with the first point, that actually does anything ... or as u said, force the ring orientation. Can u please elaborate?

    cheers.

    -Pure Krome-

    EDIT: I think this blog post from Spatial Ed has actually answered my question :) I'm going to put this down as a lesson in experience (or for me, lack of) :)
    Monday, March 16, 2009 10:26 PM
  • It's a trick ;)

    A.STUnion(B) creates a geometry from all the points contained in both A and B.
    So, if geometry B is just a single point that is the start point of A, then A.STUnion(B) doesn't really have an effect - it creates a union of all the points in A together with another point that's already in A.... so A.STUnion(B) = A, right?
    BUT,  when SQL Server works out the result of any STxxx() methods, it always gives the results using correct ring orientation.

    So A.STUnion(A.STStartPoint) is a handy way of not altering any of the points in geometry A, but it does fix ring orientation issues.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, March 16, 2009 10:49 PM
    Answerer