locked
Geography data type: GeomFromGml RRS feed

  • Question

  • I have troubles to load data into a geography data type:

    DECLARE @g geography;
    DECLARE @x xml;
    
    SET @x = '<gml:MultiSurface gml:id="HVO.20584" srsName="urn:ogc:def:crs:EPSG::5514" srsDimension="2" xmlns:gml="http://www.opengis.net/gml/3.2">
    <gml:surfaceMember>
    <gml:Polygon gml:id="HVO.20584.1">
      <gml:exterior>
        <gml:LinearRing>
          <gml:posList>-747267.00 -1056204.00 -747226.00 -1056232.00 -747239.00 -1056253.00      -747251.00 -1056278.00 -747267.00 -1056315.00 -747291.00 -1056365.00 -747337.00 -1056470.00 -747311.00 -1056474.00 -747244.00 -1056475.00 -747155.00 -1056467.00 -747077.00 -1056501.00 -747112.00 -1057366.00 -747263.00 -1057857.00 -747263.20 -1057857.05 -747263.30 -1057856.60 -747272.50 -1057858.50 -747276.10 -1057850.21 -747285.47 -1057849.89 -747295.34 -1057849.55 -747308.73 -1057848.34 -747320.38 -1057847.28 -747346.97 -1057857.70 -747358.66 -1057860.00 -747374.12 -1057860.55 -747386.06 -1057860.19 -747407.98 -1057859.54 -747444.60 -1057874.26 -747447.75 -1057879.52 -747454.57 -1057890.92 -747674.02 -1057865.46 -747851.54 -1057841.44 -747855.78 -1057838.67 -747871.68 -1057851.47 -747902.20 -1057870.55 -747916.06 -1057881.32 -747934.07 -1057891.87 -747960.03 -1057915.53 -747985.14 -1057954.40 -748001.13 -1057977.02 -748021.22 -1058003.59 -748088.58 -1058092.68 -748104.73 -1058095.49 -748166.00 -1058032.60 -748193.50 -1058002.40 -748214.40 -1057982.20 -748216.40 -1057988.60 -748293.20 -1058053.20 -748297.14 -1058050.03 -748302.55 -1058045.67 -748306.53 -1058042.47 -748311.89 -1058038.15 -748316.50 -1058034.44 -748320.83 -1058030.95 -748330.04 -1058023.54 -748332.15 -1058021.83 -748337.10 -1058017.85 -748341.39 -1058014.39 -748353.30 -1058004.80 -748347.67 -1057996.70 -748338.28 -1057981.31 -748335.70 -1057977.29 -748332.12 -1057971.71 -748330.30 -1057968.92 -748325.55 -1057961.32 -748317.05 -1057947.55 -748310.89 -1057935.02 -748307.10 -1057924.52 -748302.70 -1057912.35 -748298.54 -1057900.12 -748295.48 -1057891.13 -748286.20 -1057862.27 -748278.37 -1057838.25 -748276.55 -1057834.04 -748267.49 -1057808.20 -748264.92 -1057798.33 -748262.21 -1057787.93 -748254.57 -1057761.59 -748251.89 -1057752.13 -748247.59 -1057736.93 -748240.11 -1057716.13 -748237.47 -1057685.67 -748234.93 -1057657.42 -748234.51 -1057652.51 -748234.98 -1057625.54 -748234.51 -1057622.46 -748231.40 -1057602.21 -748233.13 -1057596.29 -748234.13 -1057592.87 -748237.61 -1057582.60 -748238.48 -1057580.02 -748249.72 -1057571.70 -748250.10 -1057571.42 -748252.39 -1057569.73 -748256.83 -1057563.57 -748258.55 -1057540.99 -748250.24 -1057535.67 -748250.94 -1057526.41 -748243.01 -1057522.35 -748236.51 -1057522.75 -748234.73 -1057522.86 -748230.54 -1057518.95 -748211.65 -1057501.36 -748199.48 -1057493.59 -748189.98 -1057489.26 -748179.90 -1057484.66 -748168.54 -1057470.65 -748168.29 -1057470.34 -748165.98 -1057467.49 -748157.92 -1057458.13 -748147.31 -1057450.99 -748146.04 -1057423.54 -748145.40 -1057410.42 -748144.91 -1057400.47 -748144.08 -1057372.43 -748142.62 -1057348.91 -748142.48 -1057347.16 -748141.05 -1057329.37 -748142.26 -1057321.94 -748146.21 -1057304.13 -748148.14 -1057295.42 -748146.30 -1057289.44 -748144.73 -1057284.34 -748141.64 -1057275.31 -748144.63 -1057272.64 -748154.01 -1057247.80 -748151.29 -1057246.55 -748127.50 -1057235.56 -748110.70 -1057228.23 -748082.39 -1057214.75 -748057.48 -1057203.76 -748041.82 -1057196.01 -748029.14 -1057189.74 -748010.10 -1057178.36 -747979.25 -1057161.25 -747957.25 -1057147.63 -747943.14 -1057138.66 -747937.35 -1057131.26 -747926.97 -1057121.60 -747909.48 -1057109.13 -747904.01 -1057106.36 -747895.25 -1057106.99 -747873.33 -1057094.64 -747869.74 -1057093.00 -747851.77 -1057082.31 -747869.79 -1057044.54 -747836.92 -1057032.09 -747795.65 -1057016.73 -747793.70 -1057009.93 -747787.82 -1056992.87 -747781.28 -1056974.44 -747776.81 -1056960.35 -747768.14 -1056933.57 -747762.16 -1056911.71 -747757.80 -1056895.21 -747753.30 -1056877.50 -747749.17 -1056859.18 -747744.25 -1056838.69 -747741.79 -1056821.91 -747739.99 -1056803.90 -747737.12 -1056781.92 -747735.82 -1056761.48 -747735.01 -1056740.42 -747736.57 -1056718.66 -747738.55 -1056694.68 -747741.17 -1056669.07 -747744.02 -1056634.01 -747745.17 -1056623.46 -747748.09 -1056596.69 -747755.37 -1056564.46 -747759.01 -1056547.20 -747761.29 -1056536.38 -747761.56 -1056535.10 -747772.54 -1056491.85 -747774.05 -1056485.90 -747786.35 -1056435.41 -747786.99 -1056433.22 -747796.41 -1056400.93 -747799.01 -1056392.00 -747799.01 -1056392.00 -747799.00 -1056392.00 -747723.00 -1056390.00 -747615.00 -1056374.00 -747596.00 -1056372.00 -747564.00 -1056375.00 -747539.00 -1056382.00 -747513.00 -1056389.00 -747487.00 -1056344.00 -747472.00 -1056316.00 -747458.00 -1056290.00 -747455.00 -1056283.00 -747445.00 -1056265.00 -747433.00 -1056243.00 -747424.00 -1056225.00 -747420.00 -1056218.00 -747412.00 -1056205.00 -747399.00 -1056188.00 -747388.00 -1056173.00 -747383.00 -1056167.00 -747371.00 -1056154.00 -747370.00 -1056153.00 -747362.00 -1056145.00 -747356.00 -1056141.00 -747356.00 -1056140.00 -747334.00 -1056160.00 -747267.00 -1056204.00</gml:posList>
        </gml:LinearRing>
      </gml:exterior>
    </gml:Polygon>
    </gml:surfaceMember>
    </gml:MultiSurface>';
    SET @g = geography::GeomFromGml(@x, 4326);

    I get this exception:

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24129: The given XML instance is not valid because the top-level tag is gml:MultiSurface. The top-level element of the input Geographic Markup Language (GML) must contain a Point, LineString, Polygon, MultiPoint, MultiGeometry, MultiCurve, MultiSurface, Arc, ArcString, CompositeCurve, PolygonPatch or FullGlobe (geography Data Type only) object.

    What do I need to change in the @x variable to get it working? 

    The example data above come from government provided source and should be some small part of Czech Republic. I need to store them in MSSQL table.

    Thanks for suggestons.

    Saturday, September 20, 2014 9:56 AM

