SQL Server – Fix Geography::Point Null Parameter Error

spatialsql serversql-server-2016t-sql

I was getting this error:

'geography::Point' failed because parameter 1 is not allowed to be null.

On this sql:

SELECT [ID], geography::Point([lat], [long], 4326) AS [loc]
FROM (
  SELECT [ID], CONVERT(float, [lat]) AS [lat], CONVERT(float, [long]) AS [long]
  FROM (
    SELECT [ID], [lat], 
    [long], ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
    FROM [GEO]
  ) AS temp1
  WHERE [sequence] = 1
  AND [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp2
ORDER BY [ID]

But there where no null values, and I was only getting the error on our production machine (Production 13.0.4422.0) and not on our development machine (Dev 13.0.1728.2). After hours of searching and retrying I found that by reordering some stuff this would work:

SELECT [ID], [loc]
FROM (
  SELECT [ID], 
    geography::Point([lat], [long], 4326) AS [loc],
    ,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
  FROM [GEO]
  WHERE [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp
WHERE [sequence] = 1

I would really like to understand what I was doing wrong in the first query and why this worked in the second query?

The first query I tried to get only the rows that have the most recent records based on a sequence number calculated using an effective date column, and then convert lat and long data to a geography type.

In the second query I convert the lat and long data and then filter.

I thought I was being more efficient with the first query.

Best Answer

I have struck similar issues with Geometry and Geography data types before. I think the issue is to do with where (and how) the optimizer decides to build the geometry in the plan.

I was having an issue with insert statement that was basically

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE X is not NULL and Y is not NULL

After playing around and getting a little confused found that

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable;

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

both work but the following doesn't

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(X,Y,0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

For the first query the plan shows that the compute scalar is done before the filter

Test 1

And the expression being computed has had the ISNULL function stripped from it.

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="[Geometry]::Point(CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[X],0),CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[Y],0),(0))">

I think the Point on the Geometry and Geography is flawed in not allowing NULLs to be input. Simply return a NULL as a result would be more correct in my opinion.

If you wish I can put up my entire test script for this.

Related Question