Enterprise GIS

Using Powershell to load Ordnance Survey Code-Point CSV data

2011-01-15T19:31:00.001Z

Another off-topic jaunt, but I have been playing with Microsoft's Powershell recently and quite enjoyed it.

Powershell is an interactive scripting language with strong ties to the .Net framework - meaning that .Net code can be accessed whilst scripting. I found it easy to get going with and used it to import code-point data from 120 CSV files into a SQL Server database.

The script is based on Doogal Bell's excellent blog post in which he provides a C# program to import code-point. I borrowed Doogal's .Net Coordinates library which was downloaded and accessed directly from within the script, simply by using the Add-Type command to locate the DLL file. This library enabled me to easily convert the OSGB coordinates into latitude and longitude.

The script is provided below and could be used by a simple cut and paste and changing the first few lines to reference your own environment.

# Add reference to DotNetCoords
Add-Type -path "C:\Users\GrahaMorgan\Software\.net code\DotNetCoords.dll"

# Specify location of codepoint csv files
$dataLocation = "C:\Data\Ordnance Survey\codepoint_gb\Code-Point Open\Data"

# set up database connection
$connectionString = "Server=GRAHAMORGAN-PC\Spatial1; " +
                    "Database=pafdb; " +
                    "Integrated Security=SSPI;"
          
# Connect to database and open it
$sqlconnection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$sqlconnection.Open()

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlconnection 

# get hold of all csv files
$files = get-childitem $datalocation *.csv | foreach-object {$_.name}

foreach ($file in $files)
{
    $filename = join-path $datalocation $file
    write-output("Processing " + $filename)

    $data = import-csv $filename -header ("postcode","B","C","D","E","F","G","H","I","J","easting","northing","M","N","O","P","Q","R","S")
    foreach ($row in $data)
    {  
        $OSRef = new-object DotNetCoords.OSRef($row.easting,$row.northing)
        $LatLng = $OSRef.ToLatLng();
        $latLng.ToWGS84();
              
        $sql = "insert into codepoint_coords (postcode, longitude, latitude, coordinates) " +
               "VALUES ('{0}', {1},{2}, geography::STPointFromText('POINT({1} {2})', 4326))" -f $row.postcode, $latLng.longitude, $latLng.latitude
         
        # Execute sql insert      
        $sqlcmd.CommandText = $sql
        $res = $sqlcmd.ExecuteNonQuery()
    }   
}

$sqlconnection.close()

Microsoft have made Powershell available from within SQL Server and it will be interesting to see what people do with it. I guess one obvious use would be to take advantage of the database import tools which would probably provide much faster performance. On the other hand, the flexibility of the script to manipulate the data, such as changing coordinate system is pretty nice.

Note syntax highlighting via Alex Gorbatchev's open-source SyntaxHighlighter; instructions on MLA Wire.

Fun with Manifold GIS

2010-01-05T22:14:00.000Z

This post may not fall strictly within the Enterprise GIS topic but I have just completed my first project with Manifold GIS . This was a little piece of work to create a series of maps for a county sports partnership in the UK; we chose Manifold purely based upon price, and curiosity...

First off - Manifold was upto the job and the client was delighted with the results. As someone who has used GIS technologies of all shapes and sizes for almost 20 years I have to admit that I was rather stumped by the user interface but, with patience, I was able to find all of the functionality that I needed.

Highlights for me were:

A great price, at just USD$400
Windows based client application
Is able to read and write a variety of file and database formats
Includes a host of analysis functions
Supports several Windows scripting/programming languages

Limitations for my purpose were:

Was not intuitive for me to use
Help documentation not always easy to find and used unfamiliar terms
Cartographic facilities were not bad but didn't provide fine grained control
Some processing steps went into long loops and failed
Exports to PDF but not to GeoPDF yet

I think the Manifold team have done a great job producing this functionality at the price. Next I want to explore Manifold further to get a better understanding of the customisation facilities and scalability for larger batched processing.

