The OGC standard has an STDimension() method defined on geometry/geography objects and this is implemented in SQL Server 2009. This function returns the inherent (or topological) dimensionality of an object: for a point this is 0; for a linestring this is 1; and for a polygon this is 2. name dim Point 0 LineString 1Read More
I often find I need to manipulate the points of a geometry/geography object directly in SQL. Oracle has the neat TABLE function mdsys.sdo_util.GetVertices( sdo_geometry ) for this. Since SQL Server 2008 does not have such a function I found I had to write it myself. I prefer to program in TSQL using the OGC geometry/geographyRead More
I have been doing some SQL Server 2008 programming lately and have come across the fact that TSQL does not allowing a programmer to create overloaded functions. This is an issue, when programming SQL Server 2008 Spatial, because of use of its implementation of two different data types for geodetic and projected data: geography andRead More
I have often advocated the use of a spatial sort when loading static spatial data into Oracle (See 1. Spatially sort read-only data. in my article on performance tips). The idea here is to try and place spatial data that is close together in space (called spatial autocorrelation), close together on disk. Then, when theRead More
Trivial I know, but here is a method for writing the result of a query (select) to a text file where the query contains either geometry or geography data. To do this we will use the xp_cmdshell stored procedure to call the external sqlcmd tool. To do this, first, we must allow SQL Server toRead More
NOTE: If your database is 2016 or newer, you can use the new STRING_SPLIT function. See an example at the end of this post. String tokenizer for SQL Server 2008. We often have need of string tokenizers in applications. I had need of one in some TSQL development I am did for a customer. IRead More
I was chatting with a friend and colleague on the telephone today when he mentioned that his project was being delayed because they had been provided some SDO_GEOMETRY data in which the latitude and longitude ordinates had been swapped out of their normal positions. For those who don’t know, latitude/longitude data (known as geodetic data)Read More