WORKING WITH INVALID DATA AND THE SQL SERVER 2008 GEOGRAPHY DATA TYPE, PART 1

  • Article

As many of you have already discovered, the Geography data type in SQL Server 2008 is very strict about the structure of the spatial data which it will accept. The Geometry data type, on the other hand, is much more forgiving about spatial data which does not conform to the rules defined in the OGC Simple Features for SQL Specification. In fact, the Geography data type will not allow non-compliant spatial data to be inserted into the database. Identifying such “bad” data and isolating it for further editing is the first step in getting resolving this issue.

Yesterday, Safe Software released its latest FME 2009 beta (Build 5578) which provides, for the first time, the ability to capture data identified by SQL Server 2008 as invalid. Invalid data is now written to the mapping_log.ffs file where if may be used for further processing. I used this new build of FME to load the rather well-known Admin00 Shapefile into the SQL Server Geography data type. In past builds, when FME encountered a SQL Server database error, it would stop processing and fail to load even the valid data currently in the insert buffer. The following image illustrates, in the FME Universal Viewer, data which was loaded into the SQL Server Geography data type (blue) and data which was rejected by the database server and written to the mapping_log.ffs file (orange).

image

In the next installment, I will attempt to use FME (no guarantees) to correct the rejected data, located in the mapping_log.ffs file, so that it loads into the Geography data type.

Technorati Tags: Geography Data Type,SQL Server,Spatial,Invalid Data