Enterprise GIS is not a Package

2009-12-14T09:28:00.001Z

It seems that some organisations continue to regard GIS as a commodity application package: something that can purchased, plugged in, and voila - GIS for the business. I started thinking about how these organisations may have come to adopt this view.

Perhaps there are 3 broad views of GIS:

Desktop application
Standalone system
Enterprise capability

Looking back, GIS has traditionally been provided by 'fat client' desktop applications such as MapInfo, ArcGIS, GeoMedia, Manifold, gvSIG, etc. (there is a nice list of GIS software on Wikipedia). These applications have in general been geared towards the use of proprietary file based formats such as shp, tab, e00, dwg, etc. As GIS grew from project based applications to departmental systems, they evolved to employ multi-user databases for storage, but still maintained the two-tier, fat-client architecture. GIS vendors marketed their GIS Systems as replacements to the drawing office paper and mylar maps i.e. as standalone, seamless map repositories.

I think those original standalone GIS systems can be thought of as the first beasties that crawled out of the sea and into a new world of land and air. They took the first step from paper to the digital world - but that was just the beginning. The data has evolved to take advantage of its new surroundings; we realised that we have many other datasets that could be indexed geographically, and many other areas of the business that could benefit from the power of spatial technologies to integrate, analyse and visualise data. GIS was no longer just the digital drawing office, it provided capabilities that were sought after across the whole organisation.

This evolution has been driven by a desire to speed up business workflows, enhance services, reduce costs and improve decision making. These drivers have required that location should be captured and used during every task in a workflow where it helps - and that turns out to be pretty much everywhere.With this realisation it is clear that GIS is no longer a standalone system - it is a set of enterprise-wide capabilities that need to be accessible to all business systems.

