SpatialDB Advisor
(ABN 90 970 658 939)
Current SQL Server Blogs
The Sad State of SQL Spatial Standards - Take 2
Tip #1: SQL Server 2008 "Katmai" - Setting SRIDs
Tip #2: Spatial Indexing and Primary Keys
Tip #3: What object is that?
Tip #4: What Coordinate System is that?
Tip #5: Where or where has my little column gone
Tip #6: Correcting invalid geometries
The Sad State of SQL Spatial Standards - Take 2 · 168 days ago by Simon Greener
There is a useful exchange going on over on the Microsoft Forums in relation to the paucity of information on geographic objects (as against planar objects) in the OGC SQL standard: this is in the context of SQL Server 2008 “Katmai” Spatial implementation. This exchange occured over Microsoft’s reading of the OGC standards in respect of the coordinate ordering of data in their geography data type (as against their geometry planar data type). The upshot is that the Microsoft engineers chose to have the coordinates in a geography ordered Latitude/Longitude which is at odds with how Oracle, PostGIS and many other vendors order the ordinates (they use Longitude/Latitude).
In the exchange I note that Google Maps uses Latitude/Longitude and observe that it is not so much the coordinate ordering that is the issue, or even “standards compliance”, rather it is about the functionality of the implementation.
Comment [2]
Tip #6: Correcting invalid geometries · 168 days ago by Simon Greener
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:
SELECT sdo_geom.validate_geometry(a.geom,0.5)
FROM projpoly2d;
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 execute a SELECT statement and that statement processes invalid geometry objects you will get an error message like this:
PDBA: Msg 6522, Level 16, State 1, Line 4
A .NET Framework error occurred during execution of user defined routine or aggregate ‘geometry’:
System.ArgumentException: 24144:
This operation cannot be completed because the instance is not valid.
Use MakeValid to convert the instance to a valid instance.
Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException:
at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
at Microsoft.SqlServer.Types.SqlGeometry.STBuffer(Double distance)
SQL Server Katmai includes the OGC function STIsValid() which can be used to find invalid geometries. Katmai also includes an “extended” (non-OGC 1.1) function called MakeValid() which can be uses to correct any geometries are STIsValid() report as invalid. To correct invalid geometries one can do this:
UPDATE projpoly2d
SET geom = geom.MakeValid()
WHERE geom.STIsValid() = 0;
Note that the MakValid function has no parameters so one cannot supply SQL Server with coordinate precision data to control the “shift slightly” warning in the above exception.
Tip #5: Where or where has my little column gone · 174 days ago by Simon Greener
In Oracle I often want to know the name of the columns that are of type SDO_GEOMETRY. Why? Because the Oracle metadata tables exposed to each user via USER_SDO_GEOM_METADATA may not have an entry for a particular table/column pair.
The query is relatively easy:
gis@XE> select table_name, column_name, data_type
2 from user_tab_columns
3 where data_type = 'SDO_GEOMETRY'
4 order by 1;
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ -------------
BASS_GRID GEOMETRY SDO_GEOMETRY
CONVEX GEOM SDO_GEOMETRY
DELAUNAY GEOM SDO_GEOMETRY
FAUNA GEOM SDO_GEOMETRY
FAUNA MAPSHEET SDO_GEOMETRY
LINE_REL GEOM SDO_GEOMETRY
PILOT_EDGE GEOMETRY SDO_GEOMETRY
7 rows selected.
In SQL Server one can get the same information via the following query:
select a.name as table_name,
b.name as column_name,
c.name as column_type
from sysobjects a,
syscolumns b,
systypes C
where a.type = 'U'
and a.id = b.id
and b.usertype = c.usertype
and c.name in ('geometry','geography')
order by 1,2;
table_name
column_name
column_type
ADMIN
Geom
geography
ADMIN
Geom
geometry
Convex
Geom
geography
Convex
Geom
geometry
COUPE
Geom
geography
COUPE
Geom
geometry
Delaunay
Geom
geography
Delaunay
Geom
geometry
my_multi_points
geom
geography
my_multi_points
geom
geometry
my_points
geom
geography
my_points
geom
geometry
my_polygons
geom
geography
my_polygons
geom
geometry
TRANSPORT
Geom
geography
TRANSPORT
Geom
geometry
Hope this is of use.
Tip #4: What Coordinate System is that? · 174 days ago by Simon Greener
SQL Server’s spatial reference system is based on EPSG.
When using Oracle Spatial I have constant recourse to MDSYS.CS_SRS – the master table that holds all the spatial reference systems Oracle supports – to discover the right SRID to use when constructing Sdo_Geometry objects.
For example, if I want to find the SRID for the ESPG defined geographic srid based on GDA94 I do this:
gis@xe> select srid, cs_name
2 from mdsys.cs_srs
3 where cs_name like '<span>GDA</span>'
4* and auth_name like '<span>EPSG</span>'
gis@XE> /
SRID CS_NAME
---------- -------------------------
4283 GDA94
62836405 GDA94 (deg)
62836413 GDA94 (3D deg)
Now, in SQL Server 2008 “Katmai” I can find the same information by querying the sys.spatial_reference_systems system table as follows:
select spatial_reference_id, well_known_text
from sys.spatial_reference_systems
where well_known_text like '<span>GDA</span>';
spatial_reference_id
well_known_text
4283
GEOGCS[“GDA94”, DATUM[“Geocentric Datum of Australia 1994”, ELLIPSOID[“GRS 1980”, 6378137, 298.257222101]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]]
4939
GEOGCS[“GDA94”, DATUM[“Geocentric Datum of Australia 1994”, ELLIPSOID[“GRS 1980”, 6378137, 298.257222101]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]]