Convert varchar(max) to geography

Question

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

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:

Post the content and the error as text in the question itself. The image shows that the text is invalid. You can't parse this any more than you can parse `Potato` as a date or number. You'll have to fix the bad text. — Panagiotis Kanavos, Jul 21 '23 at 09:39
Use UPDATE instead of INSERT to add data to your table's new column. — jarlh, Jul 21 '23 at 09:40
The geometry text is not a WKT. Remove the SRID=4326; -part from it and perhaps it will work, but hard to say since you attach an image of incomplete text. — siggemannen, Jul 21 '23 at 09:41
Well-Known-Text doesn't allow an `SRID` prefix. You'll have to split the text, parse the WKT and specify the SRID separately. Your own code already passes the SRID separately. If you're OK with hard-coding the SRID, you could *remove* it from the text with `REPLACE(shapeGeometry,'SRID=4326')` — Panagiotis Kanavos, Jul 21 '23 at 09:49

Panagiotis Kanavos · Answer 1 · 2023-07-21T10:09:36.833

2

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)

edited Jul 21 '23 at 10:09

answered Jul 21 '23 at 09:53

Panagiotis Kanavos

120,703
13
188
236

I'd guess OP wants `UPDATE dbo.Geoboundaries SET [GeoLocation] = ...`. – jarlh Jul 21 '23 at 10:04
1

Thanks. I hadn't noticed that `GeoLocation` is a column in the same table – Panagiotis Kanavos Jul 21 '23 at 10:09
It worked, thanks!! – Turtle Jul 21 '23 at 11:47

score 0 · Answer 2 · answered Jul 21 '23 at 11:02

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.

Convert varchar(max) to geography

2 Answers2