Sql-server – CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table

spatialsql serversql-server-2008-r2tempdb

@@Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a Geography datatype?

DECLARE @xPolygon NVARCHAR(MAX) 
     = '53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670'

CREATE TABLE #xSpatialTable (
     id         INT IDENTITY (1,1) 
    ,GeogCol1   geography 
);

INSERT INTO #xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

The Second Compute Scalar operator in the actual execution plan for Expr1005 shows this

Scalar Operator(CONVERT_IMPLICIT(geography,[geography]::STPolyFromText((N'POLYGON(( '+[@xPolygon])+N'))',(4326)),0))

There is no such CONVERT_IMPLICIT if I change to using a user table instead of a temp table.

CREATE TABLE dbo.xSpatialTable (
     id         INT IDENTITY (1,1) 
    ,GeogCol1   geography 
);

INSERT INTO dbo.xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

Here's the actual execution plan

If I try the same test on a SQL 2014 database on the same machine, I don't get a convert_implicit whether I use a temp table or a user table.

Why the difference on 2008R2? and how do I get rid of the Convert_Implicit when using a temp table on 2008R2?

Best Answer

I don't think this has anything to do with the spatial extensions. I think this is this problem here,

From that post, all these work without generating CONVERT_IMPLICIT

SELECT * FROM [Table] WHERE IsSomething = 'TRUE';

SELECT * FROM [Table] WHERE IsSomething = Cast(1 as bit);

DECLARE @TRUE bit;
SELECT @TRUE = 1;
SELECT * FROM [Table] WHERE IsSomething = @TRUE;

You can read about the reasons for this problem here.

If it is the spatial type you may try a CAST to VARBINARY(max).. CAST( geography::STPolyFromText('') AS VARBINARY(max))


If you can't afford to upgrade to a newer version of SQL Server, PostgreSQL with PostGIS is perhaps an option as it is free and open source and has the best GIS implementation.

CREATE TEMP TABLE xSpatialTable (
     id         int           PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     GeogCol1   geography
);

INSERT INTO xSpatialTable (GeogCol1)
VALUES ( 'POLYGON((53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670))' );