I have pointed out in another blog that the result of an STIntersection() between two polygon objects could result in an object that is not a polygon or multipolygon. Intersection GEOMETRYCOLLECTION (POLYGON ((100 200, 180 300, 100 300, 100 200)), LINESTRING (100 200, 100 75), POINT (100 0)) Note, the the result is a geometryRead More
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
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 find having a vectorising function in my database kit-bag almost indispensable in my work. I have written and revised my Oracle PL/SQL functions that do this many times over the years and so, when I first started using PostGIS, I decided to learn how to program in PL/pgSQL by implementing something familiar: a GetVector()Read More
A colleague is trying to load shapefiles into SQL Server 2008 and asked me the following question: I’m migrating mapinfo and/or shapefiles GDA94 files into sql 2008 geography data type (SRID 4283). I’m using the free Shape2SQL or EasyLoader. Some files fall over with .net exceptions. I suspect something about the coordinates not being validRead More
A few years ago I wrote an article about implementing the PostgreSQL function, generate_series, in Oracle. This function is really useful for when trying to write SQL/Functions that can be implemented across one or more databases with reasonable ease. Here is the function implemented in SQL Server 2008: Now, let’s test this function. I hopeRead More
In Oracle one can gest for invalid geometries via the SDO_GEOM package’s VALIDATE_GEOMETRY (or VALIDATE_GEOMETRY_WITH_CONTEXT) function. To use it one does the following: Where 0.5 is the minimum distance between two vertices (I have deliberately not described the other overloaded version of this function that uses an DIMINFO structure). In SQL Server, if you executeRead More