4
@@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?

0

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))' );
  • But why does the convert_implicit only occur with temp tables? – Mazhar Jun 22 '18 at 8:21
  • No idea, if you got the source code -- i'll be happy to tell you. – Evan Carroll Jun 22 '18 at 8:28
  • TSQL and execution plans for both are in the question – Mazhar Jun 22 '18 at 8:29
  • I'm confused at how I could ever tell you why the engine has a different route leading to an implicit_coercion error without the source code to SQL Server 2005. I can only speculate. My speculation is that it wants you to cast your int to a bit (because that's what it is in the table) -- if that still generates the error on the temp table, try casting the geog/geom to a VARBINARY(max) if that still generates the error -- I'm out of ideas. Run to PostgreSQL. There I have the source code. We can only speculate, and I tried my best. ;) – Evan Carroll Jun 22 '18 at 8:38
  • 1
    Ah, I didn't realise you were referring to SQL Engine code :-) – Mazhar Jun 22 '18 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.