locked
Convert a string geography type RRS feed

  • Question


  • Hallo ,
    I Need to insert data to a table and an insert SQL Statement would look like this :

    INSERT INTO post_code_areas VALUES (3879, '55118', 55118, 'Mainz', 'SRID=4326;MULTIPOLYGON(((8.24369904 50.01507567,8.24297796 50.01569748,8.24300388 50.01686478,8.24324796 50.01786045,8.24417676 50.01913449,8.24540508 50.0199165,8.24620932 50.02011108,8.24732028 50.01954273,8.24972904 50.018589,8.25265044 50.01705936,8.25563808 50.01557544,8.25834564 50.01408765,8.2626876 50.01185988,8.26304436 50.01168438,8.26325892 50.0115051,8.26660044 50.01485058,8.26992216 50.01264189,8.27094096 50.01189426,8.27396208 50.00947956,8.26820388 50.00846859,8.26462728 50.00472261,8.26206984 50.00207517,8.26221168 50.00202945,8.26201728 50.00162121,8.26175772 50.00103378,8.26107516 50.00061798,8.26057512 50.00060268,8.25966072 50.00113674,8.24761296 50.01162723,8.245017 50.01383592,8.24369904 50.01507567)))');

    Problem is that i get this fehler

    .NET Framework-Fehler beim Ausfhren der benutzerdefinierten Routine oder des benutzerdefinierten Aggregats 'geometry':

    System.FormatException: 24114: Die Beschriftung SRID=4326;MULTIPOLYG in der WKT-Eingabe (well-known text) ist nicht gltig. Gltige Beschriftungen sind POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON oder GEOMETRYCOLLECTION.

    System.FormatException:

       bei Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)

       bei Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)

       bei Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)

       bei Microsoft.SqlServer.Types.SqlGeometry.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)

       bei Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)

    .

    How could i let my database accept such statetments???
    ist there any way??
    thx :)

     


    • Edited by Bafla13 Tuesday, December 4, 2012 9:08 PM
    • Moved by Bob Beauchemin Wednesday, December 5, 2012 5:30 PM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Tuesday, December 4, 2012 9:05 PM

Answers

  • You need to get rid of the ‘SRID=4326;’ at the beginning of the string. SQL Server doesn’t recognize this because it’s non-standard.
     
    BTW, in future you can get better results posting this to the SQL Spatial Forum (http://social.technet.microsoft.com/Forums/en-US/sqlspatial/threads), I’ll be moving this posting there tomorrow.
     
    Cheers, Bob
    Wednesday, December 5, 2012 2:51 AM
  • Hello,

    As Bob already wrote, you have to remove SRID in front of the MULTIPOLYGON.

    If you need the geography with an assigned SRID, then you could use static function STGeomFromText; here you can add SRID as second parameter:

    SELECT geography::STGeomFromText('MULTIPOLYGON(((8.24369904 50.01507567,8.24297796 50.01569748,8.24300388 50.01686478,8.24324796 50.01786045,
                                      8.24417676 50.01913449,8.24540508 50.0199165,8.24620932 50.02011108,8.24732028 50.01954273,8.24972904 50.018589,
    								  8.25265044 50.01705936,8.25563808 50.01557544,8.25834564 50.01408765,8.2626876 50.01185988,8.26304436 50.01168438,
    								  8.26325892 50.0115051,8.26660044 50.01485058,8.26992216 50.01264189,8.27094096 50.01189426,8.27396208 50.00947956,
    								  8.26820388 50.00846859,8.26462728 50.00472261,8.26206984 50.00207517,8.26221168 50.00202945,8.26201728 50.00162121,
    								  8.26175772 50.00103378,8.26107516 50.00061798,8.26057512 50.00060268,8.25966072 50.00113674,8.24761296 50.01162723,
    								  8.245017 50.01383592,8.24369904 50.01507567)))'
    								  , 4326)


    Olaf Helper

    Blog Xing

    Wednesday, December 5, 2012 6:24 AM

All replies

  • You need to get rid of the ‘SRID=4326;’ at the beginning of the string. SQL Server doesn’t recognize this because it’s non-standard.
     
    BTW, in future you can get better results posting this to the SQL Spatial Forum (http://social.technet.microsoft.com/Forums/en-US/sqlspatial/threads), I’ll be moving this posting there tomorrow.
     
    Cheers, Bob
    Wednesday, December 5, 2012 2:51 AM
  • Hello,

    As Bob already wrote, you have to remove SRID in front of the MULTIPOLYGON.

    If you need the geography with an assigned SRID, then you could use static function STGeomFromText; here you can add SRID as second parameter:

    SELECT geography::STGeomFromText('MULTIPOLYGON(((8.24369904 50.01507567,8.24297796 50.01569748,8.24300388 50.01686478,8.24324796 50.01786045,
                                      8.24417676 50.01913449,8.24540508 50.0199165,8.24620932 50.02011108,8.24732028 50.01954273,8.24972904 50.018589,
    								  8.25265044 50.01705936,8.25563808 50.01557544,8.25834564 50.01408765,8.2626876 50.01185988,8.26304436 50.01168438,
    								  8.26325892 50.0115051,8.26660044 50.01485058,8.26992216 50.01264189,8.27094096 50.01189426,8.27396208 50.00947956,
    								  8.26820388 50.00846859,8.26462728 50.00472261,8.26206984 50.00207517,8.26221168 50.00202945,8.26201728 50.00162121,
    								  8.26175772 50.00103378,8.26107516 50.00061798,8.26057512 50.00060268,8.25966072 50.00113674,8.24761296 50.01162723,
    								  8.245017 50.01383592,8.24369904 50.01507567)))'
    								  , 4326)


    Olaf Helper

    Blog Xing

    Wednesday, December 5, 2012 6:24 AM