Answers

  • You are welcome :-)

    by the way my name is Ronen as you can see in my signature, pituach is just a forums nickname.

    I will answer all the question here, in order to organize the thread in one line and not separated as tree structure.

    >> is it OK to use geometry data type for purely geographical data?

    those are two different type which use different calculations. Here is a nice riddle for you: If a person go North 100km, then East 100km, then South 100km, and finally West 100km, where will he be?

    * the answer is, not in the starting point! but if you are using Two-dimensional geometrical space then he will be right at the starting point. In fact the the Geophysical type is a very complex implementation of the Geometry type. Theoretically, you can use Geometry type and build a spherical surface similar to those of the Earth. This is what actually happens behind the scenes. this is complex since this is Elliptical rather than circular. the simple answer to your question, therefore is, yes you can, but you should not do it!

    * In my solution above I used two different for the second solution.

    >> All the data in my application (like the example in my question) are and always will be inside the Czech republic (borders of towns, regions etc.).

    Any scientific system include inaccuracy. the accuracy can vary from one study to another. The data of the Earth's surface in geometry type column is not accurate as it is! and it have their own measurement errors. On a very small surface area, the spherical surface similar to a plain (same as two dimensional). therefore, the difference between the types, become smaller as the area become smaller. For example the same riddle with 10cm will bring you to a very close place and it will look like you are in the starting point. It is up to your system architect to give this answer, since he know what is "good enough" for you. He should decide the accuracy that fit your needs. I am coming from a very small country, Israel (The width of the state is about 70-80km, like one big city in Europe or the United States), and in our case, for a lot of systems's needs you can use Geometry. But again, it is all about the the accurate that you need regarding the surface area size.

    >> Do you suggest using geometry data type is OK in this case?

    same as above

    >> The data might not be incorrect, I think it is probably just in some different format This is probably correct. If you explain the format then we could help you with converting it to lat,long.
    Try to find out what is the format.


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Monday, September 22, 2014 12:39 PM
    • Proposed as answer by Charlie Liao Wednesday, September 24, 2014 7:55 AM
    • Marked as answer by Charlie Liao Sunday, September 28, 2014 2:20 AM
    Monday, September 22, 2014 12:13 PM

