Linear Referencing in SQL Server 2008

Jan 222011

imageLinear referencing is used to define features in relation to existing line features. These new features can either be points or lines. For example a water monitoring station can be defined as 300m along a section of river, or a stretch of road can be defined as requiring repairs, from 220m along to 270m along. Most GISs implement this functionality – for details look at the ESRI and GRASS help pages.

In terms of storing linear referenced point features, you only need three fields – an ID of the original line feature, an ID of the  feature to reference along the line, and a M value – the measurement along the original feature. Line features require two measurement fields, a starting distance, and an end distance.

Continue reading »

OGR Comes to SQL Server 2008 Spatial

Sep 252010

image OGR, created by Frank Warmerdam, is an open source library and set of command line utilities for reading and writing geospatial vector data using many different formats . It is the vector equivalent of GDAL which has similar functionality for rasters.

The name of the library is a vestige from when OGR used to stand for OpenGIS Simple Features Reference Implementation. However as OGR is not fully compliant with the OpenGIS Simple Feature specification the name was changed to OGR Simple Features Library (from the GDAL FAQ).

The latest additions to the OGR formats are the SQL Server 2008 geometry and geography. This enables spatial data in SQL Server 2008 to be reprojected, and converted to other formats (shapefiles, MapInfo, KML, GML, GeoJSON and any of the many other formats already supported by OGR) using freely available open source tools. Details on the associated OGR tools can be seen here.

The full MSSQLSpatial OGR driver notes and details are available online.

Continue reading »

MapInfo and SQL Server 2008

Mar 242010

For a new project I am working for the first time with MapInfo 10 “the world’s premier desktop mapping application.”

In an effort to move away from MapInfo’s .tab files (the shapefile equivalent) I was keen to test out the direct connection to geometry stored in SQL Server 2008. Until version 9.5 data could only be stored and accessed in a relational database using SpatialWare. Again to use ESRI terminology this is the ArcSDE equivalent. Fortunately MapInfo was quick off the mark in removing this barrier.

Access to SQL Server 2008 spatial data is provided by integrating FME functionality directly into the product, and having a “Universal Data” option on the File menu. There is also a tool named Easy Loader that allows loading .tab files into SQL Server as geometry.

Continue reading »

Copying Spatial Data between SQL Server Databases

Jan 042010

Aim - to copy a table containing geometry fields between two database servers.filetransfer The task was made slightly trickier as the two servers are on two different domains.

I wanted to use SQL rather than FME, or a SQL Server wizard, as it would be easier to script the process for the several tables I had to copy.

The first task was to set up linked servers. I used the following SQL (when logged in to my destination server, and using SQL Server Management Studio) to add the source server:

EXEC sp_addLinkedServer 'MY_SOURCE_SERVER_NAME'

Continue reading »

MapServer and SQL Server 2008 Plug-in

Dec 182009

The SQL Server 2008 plugin for MapServer is a “native driver modeled on the PostGIS driver to mapserversupport SQL Server 2008’s newly added spatial capabilities.” It works fine for displaying data as WMS layers in OpenLayers however I ran into all sorts of problems when trying to use the GetFeatureInfo WMS query.

With MapServer 5.2.1  (installed using MapServer for Windows – MS4W v2.3.1) no error message was returned, however neither was any data . I initially thought this was due to the following bug in the plugin, which was reported to have been fixed for MapServer 5.4.

Continue reading »

SQL Server Spatial SQL

Dec 182009

I’ve been working more and more with the SQL Server 2008 spatial queries. For standard queries I now rarely use thesql_server_2008 graphic design tools available in SQL Server Management Studio – SQL scripts are far quicker, and easier to reuse. For spatial queries I don’t think there even is a GUI. Anyway the following SQL snippets may be of use to someone. I have only been using the GEOMETRY type, so these may not be relevant to the GEOGRAPHY type, for a distinction see this Microsoft document.

Continue reading »

Visio Data Modelling and VBA

Sep 252009

I use Visio for a number of tasks. For creating geodatabase models in ArcGIS, for system architecture diagrams, and for data flow models. It may not be the easiest program to use, but the alternatives are often too simplistic, or worse.

I wanted to generate a nice diagram of a SQL Server database I had created, and automatically create hyperlinks that would pop up an Ajax box with sample records directly from the database. In order to do this each hyperlink had to have a variable based on the table/entity name. However it soon became apparent that there is no easy way to access the entity name in VBA from its associated shape object. The solution to this is the VisioModelingEngine library which provides access to the UML, but does not provide access to the shapes.
Continue reading »