January 31, 2017 at 12:15 pm
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
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