Post reply

Spatital Data Conversion

  • January 31, 2017 at 12:15 pm

    #334646

    Hi,

    i am copying spatital table from 2016 to 2008 by export wizard using CAST fucnton.
    the table is successfully copying after that while converting datatype geometry we are getting error.can you please suggest us how to resolve this.

    alter table [dbo].[Spacename]
    alter column space VARBINARY(MAX)

    alter table [dbo].[Spacename] 
    alter column space geography null

    Msg 6522, Level 16, State 1, Line 5
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
    System.ArgumentException: 24204: The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view.
    System.ArgumentException:
     at Microsoft.SqlServer.Types.SqlGeography.set_Srid(Int32 value)
     at Microsoft.SqlServer.Types.SqlGeography.Read(BinaryReader r)
     at SqlGeography::.DeserializeValidate(IntPtr , Int32 , CClrLobContext* )

  • February 1, 2017 at 2:47 pm

    #1926204

    The way you are trying to convert the Geometry to a Geography will cause problems even if it does not error.
    The current error that you have is due to the SRID being invalid for a Geography.  If you fix that problem the next problem you will likely to hit is an error that `One of the identified items was in an invalid format.`
    If you do not get that error then the Geography will be rotated.  The X and Y (Long and Lat) will be exchanged.
    If you look at the spatial results from these queries, you should see what I mean

    SELECT Geometry::STGeomFromText('LINESTRING(42 -42, 43 -42, 43 -41)',4326);
    SELECT CAST(CAST(Geometry::STGeomFromText('LINESTRING(42 -42, 43 -42, 43 -41)',4326) AS VARBINARY(MAX)) AS Geography);
    SELECT Geography::STGeomFromText(Geometry::STGeomFromText('LINESTRING(42 -42, 43 -42, 43 -41)',24204).ToString(),4326);

    Assuming that the coordinates that you have in your Geometry are Lat/Longs and that they are likely to be WGS84 (SRID 4326), then the following should get you what you want

    ALTER TABLE dbo.Spacename ADD Space_Geog Geography;

    UPDATE  dbo.Spacename
    SET Space_Geog = Geography::STGeomFromText(space.ToString(), 4326)
    WHERE space IS NOT NULL;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply