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

Question

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?

gotqn · Accepted Answer · 2021-05-19 18:17:43Z

1

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)

answered May 19, 2021 at 18:17

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related