I was rather puzzled recently when a UK Police force went to the market for a 'GIS System'. In talking with the procurement officer, he appeared to regard this as any other commodity purchase; something that could be acquired and plugged in within 30 days to replace their existing GIS - software vendors only need apply. Well good luck to them. You can easily imagine how central to police operations geography is (CBS's TV show 'The District' featured GIS scenarios prominently). Buying a standalone GIS is not going to provide them with the benefits that they will want and expect, but until organisations evolve their definition of GIS then I guess some procurement folks are going to struggle to buy what is really needed.

Enterprise Architecture and GIS

2009-12-09T11:17:00.000Z

Enterprise Architecture is a continuous practice of aligning the resources of a business with the objectives of that business. At a broad level Enterprise Architecture considers:

Business Architecture
Commercial model, process model, organisation mode
Technology Architecture
Application mode, infrastructure model
Data Architecture
Data models, information products

GIS is very much relevant to Enterprise Architecture because it can both enable and hinder each of these tiers. Business processes have traditionally needed to work around limitations imposed by GIS technologies because the transfer of spatial data between systems, departments, and hence successive steps in a workflow have been so problematic. This has been caused primarily by limitations within the technology and data tiers.

The concept of a 'GIS System' is starting to evaporate as spatial facilities emerge within other components. Databases (e.g. Oracle, SQL Server, MySQL, Postgis) and data formats (e.g. GML, GeoPDF) are rapidly maturing and are available for use by a much wider community than the traditional 'hard core GISers' and thus we are starting to see components of the IT stack become spatially-aware. This is the natural evolutionary path for GIS and is the model that we should be designing for. As these technologies mature I think it is important that the Enterprise Architects and IT Architects are aware of the current and emerging opportunities, and limitations, afforded by spatial technologies.

AGI 2009 Geocommunity Conference - Mainstream? Not yet.

2009-09-29T17:16:00.005+01:00

A good AGI GeoCommunity conference for sure with some thought provoking presentations. There were lots of familar faces and it was good to catch up over a beer or two. I was wondering who would be there this year and what the community would consider the burning questions of the age to be...

It wasn't as I had expected. Steven Feldman, the conference chair, opened the first day with a solid introduction, explaining that the central theme would be 'What' people are doing with GIS, and hoping that over the next year we would be considering more 'Why' questions. This year's conference would not really consider the 'How' as GIS has now merged with the mainstream, and so presumably then, implementation problems are a thing of the past. At least that is how I heard it and I must admit that this sentiment does not ring true to me. Not yet.

In my own travels as a Geospatial Solution Architect I haven't noticed GIS implementation challenges lessen very much - sure some of the standards are beginning to take hold but the problem areas of real multi-user concurrent data editing, application interoperability, data integration, data licensing, and GIS within enterprise architecture are still very much significant challenges. I would have loved to see an implementation stream cover progress in these areas.

The conference was not organised along the lines of the AGI Special Interest Groups which perhaps in itself indicates that we may need to review the topic areas and roles of the SIGs to ensure that they remain relevant to the community. As someone that feels that technical developments are of great interest I enjoyed the Geoweb stream which addressed some relevant (or potentially relevant) technical developments, principally in the area of mashups. I would love to hear more about developments in data storage, server based services (and issues of scalability and availability), systems and data integration - technical issues relevant to enterprise deployment of GIS capabilities.

The AGI Northern Group has organised a follow up conference (Where2.0Now?) in Harrogate on 10th November on the subject of Web2.0 and disruptive technologies. The AGI Technical SIG is also in the process of organising a conference for early next year on the subject of Mobile GIS. I look forward to both of these events and to hear more of the technical developments effecting our industry.

GIS as a member of the mainstream? Based upon the content and attendance of this conference we can't possibly argue that GIS is mainstream yet. Sure, many more people use spatial data and technologies daily in the form of SatNav and online web maps but the IT mainstream was not at the conference. There were no stands from the mainstream IT players such as IBM, Microsoft, Oracle, FileNet, Google, Informatica, Accenture, Logica, Dell, Sun, Embarcodero, Adobe, etc, etc. in the small exhibit hall. When we start to see these vendors and integrators showing up in numbers then we will know that GIS has arrived. I am not so sure what we will all do after that though!

Thanks to the conference organizers.

What is this Enterprise GIS Thing?

2008-05-18T23:34:00.001+01:00

I suppose if I am going to blog on about this topic then I should really lay out what it is I am talking about, so here goes. It is not going to be straight forward though but hopefully, as my ideas develop, I’ll be able to refine this over time.

“Enterprise GIS” is one of those terms that seems to have been around for a long time, at least since the late 1990’s but it has always had more to do with marketing hype than any commonly understood concept. During my time at Convergent Group in the US we certainly toted many solutions as being Enterprise GIS (and some were pretty cool) but today I am taking a different slant on the notion. To begin with I don’t believe that there is such a thing as “an Enterprise GIS” – that is, there is no single application or architectural component that provides all the GIS capabilities that a large organization could need.

In my experience the term is most commonly used for large spatial data repositories; hubs, warehouses and clearinghouses are often labelled as Enterprise GIS. These central stores of geospatial data can vary enormously in their sophistication, ranging from simple file shares that act as dumping grounds for file based formats, to spatially enabled databases fed by automated feeds from business applications. Repositories are often accompanied by metadata catalogues to assist potential users find and evaluate data – but in the end these are still read-only systems and only provide a subset of capabilities.

To me, the definition of “Enterprise GIS” is a set of capabilities (or services) that are baked into the software infrastructure (the ‘stack’) in order to be made available to any application that needs to use them. What are those capabilities? Well basically they provide the ability to work with spatial data i.e. to capture, transform, transfer, store, analyse, visualise, maintain, validate, manage, secure and distribute spatial data. ‘Baking them into the stack’ means that they are made available to any business application without any dependency on proprietary ‘fat’ desktop client software.

Thus it is not possible to purchase an Enterprise GIS; these capabilities need to be architected and assembled using a blend of platform software, supplemented by specific technology components from the specialist GIS houses. This involves careful selection of appropriate standards and policies to support data interoperability between applications and to enable reliable ‘Create Read Update Delete’ access to spatial data via any channel including desktop, web, sensor, mobile, EAI, etc.

Enterprise-wide GIS capabilities need to exhibit specific ‘enterprise class qualities’ i.e. they must be deployed in a server environment and possess the following qualities:

Scalable – they can grow to support volume and performance demands for current and future (unforeseen) needs;
Reliable – can operate in an unattended fashion to produce consistent and predictable results;
Available – can be configured to support High Availability e.g. support clustering and fail over;
Flexible – configuration can be changed to support evolving needs of the business with relative ease, i.e. without significant downtime or impact on business operations;
Secure – all access to data functions must be securable in line with corporate policies; includes support for directory services, delegation and encryption;
Measurable – it must be possible to monitor all aspects of the operation of these capabilities such that administrators can easily determine that they are functioning correctly and to support problem identification and resolution using standard tools (e.g. HP OpenView);
Standards-based – In order to support use by many applications, these capabilities need to be accessible via open standard protocols and formats;
Discoverable – in future posts I’ll explore this idea more, but the crux is that as we build a set of commonly available geospatial services, they will need to be described, and those descriptions made in an accessible fashion.

This doesn’t sound like GIS today, and in 20 years time may be redundant anyway as the capabilities are increasingly adopted by the major platform vendors. But at the present time I believe that providing spatial capabilities within the common platform is still a highly desirable and achievable vision.

1Spatial and Snowflake Conferences

2008-05-08T22:21:00.003+01:00

In the past couple of weeks I managed to squeeze in the Snowflake GeoxChange conference in Edinburgh and the 1Spatial conference at Stansted. Both conferences turned out to be very good so chairman Peter Woodsford must be doing something right.

Interestingly the INSPIRE initiative featured heavily in both of them, the reason being that this European Spatial Data Infrastructure is an ideal driver for both companies. As public bodies across Europe will be mandated to catalogue and share vast amounts of spatial information in the near future, there will likely be the need to quantify data qualities so enable potential users to assess fitness for purpose (and at the very least not sue the provider); 1Spatial are positioning themselves nicely to provide the ability to do just that.

The company is actively involved in an OGC initiative to define data quality assurance measures for spatial metadata to enable data quality attributes to be reported consistently and its Radius Studio tool provides a means to profile spatial data to divine business rules, report conformance with them, and perform actions to improve data consistency. This looks like a nice niche that will enable data custodians to provide clearer descriptions of their data and assist potential users determine the extent to which it might meet their needs. I think that as practitioners get their head around this concept of spatial data quality auditing there is considerable scope for profiling and improving all those datasets that have come into digital existence in the last 15 years.

Snowflake's tools also look well set to support INSPIRE driven initiatives by enabling the encoding and decoding of spatial data packets in GML format. Given this government's preference for XML centric data exchange standards and even the emergence of Dutch legislation that goes as far as to enshrine GML into law (it's true but unfortunately for some strange reason the GeoXchange presentation that describes this is password protected); Snowflake's tools may have a bright future if they can plug into message backbones to enable the transport of spatial data within the new SDIs.

Enterprise Architecture Practitioners Conference

2008-04-29T00:03:00.004+01:00

I attended The Open Group's conference is Glasgow last week and was rather surprised to find that geospatial issues didn't really feature. Given how common geospatial is (i.e. it is of interest to many departments within many organizations); how useful the geospatial dimension is to data integration analysis; and how difficult it still is to provide access to geospatial data and functionality - I had expected it to be a well worn theme but that was not the case last week.

I think this is an exciting time as the discipline of Enterprise Architecture is maturing at the same time as GIS technologies are reaching a point where they can contribute core capabilities to the software infrastructure.

I did provide a brief overview of what geospatial is and how it might fit as a set of enterprise capabilities and the ideas were pretty well received. So on the strength of that I've set up this blog to log my thoughts and ideas on the subject - and just hope that I have some!