\nYesterday a friend asked me if it was better to always use a value of GEOMETRYCOLLECTION EMPTY as an alternative to making a geometry\/geography column nullable. OGC has its own concept of database NULL, that is [GEOMETRY] EMPTY, where [GEOMETRY] can be any valid geometry subtype (that is 'POINT EMPTY', 'LINESTRING EMPTY', etc). When I asked why it mattered, he said he was tired of putting ..WHERE…IS NOT NULL in every UPDATE of SQL Server spatial data type. Sounds like reason for a blog entry.\n<\/p>\n
\nNULL is a valid value of any SQL Server data type, defining a variable without initializing it makes it NULL.
\nDECLARE @x int; — value of @x is NULL
\nDECLARE @geog Geography — value of @geog is NULL\n<\/p>\n
\nAccording to SQL-1999 (and above) a SQL data type can have properties and methods; accessor methods and mutator methods. Calling a accessor method on a NULL variable returns NULL. Calling a mutator method on a NULL variable is an error, in SQL Server the error text is "Mutator … cannot be called on a NULL value". If I have a table of 100000 geography instances and one row contains NULL, attempting to update that column without using WHERE..IS NOT NULL produces this error.\n<\/p>\n
\nUPDATE dbo.Geotable SET geog.STSrid = 4326; — error if any row contains NULL geog\n<\/p>\n
\nEMPTY GEOGRAPHY is a different concept then NULL because OGC defines a common architecture for geography, independent of SQL. In fact, the "OpenGIS® Implementation Specification for Geographic information – Simple feature access – Part 1:Common architecture" spec does not even use the word NULL once. EMPTY can be tested with the OGC standard method IsEmpty (STIsEmpty in SQL Server), and is defined as "the empty point set ∅ for the coordinate space". Because it is not database NULL, you can call methods on it without error:\n<\/p>\n
\nDECLARE @a geometry = 'POINT EMPTY';
\nSET @a.STSrid = 4326; — works fine\n<\/p>\n
\nSome other interesting behaviors of [GEOGRAPHY] EMPTY\n<\/p>\n
\ndeclare @a geometry, @b geometry;
\nset @a = 'POINT EMPTY';
\nset @b = 'LINESTRING EMPTY';\n<\/p>\n
\nselect @a.STEquals(@b); — true\n<\/p>\n
\nselect @a.STDimension(); — -1 (negative one)\n<\/p>\n
\nselect @a.STIsEmpty(); — true, of course
\nselect @a.STIsSimple(); — true\n<\/p>\n
\n— Disjoint is true if "Intersection of @a and @b is the empty set"
\nselect @a.STDisjoint(@b); — 1\n<\/p>\n
\n— Both Within and Contains are false
\nselect @a.STWithin(@b); — 0
\nselect @a.STContains(@b); — 0\n<\/p>\n
\nSome other interesting uses of [GEOGRAPHY] EMPTY\n<\/p>\n
\n— the boundary of a point is the empty set
\ndeclare @x geometry = 'POINT(1 1)';
\nselect @x.STBoundary().ToString(); — GEOMETRYCOLLECTION EMPTY\n<\/p>\n
\n— but the boundary of a polygon is a closed linestring
\ndeclare @y geometry = 'POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))';
\nselect @y.STBoundary().ToString(); — 'LINESTRING (1 1, 2 1, 2 2, 1 2, 1 1)'\n<\/p>\n
\n— and the boundary of a closed linestring (ring) is GEOMETRYCOLLECTION EMPTY
\ndeclare @z geometry = @y.STBoundary();
\nselect @z.STBoundary().ToString(); — GEOMETRYCOLLECTION EMPTY\n<\/p>\n
\nOGC's SQL Option spec (and SQL Server) differenciates between NULL and [GEOMETRY] EMPTY. For example, in the case above, the boundary of any NULL geometry IS NULL, not EMPTY.\n<\/p>\n
\nShould you use NULL or EMPTY as a Geometry\/Geography default? Depends on the semantics that you want to enforce, but, to me, lack of a value in SQL is NULL. And you can even include a constraint that the column value may not be either or both special values. EMPTY is a good way to ensure not having to use "IS NOT NULL" in every UPDATE statement, but that's as far as it goes.\n<\/p>\n
\nWhat do you think?<\/p>\n","protected":false},"excerpt":{"rendered":"
Yesterday a friend asked me if it was better to always use a value of GEOMETRYCOLLECTION EMPTY as an alternative to making a geometry\/geography column nullable. OGC has its own concept of database NULL, that is [GEOMETRY] EMPTY, where [GEOMETRY] can be any valid geometry subtype (that is 'POINT EMPTY', 'LINESTRING EMPTY', etc). When I asked why […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,36],"tags":[],"class_list":["post-659","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-spatial"],"yoast_head":"\n