6

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.

1
  • Your queries are not semantically equivalent. The first returns a row if the most recent record has values in lat and long. The second returns the most recent row that has values in lat and long. So if you have null values on the most recent record, the result of the two queries will not be the same. Oct 9, 2017 at 13:41

5 Answers 5

2

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.

1

I was able to get my query to no longer throw errors by adding an equivalent to the following at the end of the query.

WHERE Id  IN (
   SELECT Id FROM [Geo] 
       WHERE [lat] IS NOT NULL AND [long] IS NOT NULL
)
1

I just had same issue, and mucked about with trying to change the execution order in the query plan, but in the end, though a bit slower, I created my points from a string, and it was happy:

geography::STPointFromText('POINT(' + str(long, 10, 6 ) + ' ' + str(lat,10,6 ) + ')', 4326)  
1
  • good and simple solution Dec 24, 2021 at 2:20
0

I have just run across this issue and it does seem to be a bug as the values are not NULL or outside of the valid range allowed.

I found that by inserting the data into a temp table and then performing the geography calculation on that instead, it resolves the issue and does not affect speed, at least in my case.

This query fails even though the geography parameters are never NULL:

SELECT
    PostCodeFrom = RPCF.PostCode,
    LatitudeFrom = PCF.latitude,
    LongitudeFrom = PCF.longitude,
    Loc = geography::Point ( PCF.latitude, PCF.longitude, 4326 )
FROM #RandomPostCodes RPCF
INNER JOIN ukpostcodes PCF
    ON PCF.postcode = RPCF.PostCode
WHERE
    RPCF.RowNum = 1

This works after adding inserting into a temporary table and performing the geography calculation on the temp table:

DROP TABLE IF EXISTS #PostCodes
SELECT
    PostCodeFrom = RPCF.PostCode,
    LatitudeFrom = PCF.latitude,
    LongitudeFrom = PCF.longitude
    INTO #PostCodes
FROM #RandomPostCodes RPCF
INNER JOIN ukpostcodes PCF
    ON PCF.postcode = RPCF.PostCode
WHERE
    RPCF.RowNum = 1

SELECT
    PostCodeFrom = PC.PostCodeFrom,
    LatitudeFrom = PC.LatitudeFrom,
    LongitudeFrom = PC.LongitudeFrom,
    Loc = geography::Point ( PC.LatitudeFrom, PC.LongitudeFrom, 4326 )
FROM #PostCodes PC
-1

One more thing to keep in mind. It is not a matter that your coordinates are not null. They also need to make sense.

So if you are using lat/long, then latitude cannot exceed 90 or -90. Longitude cannot exceed 180 or -180.

Be sure your lat/long are not transposed.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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