Convert varchar(max) to geography

Question

I have an SQL table containing multipolygons (country borders) in varchar(max) format.

enter image description here

I want to convert them into geography format so that I can intersect them with geographical points (also in geography format).

So far I've tried it like this:

ALTER TABLE dbo.Geoboundaries
ADD [GeoLocation] GEOGRAPHY

INSERT INTO [GeoLocation]  
SELECT GEOGRAPHY::STGeomFromText(shapeGeometry,4326)
FROM dbo.Geoboundaries

Unfortunately, I am always getting following error:

Answer 1

That's not valid WKT. The Well-Known-Text format doesn't have an SRID prefix. You'll have to either split the string and extract the parts, or remove the SRID prefix entirely with REPLACE(shapeGeometry,'SRID=4326;','').

INSERT INTO [GeoLocation]  
SELECT GEOGRAPHY::STGeomFromText(
                      REPLACE(shapeGeometry,'SRID=4326;',''),
                      4326)
FROM dbo.Geoboundaries

If multiple SRIDs are possible, you could extract it as a substring and parse it with cast(SUBSTRING(shapeGeometry,6,4) as int)

If you want to fill Geolocation in the same row though, you need to use UPDATE, not INSERT.

UPDATE Geoboundaries
SET [GeoLocation] = GEOGRAPHY::STGeomFromText(
                        REPLACE(shapeGeometry, 'SRID=4326;', ''),
                        4326)

Answer 2

Your code looks good, but there is one small error. You need to use the @ symbol before the shapeGeometry parameter in the STGeomFromText function. This is because the @ symbol tells SQL Server that the parameter is a variable. So, your code should look like this:

ALTER TABLE dbo.Geoboundaries
ADD [GeoLocation] GEOGRAPHY

INSERT INTO [GeoLocation]
SELECT GEOGRAPHY::STGeomFromText(@shapeGeometry, 4326)
FROM dbo.Geoboundaries

Once you have made this change, your code should work as expected.