Search code examples
sqlsql-servernullssmsgeography

How to create calculated Geometry column that ignores NULL values in SQL Server

I have a 'Sites' table with columns for Latitude and Longitude in SSMS 2017. I would like to add a calculated column of Geography data type based on the Latitude and Longitude values. I have used this command:

ALTER TABLE Sites 
    ADD Geo2 AS (geography::STGeomFromText('POINT(Sites.Latitude Sites.Longitude)', 4326))

This command completes successfully, but when I try to select * from the table, it returns the error shown in the screenshot failed select command error message. I suspect it is because some sites do not have Lat/Long data (are NULL values). Is there a way around this problem?

Solution

  • Try filtering the values first. Something like this:

    ALTER TABLE Sites 
 ADD Geo2 AS IIF(Latitude  IS NOT NULL AND Longitude IS NOT NULL, [geography]::Point(Longitude,Latitude,4326), NULL)