Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Red face Unanswered: Importing SPatial Data

    I have a KML file with co-ordinates of hurricane ring in it, How can i import it to SQL Server , This is how it looks

    <coordinates>
    -99.015564,23.398851,0 -98.953056,23.392969,0 -98.889214,23.387062
    --------------
    -----------
    ----------
    0 -99.146057,23.411346,0 -99.079224,23.405035,0 -99.015564,23.398851,0 </coordinates>



    I am writing following statement to Inert it but i am getting an error



    -------------creating Index-----------------------
    CREATE spatial INDEX IXS_Geo_GeoData ON dbo.Geo (GeoData )
    USING GEOMETRY_GRID
    WITH (BOUNDING_BOX =(0, 0, 1000, 800))
    GO

    -------------------creating Table------------------
    CREATE TABLE SpatialTable
    ( id int IDENTITY (1,1),
    GeogCol1 geography,
    GeogCol2 AS GeogCol1.STAsText() );
    GO
    ---------------Inserting Data------------------------
    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography:TGeomFromText('Point(-82.235840 28.601027 0)', 4326));

    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography:TGeomFromText('POLYGON((
    -99.015564,23.398851,0 -98.953056,23.392969,0 -98.889214,23.387062,0 -98.823761,23.380806,0 -98.759232,23.374264,0 -98.699097,23.367908,0 -98.644958,23.362230,0 -98.594833,23.357319,0 -98.544357,23.352747,0 -98.489655,23.347843,0 -98.429970,23.342175,0 -98.368095,23.335840,0 -98.308228,23.329405,0 -98.253082,23.323483,0 -98.201988,23.318270,0 -98.151428,23.313349,0 -98.097397,23.307846,0 -98.037872,23.300838,0 -97.973625,23.291767,0 -97.907524,23.280695,0 -97.842812,23.268274,0 -97.781586,23.255432,0 -97.723999,23.242920,0 -97.668526,23.230930,0 -97.613205,23.219128,0 -97.557236,23.207066,0 -97.501610,23.194681,0 -97.448418,23.182446,0 -97.399109,23.171022,0 -97.353188,23.160719,0 -97.308296,23.151176,0 -97.261765,23.141567,0 -97.212296,23.131157,0 -97.160660,23.119776,0 -97.108971,23.107904,0 -97.059181,23.096334,0 -97.011871,23.085608,0 -96.966034,23.075645,0 -96.919930,23.065784,0 -96.872314,23.055138,0 -96.823242,23.043072,0 -96.774055,23.029531,0 -96.726761,23.015068,0 -96.683105,23.000622,0 -96.643692,22.987040,0 -96.607506,22.974628,0 -96.572304,22.962973,0 -96.535828,22.951229,0 -96.497017,22.938671,0 -96.456528,22.925150,0 -96.416000,22.911160,0 -96.376968,22.897476,0 -96.339874,22.884636,0 -96.303871,22.872595,0 -96.267540,22.860773,0 -96.229767,22.848454,0 -96.190407,22.835247,0 -96.150269,22.821356,0 -96.110596,22.807419,0 -96.072327,22.794121,0 -96.035721,22.781750,0 -96.000313,22.770048,0 -95.965523,22.758446,0 -95.931183,22.746496,0 -95.897804,22.734211,0 -95.866203,22.722088,0 -95.836884,22.710804,0 -95.809555,22.700808,0 -95.783096,22.692009,0 -95.756218,22.683830,0 -95.728043,22.675472,0 -95.698471,22.666248,0 -95.668022,22.655739,0 -95.637383,22.643764,0 -95.607086,22.630299,0 -95.577469,22.615372,0 -95.548721,22.599024,0 -95.520943,22.581305,0 -95.494217,22.562267,0 -95.468620,22.541983,0 -95.444252,22.520575,0 -95.421265,22.498318,0 -95.399887,22.475710,0 -95.380386,22.453539,0 -95.362930,22.432663,0 -95.347382,22.413631,0 -95.333138,22.396303,0 -95.319275,22.379818,0 -95.304977,22.363029,0 -95.289871,22.345160,0 -95.274132,22.326178,0 -95.258293,22.306730,0 -95.242874,22.287703,0 -95.228111,22.269688,0 -95.213936,22.252701,0 -95.200096,22.236267,0 -95.186417,22.219830,0 -95.172989,22.203159,0 -95.160057,22.186548,0 -95.147911,22.170620,0 -95.136620,22.155956,0 -95.125961,22.142712,0 -95.115509,22.130487,0 -95.104820,22.118549,0 -95.093727,22.106201,0 -95.082375,22.093075,0 -95.071083,22.079197,0 -95.060181,22.064907,0 -95.049904,22.050745,0 -95.040314,22.037327,0 -95.031342,22.025146,0 -95.022736,22.014357,0 -95.014206,22.004677,0 -95.005463,21.995523,0 -94.996399,21.986290,0 -94.987152,21.976578,0 -94.977951,21.966255,0 -94.969055,21.955355,0 -94.960678,21.943960,0 -94.952919,21.932144,0 -94.945816,21.919958,0 -94.939407,21.907440,0 -94.933708,21.894630,0 -94.928749,21.881561,0 -94.924538,21.868269,0 -94.921082,21.854790,0 -94.918396,21.841162,0 -94.916466,21.827419,0 -94.915321,21.813604,0 -94.914963,21.799751,0 -94.915375,21.785902,0 -94.916565,21.772091,0 -94.918549,21.758358,0 -94.921295,21.744738,0 -94.924797,21.731274,0 -94.929062,21.717993,0 -94.934059,21.704941,0 -94.939789,21.692146,0 -94.946228,21.679651,0 -94.953369,21.667484,0 -94.961182,21.655682,0 -94.969635,21.644274,0 -94.978722,21.633293,0 -94.988426,21.622772,0 -94.998703,21.612734,0 -95.009529,21.603210,0 -95.020874,21.594223,0 -95.032707,21.585800,0 -95.044983,21.577965,0 -95.057701,21.570734,0 -95.070801,21.564133,0 -95.084244,21.558178,0 -95.098015,21.552885,0 -95.112061,21.548269,0 -95.126343,21.544340,0 -95.140839,21.541111,0 -95.155487,21.538588,0 -95.170258,21.536776,0 -95.185135,21.535656,0 -95.200096,21.535172,0 -95.215210,21.535172,0 -95.230591,21.535406,0 -95.246407,21.535530,0 -95.262878,21.535210,0 -95.280106,21.534264,0 -95.298111,21.532759,0 -95.316750,21.530987,0 -95.335861,21.529324,0 -95.355263,21.528057,0 -95.374863,21.527273,0 -95.394684,21.526829,0 -95.414833,21.526411,0 -95.435524,21.525703,0 -95.456932,21.524551,0 -95.479118,21.523046,0 -95.501953,21.521467,0 -95.525162,21.520111,0 -95.548439,21.519096,0 -95.571625,21.518309,0 -95.594910,21.517475,0 -95.618790,21.516342,0 -95.643890,21.514811,0 -95.670631,21.512913,0 -95.699219,21.510628,0 -95.729576,21.507654,0 -95.761459,21.503403,0 -95.794525,21.497227,0 -95.828476,21.488777,0 -95.863213,21.478241,0 -95.898918,21.466217,0 -95.936012,21.453352,0 -95.975006,21.439999,0 -96.016228,21.426134,0 -96.059494,21.411613,0 -96.104095,21.396484,0 -96.149063,21.381092,0 -96.193687,21.365871,0 -96.237740,21.351038,0 -96.281509,21.336437,0 -96.325401,21.321709,0 -96.369728,21.306595,0 -96.414482,21.291161,0 -96.459396,21.275734,0 -96.504028,21.260624,0 -96.548103,21.245832,0 -96.591797,21.230986,0 -96.635849,21.215515,0 -96.681236,21.198900,0 -96.728676,21.180819,0 -96.778381,21.161133,0 -96.830147,21.139868,0 -96.883690,21.117210,0 -96.939011,21.093449,0 -96.996353,21.068825,0 -97.055923,21.043427,0 -97.117592,21.017275,0 -97.180710,20.990509,0 -97.244492,20.963451,0 -97.308289,20.936420,0 -97.371948,20.909472,0 -97.435730,20.882357,0 -97.500175,20.854687,0 -97.565781,20.826160,0 -97.632805,20.796631,0 -97.701218,20.766094,0 -97.770691,20.734699,0 -97.840683,20.702765,0 -97.910759,20.670687,0 -97.980637,20.638721,0 -98.050385,20.606823,0 -98.120224,20.574768,0 -98.190308,20.542465,0 -98.260513,20.510080,0 -98.330551,20.477839,0 -98.400536,20.445684,0 -98.471252,20.413280,0 -98.543991,20.380379,0 -98.619804,20.347284,0 -98.699066,20.314922,0 -98.781448,20.284504,0 -98.866272,20.257109,0 -98.952904,20.233444,0 -99.040863,20.213882,0 -99.129807,20.198587,0 -99.219482,20.187630,0 -99.309624,20.181042,0 -99.400002,20.178844,0 -99.490387,20.181042,0 -99.580528,20.187630,0 -99.670204,20.198589,0 -99.759155,20.213890,0 -99.847160,20.233494,0 -99.933975,20.257347,0 -100.019363,20.285389,0 -100.103104,20.317539,0 -100.184967,20.353718,0 -100.264755,20.393826,0 -100.342224,20.437757,0 -100.417191,20.485395,0 -100.489433,20.536610,0 -100.558769,20.591267,0 -100.625015,20.649218,0 -100.687958,20.710310,0 -100.747459,20.774380,0 -100.803345,20.841253,0 -100.855461,20.910751,0 -100.903656,20.982689,0 -100.947800,21.056871,0 -100.987762,21.133097,0 -101.023438,21.211161,0 -101.054718,21.290852,0 -101.081512,21.371956,0 -101.103745,21.454248,0 -101.121338,21.537510,0 -101.134247,21.621511,0 -101.142410,21.706026,0 -101.145828,21.790825,0 -101.144455,21.875673,0 -101.138306,21.960337,0 -101.127365,22.044586,0 -101.111679,22.128185,0 -101.091263,22.210911,0 -101.066177,22.292526,0 -101.036484,22.372814,0 -101.002235,22.451548,0 -100.963547,22.528509,0 -100.920517,22.603485,0 -100.873245,22.676266,0 -100.821861,22.746649,0 -100.766510,22.814438,0 -100.707329,22.879440,0 -100.644485,22.941479,0 -100.578148,23.000376,0 -100.508499,23.055967,0 -100.435722,23.108097,0 -100.360039,23.156618,0 -100.281631,23.201393,0 -100.200745,23.242296,0 -100.117592,23.279211,0 -100.032425,23.312035,0 -99.945549,23.340673,0 -99.857452,23.365061,0 -99.769012,23.385155,0 -99.681580,23.400972,0 -99.596817,23.412605,0 -99.515862,23.420231,0 -99.438622,23.424095,0 -99.363747,23.424530,0 -99.289795,23.422028,0 -99.216736,23.417316,0 -99.146057,23.411346,0 -99.079224,23.405035,0 -99.015564,23.398851,0 ))', 4326));
    GO

    ERROR MESSAGE:
    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
    System.ArgumentException: 24200: The specified input does not represent a valid geography instance.


    do i need to change cordinates ?? is there any tool

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure that POLYGON supports 3 dimensions. At least that's what I assume all of the ",0"s are for?

    Second, commas should separate points (ordered pairs), but not the other way round. I would write it out in full, but that example really puts the "poly" in polygon.
    Code:
    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography::STGeomFromText('POLYGON((-99.015564 23.398851 , -98.953056 23.392969 , .... , -99.015564 23.398851))', 4326));

  3. #3
    Join Date
    Aug 2009
    Posts
    73

    Red face

    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography:TGeomFromText('POLYGON(( -99.015564 23.398851, -98.953056 23.392969, -98.889214 23.387062, -98.823761 23.380806, -98.759232 23.374264,
    -98.699097 23.367908, -98.644958 23.362230, -98.594833 23.357319, -98.544357 23.352747, -98.489655 23.347843, -98.429970 23.342175,
    -98.368095 23.335840, -98.308228 23.329405, -98.253082 23.323483, -98.201988 23.318270, -98.151428 23.313349, -98.097397 23.307846,
    -98.037872 23.300838, -97.973625 23.291767, -97.907524 23.280695, -97.842812 23.268274, -97.781586 23.255432, -97.723999 23.242920,
    -97.668526 23.230930, -97.613205 23.219128, -97.557236 23.207066, -97.501610 23.194681, -97.448418 23.182446, -97.399109 23.171022,
    -97.353188 23.160719, -97.308296 23.151176, -97.261765 23.141567, -97.212296 23.131157, -97.160660 23.119776, -97.108971 23.107904,
    -97.059181 23.096334, -97.011871 23.085608, -96.966034 23.075645, -96.919930 23.065784, -96.872314 23.055138, -96.823242 23.043072,
    -96.774055 23.029531, -96.726761 23.015068, -96.683105 23.000622, -96.643692 22.987040, -96.607506 22.974628, -96.572304 22.962973,
    -96.535828 22.951229, -96.497017 22.938671, -96.456528 22.925150, -96.416000 22.911160, -96.376968 22.897476, -96.339874 22.884636,
    -96.303871 22.872595, -96.267540 22.860773, -96.229767 22.848454, -96.190407 22.835247, -96.150269 22.821356, -96.110596 22.807419,
    -96.072327 22.794121, -96.035721 22.781750, -96.000313 22.770048, -95.965523 22.758446, -95.931183 22.746496, -95.897804 22.734211,
    -95.866203 22.722088, -95.836884 22.710804, -95.809555 22.700808, -95.783096 22.692009, -95.756218 22.683830, -95.728043 22.675472,
    -95.698471 22.666248, -95.668022 22.655739, -95.637383 22.643764, -95.607086 22.630299, -95.577469 22.615372, -95.548721 22.599024,
    -95.520943 22.581305, -95.494217 22.562267, -95.468620 22.541983, -95.444252 22.520575, -95.421265 22.498318, -95.399887 22.475710,
    -95.380386 22.453539, -95.362930 22.432663, -95.347382 22.413631, -95.333138 22.396303, -95.319275 22.379818, -95.304977 22.363029,
    -95.289871 22.345160, -95.274132 22.326178, -95.258293 22.306730, -95.242874 22.287703, -95.228111 22.269688, -95.213936 22.252701,
    -95.200096 22.236267, -95.186417 22.219830, -95.172989 22.203159, -95.160057 22.186548, -95.147911 22.170620, -95.136620 22.155956,
    -95.125961 22.142712, -95.115509 22.130487, -95.104820 22.118549, -95.093727 22.106201, -95.082375 22.093075, -95.071083 22.079197,
    -95.060181 22.064907, -95.049904 22.050745, -95.040314 22.037327, -95.031342 22.025146, -95.022736 22.014357, -95.014206 22.004677,
    -95.005463 21.995523, -94.996399 21.986290, -94.987152 21.976578, -94.977951 21.966255, -94.969055 21.955355, -94.960678 21.943960,
    -94.952919 21.932144, -94.945816 21.919958, -94.939407 21.907440, -94.933708 21.894630, -94.928749 21.881561, -94.924538 21.868269,
    -94.921082 21.854790, -94.918396 21.841162, -94.916466 21.827419, -94.915321 21.813604, -94.914963 21.799751, -94.915375 21.785902,
    -94.916565 21.772091, -94.918549 21.758358, -94.921295 21.744738, -94.924797 21.731274, -94.929062 21.717993, -94.934059 21.704941,
    -94.939789 21.692146, -94.946228 21.679651, -94.953369 21.667484, -94.961182 21.655682, -94.969635 21.644274, -94.978722 21.633293,
    -94.988426 21.622772, -94.998703 21.612734, -95.009529 21.603210, -95.020874 21.594223, -95.032707 21.585800, -95.044983 21.577965,
    -95.057701 21.570734, -95.070801 21.564133, -95.084244 21.558178, -95.098015 21.552885, -95.112061 21.548269, -95.126343 21.544340,
    -95.140839 21.541111, -95.155487 21.538588, -95.170258 21.536776, -95.185135 21.535656, -95.200096 21.535172, -95.215210 21.535172,
    -95.230591 21.535406, -95.246407 21.535530, -95.262878 21.535210, -95.280106 21.534264, -95.298111 21.532759, -95.316750 21.530987,
    -95.335861 21.529324, -95.355263 21.528057, -95.374863 21.527273, -95.394684 21.526829, -95.414833 21.526411, -95.435524 21.525703,
    -95.456932 21.524551, -95.479118 21.523046, -95.501953 21.521467, -95.525162 21.520111, -95.548439 21.519096, -95.571625 21.518309,
    -95.594910 21.517475, -95.618790 21.516342, -95.643890 21.514811, -95.670631 21.512913, -95.699219 21.510628, -95.729576 21.507654,
    -95.761459 21.503403, -95.794525 21.497227, -95.828476 21.488777, -95.863213 21.478241, -95.898918 21.466217, -95.936012 21.453352,
    -95.975006 21.439999, -96.016228 21.426134, -96.059494 21.411613, -96.104095 21.396484, -96.149063 21.381092, -96.193687 21.365871,
    -96.237740 21.351038, -96.281509 21.336437, -96.325401 21.321709, -96.369728 21.306595, -96.414482 21.291161, -96.459396 21.275734,
    -96.504028 21.260624, -96.548103 21.245832, -96.591797 21.230986, -96.635849 21.215515, -96.681236 21.198900, -96.728676 21.180819,
    -96.778381 21.161133, -96.830147 21.139868, -96.883690 21.117210, -96.939011 21.093449, -96.996353 21.068825, -97.055923 21.043427,
    -97.117592 21.017275, -97.180710 20.990509, -97.244492 20.963451, -97.308289 20.936420, -97.371948 20.909472, -97.435730 20.882357,
    -97.500175 20.854687, -97.565781 20.826160, -97.632805 20.796631, -97.701218 20.766094, -97.770691 20.734699, -97.840683 20.702765,
    -97.910759 20.670687, -97.980637 20.638721, -98.050385 20.606823, -98.120224 20.574768, -98.190308 20.542465, -98.260513 20.510080,
    -98.330551 20.477839, -98.400536 20.445684, -98.471252 20.413280, -98.543991 20.380379, -98.619804 20.347284, -98.699066 20.314922,
    -98.781448 20.284504, -98.866272 20.257109, -98.952904 20.233444, -99.040863 20.213882, -99.129807 20.198587, -99.219482 20.187630,
    -99.309624 20.181042, -99.400002 20.178844, -99.490387 20.181042, -99.580528 20.187630, -99.670204 20.198589, -99.759155 20.213890,
    -99.847160 20.233494, -99.933975 20.257347, -100.019363 20.285389, -100.103104 20.317539, -100.184967 20.353718, -100.264755 20.393826,
    -100.342224 20.437757, -100.417191 20.485395, -100.489433 20.536610, -100.558769 20.591267, -100.625015 20.649218, -100.687958 20.710310,
    -100.747459 20.774380, -100.803345 20.841253, -100.855461 20.910751, -100.903656 20.982689, -100.947800 21.056871, -100.987762 21.133097,
    -101.023438 21.211161, -101.054718 21.290852, -101.081512 21.371956, -101.103745 21.454248, -101.121338 21.537510, -101.134247 21.621511,
    -101.142410 21.706026, -101.145828 21.790825, -101.144455 21.875673, -101.138306 21.960337, -101.127365 22.044586, -101.111679 22.128185,
    -101.091263 22.210911, -101.066177 22.292526, -101.036484 22.372814, -101.002235 22.451548, -100.963547 22.528509, -100.920517 22.603485,
    -100.873245 22.676266, -100.821861 22.746649, -100.766510 22.814438, -100.707329 22.879440, -100.644485 22.941479, -100.578148 23.000376,
    -100.508499 23.055967, -100.435722 23.108097, -100.360039 23.156618, -100.281631 23.201393, -100.200745 23.242296, -100.117592 23.279211,
    -100.032425 23.312035, -99.945549 23.340673, -99.857452 23.365061, -99.769012 23.385155, -99.681580 23.400972, -99.596817 23.412605,
    -99.515862 23.420231, -99.438622 23.424095, -99.363747 23.424530, -99.289795 23.422028, -99.216736 23.417316, -99.146057 23.411346,
    -99.079224 23.405035, -99.015564 23.398851 ))', 4326));
    GO



    got the following error

    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.
    Microsoft.SqlServer.Types.GLArgumentException:
    at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExc eptionForHr(GL_HResult errorCode)
    at Microsoft.SqlServer.Types.GLNativeMethods.Geodetic IsValid(GeoData g)
    at Microsoft.SqlServer.Types.SqlGeography.IsValidExpe nsive()
    at Microsoft.SqlServer.Types.SqlGeography.ConstructGe ographyFromUserInput(GeoData g, Int32 srid)
    at Microsoft.SqlServer.Types.SqlGeography.GeographyFr omText(OpenGisType type, SqlChars taggedText, Int32 srid)

    ************************************************** *********************************
    No two points intersect neither they represent a line still i am getting this error

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This article makes for some interesting reading about the error message. This other article gives an explanation of what to do about it. In short, it looks like the points have to be in reverse order (counter-clockwise).

    It also mentions that there are several differences in geometry and geography datatypes. Especially around figuring area and perimeter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •