locked
ZIP code multipolygon not in correct format - What might be wrong ? RRS feed

  • Question

  • I imported our Map Info ZIPs for AZ using FWTools to shape files. I imported the shape file to SQL 2008 using Shape2SQL. No problem so far. Some of the shapes (ZIPS) are multipolygons and some are polygons. Some of the sets of multipolygons have single parenthesis "(" around the points , not double "((".  Like so:  (I've cut down the points for this example. I've bold and underlined the offending paras)

      MULTIPOLYGON (((-111.94461300969124 33.378685995936394, -111.94431000947952 33.378695994615555, -111.9442940056324 33.378712996840477, -111.94428899884224 33.378685995936394)),

    ((-111.97762799263 33.376549005508423, -111.97736901044846

    33.376693993806839, -111.97563698887825 33.376638993620872, -111.97545799612999 33.376552000641823, -111.97762799263 33.376549005508423)),

    ((-111.92428800463677 33.349101006984711, -111.92431101202965 33.349153995513916, -111.92432600259781 33.349227994680405, -111.92305099964142 111.92584699392319 33.349236994981766, -111.92580598592758 33.349105000495911, -111.92428800463677 33.349101006984711),(-1 11.95171898603439 33.38068999350071, -111.95177599787712 33.380695998668671, -111.95183399319649 33.380731999874115, -111.95131701231003 33.380691006779671, -111.95171898603439 33.38068999350071)))

    Between the 3rd and 4th polygon there is only a single ") (" .

    So I 'fixed' it by putting the second "),(" around the data.  But it won't display the shape on the Bing map.  If I leave out the last poly, it works OK.

    So my questions are: Have you come across this single paras problem like this before ?  If so, do you have any suggestions on fixing it?

    Thxs.

    Jay

    • Edited by JayViz Tuesday, July 28, 2009 5:04 AM
    Monday, July 27, 2009 8:47 PM

Answers

  • A polygon contains one or more rings. A multipolygon contains one or more polygons.

    Each ring of a polygon is contained in a comma-separated list surrounded by brackets, and each polgon in a multipolygon instance is contained in a comma-separated list surrounded by brackets.

    The 'single brackets' in your original code listing might become a bit clearer when you indent the code, as follows:

    MULTIPOLYGON (
      --This is the first polygon
      (
        --This is the first ring of the first polygon
        (-111.94461300969124 33.378685995936394, -111.94431000947952 33.378695994615555, -111.9442940056324 33.378712996840477, -111.94428899884224 33.378685995936394
        )
      ),
      --This is the second polygon
      (
        -- This is the first ring of the second polygon
        (-111.97762799263 33.376549005508423, -111.97736901044846 33.376693993806839, -111.97563698887825 33.376638993620872, -111.97545799612999 33.376552000641823, -111.97762799263 33.376549005508423
        )
      ),
      -- This is the third polygon
      (
        -- This is the first ring of the third polygon
        (-111.92428800463677 33.349101006984711, -111.92431101202965 33.349153995513916, -111.92432600259781 33.349227994680405, -111.92305099964142 111.92584699392319 33.349236994981766, -111.92580598592758 33.349105000495911, -111.92428800463677 33.349101006984711
        ),
        -- This is the second ring of the third polygon
        (-1 11.95171898603439 33.38068999350071, -111.95177599787712 33.380695998668671, -111.95183399319649 33.380731999874115, -111.95131701231003 33.380691006779671, -111.95171898603439 33.38068999350071
        )
      )
    )

    For a thread concerning splitting multipolygons to polygons, try: http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/c2096879-ebf8-4245-8f59-cbe853d00069

    For splitting rings from a multiring instance, you can follow basically the same process but replace STNumGeometries() with NumRings(), and STGeometryN() with RingN()

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by JayViz Tuesday, July 28, 2009 5:46 PM
    Tuesday, July 28, 2009 6:47 AM
    Answerer

All replies

  • I think that the crux of your problem is the Bing Maps AJAX API. The Bing Maps API does not handle multipart geometries.  Additionally, something to watch out for are polyogn holes (interior rings), which the Bing Maps API also does not handle.

    The easiest way to handle the multipart problem with Bing Maps is to make each "part" a separate instance. You can do with the T-SQL methods provided with SQL Server 2008 Spatial.

    Monday, July 27, 2009 10:23 PM
  • Yes, the Bing map seems to be the problem. 

    When I do a SELECT *  FROM <table_name> and look at the spatial result, the shape does display correctly.

    So what methods to I use to make each part a separate instance ?

    Thanks.
    Monday, July 27, 2009 10:35 PM
  • A polygon contains one or more rings. A multipolygon contains one or more polygons.

    Each ring of a polygon is contained in a comma-separated list surrounded by brackets, and each polgon in a multipolygon instance is contained in a comma-separated list surrounded by brackets.

    The 'single brackets' in your original code listing might become a bit clearer when you indent the code, as follows:

    MULTIPOLYGON (
      --This is the first polygon
      (
        --This is the first ring of the first polygon
        (-111.94461300969124 33.378685995936394, -111.94431000947952 33.378695994615555, -111.9442940056324 33.378712996840477, -111.94428899884224 33.378685995936394
        )
      ),
      --This is the second polygon
      (
        -- This is the first ring of the second polygon
        (-111.97762799263 33.376549005508423, -111.97736901044846 33.376693993806839, -111.97563698887825 33.376638993620872, -111.97545799612999 33.376552000641823, -111.97762799263 33.376549005508423
        )
      ),
      -- This is the third polygon
      (
        -- This is the first ring of the third polygon
        (-111.92428800463677 33.349101006984711, -111.92431101202965 33.349153995513916, -111.92432600259781 33.349227994680405, -111.92305099964142 111.92584699392319 33.349236994981766, -111.92580598592758 33.349105000495911, -111.92428800463677 33.349101006984711
        ),
        -- This is the second ring of the third polygon
        (-1 11.95171898603439 33.38068999350071, -111.95177599787712 33.380695998668671, -111.95183399319649 33.380731999874115, -111.95131701231003 33.380691006779671, -111.95171898603439 33.38068999350071
        )
      )
    )

    For a thread concerning splitting multipolygons to polygons, try: http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/c2096879-ebf8-4245-8f59-cbe853d00069

    For splitting rings from a multiring instance, you can follow basically the same process but replace STNumGeometries() with NumRings(), and STGeometryN() with RingN()

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by JayViz Tuesday, July 28, 2009 5:46 PM
    Tuesday, July 28, 2009 6:47 AM
    Answerer
  • Thanks for the great information.  I'll check out those links.
    Tuesday, July 28, 2009 5:49 PM