All replies

  • Hi,

    1. First use http://www.opengis.net/gml namespace, not the http://www.opengis.net/gml/3.2

    2. remove extra parameters from top level tag
    gml:id="HVO.20584" srsName="urn:ogc:def:crs:EPSG::5514" srsDimension="2"

    3. Your data is not correct!
    Read the Error and fix the data in the the <gml:posList> tag
    Latitude values must be between -90 and 90 degrees.

    here is example:

    DECLARE @g geography;
    DECLARE @x xml;
    SET @x = '
    <gml:MultiSurface xmlns:gml="http://www.opengis.net/gml">
    <gml:surfaceMember>
    <gml:Polygon>
     <gml:exterior>
      <gml:LinearRing>
       <gml:posList>
    	 <!--
    	 -747267.00 -1056204.00 -747226.00 -1056232.00 -747239.00 -1056253.00      -747251.00 -1056278.00 -747267.00 -1056315.00 -747291.00 -1056365.00 -747337.00 -1056470.00 -747311.00 -1056474.00 -747244.00 -1056475.00 -747155.00 -1056467.00 -747077.00 -1056501.00 -747112.00 -1057366.00 -747263.00 -1057857.00 -747263.20 -1057857.05 -747263.30 -1057856.60 -747272.50 -1057858.50 -747276.10 -1057850.21 -747285.47 -1057849.89 -747295.34 -1057849.55 -747308.73 -1057848.34 -747320.38 -1057847.28 -747346.97 -1057857.70 -747358.66 -1057860.00 -747374.12 -1057860.55 -747386.06 -1057860.19 -747407.98 -1057859.54 -747444.60 -1057874.26 -747447.75 -1057879.52 -747454.57 -1057890.92 -747674.02 -1057865.46 -747851.54 -1057841.44 -747855.78 -1057838.67 -747871.68 -1057851.47 -747902.20 -1057870.55 -747916.06 -1057881.32 -747934.07 -1057891.87 -747960.03 -1057915.53 -747985.14 -1057954.40 -748001.13 -1057977.02 -748021.22 -1058003.59 -748088.58 -1058092.68 -748104.73 -1058095.49 -748166.00 -1058032.60 -748193.50 -1058002.40 -748214.40 -1057982.20 -748216.40 -1057988.60 -748293.20 -1058053.20 -748297.14 -1058050.03 -748302.55 -1058045.67 -748306.53 -1058042.47 -748311.89 -1058038.15 -748316.50 -1058034.44 -748320.83 -1058030.95 -748330.04 -1058023.54 -748332.15 -1058021.83 -748337.10 -1058017.85 -748341.39 -1058014.39 -748353.30 -1058004.80 -748347.67 -1057996.70 -748338.28 -1057981.31 -748335.70 -1057977.29 -748332.12 -1057971.71 -748330.30 -1057968.92 -748325.55 -1057961.32 -748317.05 -1057947.55 -748310.89 -1057935.02 -748307.10 -1057924.52 -748302.70 -1057912.35 -748298.54 -1057900.12 -748295.48 -1057891.13 -748286.20 -1057862.27 -748278.37 -1057838.25 -748276.55 -1057834.04 -748267.49 -1057808.20 -748264.92 -1057798.33 -748262.21 -1057787.93 -748254.57 -1057761.59 -748251.89 -1057752.13 -748247.59 -1057736.93 -748240.11 -1057716.13 -748237.47 -1057685.67 -748234.93 -1057657.42 -748234.51 -1057652.51 -748234.98 -1057625.54 -748234.51 -1057622.46 -748231.40 -1057602.21 -748233.13 -1057596.29 -748234.13 -1057592.87 -748237.61 -1057582.60 -748238.48 -1057580.02 -748249.72 -1057571.70 -748250.10 -1057571.42 -748252.39 -1057569.73 -748256.83 -1057563.57 -748258.55 -1057540.99 -748250.24 -1057535.67 -748250.94 -1057526.41 -748243.01 -1057522.35 -748236.51 -1057522.75 -748234.73 -1057522.86 -748230.54 -1057518.95 -748211.65 -1057501.36 -748199.48 -1057493.59 -748189.98 -1057489.26 -748179.90 -1057484.66 -748168.54 -1057470.65 -748168.29 -1057470.34 -748165.98 -1057467.49 -748157.92 -1057458.13 -748147.31 -1057450.99 -748146.04 -1057423.54 -748145.40 -1057410.42 -748144.91 -1057400.47 -748144.08 -1057372.43 -748142.62 -1057348.91 -748142.48 -1057347.16 -748141.05 -1057329.37 -748142.26 -1057321.94 -748146.21 -1057304.13 -748148.14 -1057295.42 -748146.30 -1057289.44 -748144.73 -1057284.34 -748141.64 -1057275.31 -748144.63 -1057272.64 -748154.01 -1057247.80 -748151.29 -1057246.55 -748127.50 -1057235.56 -748110.70 -1057228.23 -748082.39 -1057214.75 -748057.48 -1057203.76 -748041.82 -1057196.01 -748029.14 -1057189.74 -748010.10 -1057178.36 -747979.25 -1057161.25 -747957.25 -1057147.63 -747943.14 -1057138.66 -747937.35 -1057131.26 -747926.97 -1057121.60 -747909.48 -1057109.13 -747904.01 -1057106.36 -747895.25 -1057106.99 -747873.33 -1057094.64 -747869.74 -1057093.00 -747851.77 -1057082.31 -747869.79 -1057044.54 -747836.92 -1057032.09 -747795.65 -1057016.73 -747793.70 -1057009.93 -747787.82 -1056992.87 -747781.28 -1056974.44 -747776.81 -1056960.35 -747768.14 -1056933.57 -747762.16 -1056911.71 -747757.80 -1056895.21 -747753.30 -1056877.50 -747749.17 -1056859.18 -747744.25 -1056838.69 -747741.79 -1056821.91 -747739.99 -1056803.90 -747737.12 -1056781.92 -747735.82 -1056761.48 -747735.01 -1056740.42 -747736.57 -1056718.66 -747738.55 -1056694.68 -747741.17 -1056669.07 -747744.02 -1056634.01 -747745.17 -1056623.46 -747748.09 -1056596.69 -747755.37 -1056564.46 -747759.01 -1056547.20 -747761.29 -1056536.38 -747761.56 -1056535.10 -747772.54 -1056491.85 -747774.05 -1056485.90 -747786.35 -1056435.41 -747786.99 -1056433.22 -747796.41 -1056400.93 -747799.01 -1056392.00 -747799.01 -1056392.00 -747799.00 -1056392.00 -747723.00 -1056390.00 -747615.00 -1056374.00 -747596.00 -1056372.00 -747564.00 -1056375.00 -747539.00 -1056382.00 -747513.00 -1056389.00 -747487.00 -1056344.00 -747472.00 -1056316.00 -747458.00 -1056290.00 -747455.00 -1056283.00 -747445.00 -1056265.00 -747433.00 -1056243.00 -747424.00 -1056225.00 -747420.00 -1056218.00 -747412.00 -1056205.00 -747399.00 -1056188.00 -747388.00 -1056173.00 -747383.00 -1056167.00 -747371.00 -1056154.00 -747370.00 -1056153.00 -747362.00 -1056145.00 -747356.00 -1056141.00 -747356.00 -1056140.00 -747334.00 -1056160.00 -747267.00 -1056204.00
         -->
         60.459099 16.926436 60.460080 16.933631 60.460272 16.935328 60.460563 16.936937 60.474139 16.929063 60.474348 16.930889 60.476611 16.929621 60.479701 16.929346 60.479487 16.922792 60.476801 16.923118 60.475752 16.921900 60.472010 16.923844 60.470093 16.920215 60.459099 16.926436
       </gml:posList>
      </gml:LinearRing>
     </gml:exterior>
    </gml:Polygon>
    </gml:surfaceMember>
    </gml:MultiSurface>
    ';
    select @x
    SET @g = geography::GeomFromGml(@x, 4326);


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Saturday, September 20, 2014 12:55 PM
    Saturday, September 20, 2014 12:49 PM
  • About the last comment: maybe you want to use GEOMETRY and NOT Geography data type?!?

    this will work for your data

    DECLARE @g geometry;
    DECLARE @x xml;
    SET @x = '
    <gml:MultiSurface xmlns:gml="http://www.opengis.net/gml">
    <gml:surfaceMember>
    <gml:Polygon>
     <gml:exterior>
      <gml:LinearRing>
       <gml:posList>
    	 -747267.00 -1056204.00 -747226.00 -1056232.00 -747239.00 -1056253.00      -747251.00 -1056278.00 -747267.00 -1056315.00 -747291.00 -1056365.00 -747337.00 -1056470.00 -747311.00 -1056474.00 -747244.00 -1056475.00 -747155.00 -1056467.00 -747077.00 -1056501.00 -747112.00 -1057366.00 -747263.00 -1057857.00 -747263.20 -1057857.05 -747263.30 -1057856.60 -747272.50 -1057858.50 -747276.10 -1057850.21 -747285.47 -1057849.89 -747295.34 -1057849.55 -747308.73 -1057848.34 -747320.38 -1057847.28 -747346.97 -1057857.70 -747358.66 -1057860.00 -747374.12 -1057860.55 -747386.06 -1057860.19 -747407.98 -1057859.54 -747444.60 -1057874.26 -747447.75 -1057879.52 -747454.57 -1057890.92 -747674.02 -1057865.46 -747851.54 -1057841.44 -747855.78 -1057838.67 -747871.68 -1057851.47 -747902.20 -1057870.55 -747916.06 -1057881.32 -747934.07 -1057891.87 -747960.03 -1057915.53 -747985.14 -1057954.40 -748001.13 -1057977.02 -748021.22 -1058003.59 -748088.58 -1058092.68 -748104.73 -1058095.49 -748166.00 -1058032.60 -748193.50 -1058002.40 -748214.40 -1057982.20 -748216.40 -1057988.60 -748293.20 -1058053.20 -748297.14 -1058050.03 -748302.55 -1058045.67 -748306.53 -1058042.47 -748311.89 -1058038.15 -748316.50 -1058034.44 -748320.83 -1058030.95 -748330.04 -1058023.54 -748332.15 -1058021.83 -748337.10 -1058017.85 -748341.39 -1058014.39 -748353.30 -1058004.80 -748347.67 -1057996.70 -748338.28 -1057981.31 -748335.70 -1057977.29 -748332.12 -1057971.71 -748330.30 -1057968.92 -748325.55 -1057961.32 -748317.05 -1057947.55 -748310.89 -1057935.02 -748307.10 -1057924.52 -748302.70 -1057912.35 -748298.54 -1057900.12 -748295.48 -1057891.13 -748286.20 -1057862.27 -748278.37 -1057838.25 -748276.55 -1057834.04 -748267.49 -1057808.20 -748264.92 -1057798.33 -748262.21 -1057787.93 -748254.57 -1057761.59 -748251.89 -1057752.13 -748247.59 -1057736.93 -748240.11 -1057716.13 -748237.47 -1057685.67 -748234.93 -1057657.42 -748234.51 -1057652.51 -748234.98 -1057625.54 -748234.51 -1057622.46 -748231.40 -1057602.21 -748233.13 -1057596.29 -748234.13 -1057592.87 -748237.61 -1057582.60 -748238.48 -1057580.02 -748249.72 -1057571.70 -748250.10 -1057571.42 -748252.39 -1057569.73 -748256.83 -1057563.57 -748258.55 -1057540.99 -748250.24 -1057535.67 -748250.94 -1057526.41 -748243.01 -1057522.35 -748236.51 -1057522.75 -748234.73 -1057522.86 -748230.54 -1057518.95 -748211.65 -1057501.36 -748199.48 -1057493.59 -748189.98 -1057489.26 -748179.90 -1057484.66 -748168.54 -1057470.65 -748168.29 -1057470.34 -748165.98 -1057467.49 -748157.92 -1057458.13 -748147.31 -1057450.99 -748146.04 -1057423.54 -748145.40 -1057410.42 -748144.91 -1057400.47 -748144.08 -1057372.43 -748142.62 -1057348.91 -748142.48 -1057347.16 -748141.05 -1057329.37 -748142.26 -1057321.94 -748146.21 -1057304.13 -748148.14 -1057295.42 -748146.30 -1057289.44 -748144.73 -1057284.34 -748141.64 -1057275.31 -748144.63 -1057272.64 -748154.01 -1057247.80 -748151.29 -1057246.55 -748127.50 -1057235.56 -748110.70 -1057228.23 -748082.39 -1057214.75 -748057.48 -1057203.76 -748041.82 -1057196.01 -748029.14 -1057189.74 -748010.10 -1057178.36 -747979.25 -1057161.25 -747957.25 -1057147.63 -747943.14 -1057138.66 -747937.35 -1057131.26 -747926.97 -1057121.60 -747909.48 -1057109.13 -747904.01 -1057106.36 -747895.25 -1057106.99 -747873.33 -1057094.64 -747869.74 -1057093.00 -747851.77 -1057082.31 -747869.79 -1057044.54 -747836.92 -1057032.09 -747795.65 -1057016.73 -747793.70 -1057009.93 -747787.82 -1056992.87 -747781.28 -1056974.44 -747776.81 -1056960.35 -747768.14 -1056933.57 -747762.16 -1056911.71 -747757.80 -1056895.21 -747753.30 -1056877.50 -747749.17 -1056859.18 -747744.25 -1056838.69 -747741.79 -1056821.91 -747739.99 -1056803.90 -747737.12 -1056781.92 -747735.82 -1056761.48 -747735.01 -1056740.42 -747736.57 -1056718.66 -747738.55 -1056694.68 -747741.17 -1056669.07 -747744.02 -1056634.01 -747745.17 -1056623.46 -747748.09 -1056596.69 -747755.37 -1056564.46 -747759.01 -1056547.20 -747761.29 -1056536.38 -747761.56 -1056535.10 -747772.54 -1056491.85 -747774.05 -1056485.90 -747786.35 -1056435.41 -747786.99 -1056433.22 -747796.41 -1056400.93 -747799.01 -1056392.00 -747799.01 -1056392.00 -747799.00 -1056392.00 -747723.00 -1056390.00 -747615.00 -1056374.00 -747596.00 -1056372.00 -747564.00 -1056375.00 -747539.00 -1056382.00 -747513.00 -1056389.00 -747487.00 -1056344.00 -747472.00 -1056316.00 -747458.00 -1056290.00 -747455.00 -1056283.00 -747445.00 -1056265.00 -747433.00 -1056243.00 -747424.00 -1056225.00 -747420.00 -1056218.00 -747412.00 -1056205.00 -747399.00 -1056188.00 -747388.00 -1056173.00 -747383.00 -1056167.00 -747371.00 -1056154.00 -747370.00 -1056153.00 -747362.00 -1056145.00 -747356.00 -1056141.00 -747356.00 -1056140.00 -747334.00 -1056160.00 
    	 -747267.00 -1056204.00
       </gml:posList>
      </gml:LinearRing>
     </gml:exterior>
    </gml:Polygon>
    </gml:surfaceMember>
    </gml:MultiSurface>
    ';
    select @x
    SET @g = geometry::GeomFromGml(@x, 4326);


    [Personal Site] [Blog] [Facebook]signature

    Saturday, September 20, 2014 12:58 PM
  • Here is the result by the way if you use "select @g" in the end:


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Saturday, September 20, 2014 1:11 PM
    Saturday, September 20, 2014 1:10 PM
  • Hi Pituach, thank you very much for your response. The data might not be incorrect, I think it is probably just in some different format (or units or something like that) then what SQL server expects. Unfortunately I do not understand GML at all.

    Monday, September 22, 2014 7:16 AM
  • Thanks, this works. But is it OK to use geometry data type for purely geographical data? All the data in my application (like the example in my question) are and always will be inside the Czech republic (borders of towns, regions etc.).

    Do you suggest using geometry data type is OK in this case?

    Thanks for help.

    Monday, September 22, 2014 7:22 AM
  • You are welcome :-)

    by the way my name is Ronen as you can see in my signature, pituach is just a forums nickname.

    I will answer all the question here, in order to organize the thread in one line and not separated as tree structure.

    >> is it OK to use geometry data type for purely geographical data?

    those are two different type which use different calculations. Here is a nice riddle for you: If a person go North 100km, then East 100km, then South 100km, and finally West 100km, where will he be?

    * the answer is, not in the starting point! but if you are using Two-dimensional geometrical space then he will be right at the starting point. In fact the the Geophysical type is a very complex implementation of the Geometry type. Theoretically, you can use Geometry type and build a spherical surface similar to those of the Earth. This is what actually happens behind the scenes. this is complex since this is Elliptical rather than circular. the simple answer to your question, therefore is, yes you can, but you should not do it!

    * In my solution above I used two different for the second solution.

    >> All the data in my application (like the example in my question) are and always will be inside the Czech republic (borders of towns, regions etc.).

    Any scientific system include inaccuracy. the accuracy can vary from one study to another. The data of the Earth's surface in geometry type column is not accurate as it is! and it have their own measurement errors. On a very small surface area, the spherical surface similar to a plain (same as two dimensional). therefore, the difference between the types, become smaller as the area become smaller. For example the same riddle with 10cm will bring you to a very close place and it will look like you are in the starting point. It is up to your system architect to give this answer, since he know what is "good enough" for you. He should decide the accuracy that fit your needs. I am coming from a very small country, Israel (The width of the state is about 70-80km, like one big city in Europe or the United States), and in our case, for a lot of systems's needs you can use Geometry. But again, it is all about the the accurate that you need regarding the surface area size.

    >> Do you suggest using geometry data type is OK in this case?

    same as above

    >> The data might not be incorrect, I think it is probably just in some different format This is probably correct. If you explain the format then we could help you with converting it to lat,long.
    Try to find out what is the format.


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Monday, September 22, 2014 12:39 PM
    • Proposed as answer by Charlie Liao Wednesday, September 24, 2014 7:55 AM
    • Marked as answer by Charlie Liao Sunday, September 28, 2014 2:20 AM
    Monday, September 22, 2014 12:13 PM