Nick’s Blog

GIS

Inserting a geometry into a SQL Server 2008 Database

by Nick on Dec.09, 2010, under GIS

I’m not sure why but it seems that there is a lack of documentation surrounding how you get spatial data into and out of a SQL server 2008 database. Some people resort to using WKT (well known text) others serialise to a binary format. I was not happy with either approach, I mean inserting data into a database should be relatively straightforward.

So before we go anything further. Here is the code I created to create a SQL Server 2008 point geometry and insert it into a table with two columns. The first column was an auto increment ID and is not shown, the second is called LOCATION and is defined as a Geometry as far as SQL Server is concerned.

private void Add(Point point)
{
   SqlGeometryBuilder builder = new SqlGeometryBuilder();
   builder.SetSrid(900913);
   builder.BeginGeometry(OpenGisGeometryType.Point);
   builder.BeginFigure(point.X, point.Y);
   builder.EndFigure();
   builder.EndGeometry();
   SqlGeometry geom = builder.ConstructedGeometry;
   SqlCommand cmd = _dbConnection.CreateCommand();
   cmd.CommandText = "INSERT INTO READING (LOCATION) VALUES (@geometry)";
   SqlParameter param = cmd.Parameters.AddWithValue("geometry", geom);
   param.UdtTypeName = "Geometry";
   cmd.ExecuteNonQuery();
}

The Point type that is passed in is my own and simply contains an X and a Y. The types SqlGeometryBuilder and SqlGeometry are defined in Microsoft.SqlServer.Types which requires that you install Microsoft SQL Server System CLR Types which you can find via http://www.microsoft.com/downloads/en/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en. Something that fooled me was that after installing this I expected the reference to be in the GAC. Well it isn’t, or at least it isn’t on my machine and instead I had to browse to C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll. 

The lines to note are

   SqlParameter param = cmd.Parameters.AddWithValue(“geometry”, geom);
   param.UdtTypeName = “Geometry”;

The first adds the Geometry object as a parameter and the second tells SQL Server what kind of UDT (or user defined type) we are using. Strange that it was not possible to devise a way to make Geometry and Geography fully fledged types but the extra work is very little.

Leave a Comment :, , , more...

Tatuk GIS

by Nick on Sep.25, 2010, under GIS

I’ve been using Tatuks DK.Net version 9 for about a year now. The choice to use Tatuk was not mine but rather it appears to have been financially driven by predecessors and I have to say price performance is very good. You pay only for a license for each developer and are then free to distribute the applications to as many users or customers as you want.

The DK includes a control for the display of maps within the application that is capable of displaying data from a wide range of spatial data sources (http://www.tatukgis.com/Products/InternetServer/Features.aspx) including all the major ones such as ESRI, Oracle Spatial, WKB, WKT, and most common raster formats. The control also supports printing templates allowing the map to be printed with a border and text adornments.

Along side the visible mapping capabilities, the DK also includes an amount of spatial processing capabilities. You can filter spatially or create buffer zones and much more. It all adds up to making creating a map with Tatuk quite easy.

There are, however, some downsides. While the DK is very fast with shapefiles this appears only to be because it loads vast amounts (or possibly all) the data into memory. This really does give us a very fast map but as we add data from other sources the memory usage builds up and the map slows down. Performance with Oracle Spatial is currently proving a headache for us as clients are insisting on adding every layer available to them and making map rendering times to say the least sluggish.

To add to this problem we find that the DK cannot share layer information between map instances. We wanted to build an application with maps in several places. The main screen contains a map and the fact that startup time is not quick is not really a problem, however we would have liked to have had auxiliary maps on various other forms in the application. However those instances cannot be made to share information with the main map and so have the same memory usage and startup time.

Another weakness of the DK appears to be that it only operates on the GUI thread. I did some tests early on with running the DK on another thread and I was able to get it to do some work but not by doing anything that was Tatuk approved. The reason I wanted DK to run on another thread was simply that every time the map renders the entire GUI stalls. I had hoped that the slower layers could be rendered on a separate thread while the faster raster layers gave context to the user.

In all I think Tatuk is a great product and for the price you’ll find it hard to do better. The guys at Tatuk are supremely helpful and willing to look into most problems. However you can try to push it too far and perhaps our product is doing that.

Leave a Comment :, more...

Point order in spatial data (or it depends how you look at it)

by Nick on Apr.27, 2010, under GIS

I was battling with a new GIS application and started getting complaints from some people that the points were being ordered wrongly. Now I have always known that the point order in some spatial systems is important. Mostly I rely on the software between me and the storage to correct the order before saving and this time was no different. I am using the products of a relatively small Polish outfit called TatukGIS. They have made quite a nice product and are helpful when things go wrong. Their product is a viewer and middleware that allows many different spatial formats to be read or written to. You can read more on their site http://www.tatukgis.com/

Now as a use of an application I can imagine that as you draw a polygon, that represents the boundary of your garden for example, you are not concerned in which order you draw the points (clockwise or anticlockwise) but in theory it makes a difference. I’ll try and describe the reason as best as I can. There are two ways to explain it, the Oracle way that I learned a while ago and the WKT (well known text) way that I’ve only just read. To be honest both make sense but the WKT way perhaps gives a little more information than the Oracle way.

Firstly Oracle simply says that a polygon exterior is a list of points that must be filled counter clockwise. (See 2.2.4 of the spatial data types and meta data documentation.) Now I was always told that the explanation was you need to imagine yourself walking around the exterior, on your left is the interior. So, in theory, if you specify the points on the other order then everything outside your garden would become garden and only the bit inside would be not garden (nice mistake if you can get the land registry to make it). But Oracle also says that any holes in your garden must be specified clockwise. Well if you think about this for a moment (taking the classical doughnut as an example) it makes perfect sense. You walk around the outside of the doughnut anticlockwise and everything on your left is doughnut, walk around the hole clockwise and again everything on your left is doughnut.

Secondly the WKT way. WKT is a way of formatting geometries in a standard fashion that is supported by many databases and can easily be used within databases that do not natively support spatial information because the format is purely text (there is also a WKB version, a binary format, that is more efficient with the storage but is not human readable). Their documentation (section 6.1.11.1) states “The exterior boundary LinearRing defines the “top” of the surface which is the side of the surface from which the exterior boundary appears to traverse the boundary in a counter clockwise direction.”. Now if you consider this for a moment it sounds like exactly the same thing and really it is. But suppose once again that you look at your garden, draw out the points in a clockwise fashion and then hand the map to someone that does not know your garden. They might conclude that either a) you have made a mistake b) your garden is best viewed from the underside or c) your garden occupies the entire map with the exception of the small bit in the middle.

I would suggest that most people will conclude alternative a) that you have made a mistake. And it seems that this is the alternative that a large number of software packages makes (including Tatuk and GeoMedia). However they are guessing at your mistake and perhaps you really do have a massive garden. Move to three dimensional data and the difference becomes less clear. In three dimensions how can you tell  which side is the top of something, and what does top really mean? Suppose you have a drawing of cube that represents a water tank. What order would you draw the points of the bottom panel of the tank? Does the top of that panel point downwards or upwards? And what about the sides?

If you are wondering about the idea that your garden could occupy the entire globe then imagine a spherical world (a bit like earth perhaps) and draw an island round one of the poles. Now imagine how this island might look if you were to try to draw this map on a flat piece of paper. It would probably look simply like a straight line running along the top of the paper. Now how do you decide which side of the line is land and which is water? Now imagine and island that covers half of the globe. It does not matter which half, you will always need to somehow decide which is inside and which is outside.

I said earlier that I would revisit this and add some information about other file formats. The next file format up is the shapefile from ESRI. According to the ESRI definition of the shapefile (see page 9) the order of the points is opposite to that of the previous two formats that I listed. This does, of course, pose a risk for those developing tools to read from one format and write to another, and that is that they have to reverse the order with each read/write. Not that this is any reflection on ESRI, actually the shapefile format pre-dates  both of the previous two formats.

Microsoft have been fairly slow to recognise the necessity of spatial capabilities in their database but 2008 has finally seen the introduction of two new data types. Geography for storing geodetic (real world) data and geometry for storing planar data. Hopefully their time spent catching up also means they have overtaken some of the now elderly formats and engines.  For the geometry type Microsoft states that the point order does not matter. They go on here to say :-

The OGC Simple Features for SQL Specification does not dictate a ring ordering, and SQL Server does not enforce ring ordering

Which seems at odds with the earlier reference that I gave earlier stating that it does matter. However that was a draft specification so perhaps they later dropped the requirement. I can quite see how a polygon on a planar system is unambiguous but WKT is not solely used for planar systems.  Clearly a little more homework is required.

For the MS SQL Server Geography type the point order is important but the documentation I read did not say what the ordering should be. What they did say is that no geometry using a geography data type may be larger than a hemisphere and that there is effectively no such thing as an inner or and outer ring. This last point might sound like non-sense but actually it makes perfect sense.

Imagine an island that has two lakes. Now make the island so big that it covers nearly the entire planet. Does it really make sense to call one of the exteriors the outer? In other words anything that is not dry land is outside the island. Let us imagine that one of the lakes has an island of its own. And suppose the island has a school. Now you perform a query to find which schools lie on the original outer island, is the school on the island in the lake of the island included. No! Not if you are using the geometry of the main island.

But I can imagine that there are circumstances where this theoretical lack of distinction would get in the way. Suppose I want to know where all the schools are within the territory of the main island. In most other systems I’d load up my polygon, delete any interior holes and then query the database. Only under SQL Server  that will not be possible because I have no idea which is my outer, they are all outers. I might be able to iterate over the outers and find out which is the largest and delete the rest.

I am going to have to come back to this topic to make it complete for other databases and file formats.

Leave a Comment :, , , , , , , , more...

PostGIS on Ubuntu

by Nick on Jun.12, 2009, under GIS

I’d been called by an old client and they were after someone with OpenLayers experience. Now I do have some experience with OpenLayers but I decided that brushing up on the skills would do no harm. I created an application that connects to a GeoServer WMS instance on my Ubuntu machine and even managed to get the GetFeatureInfo working with a little effort (because the site and the GeoServer are in different domains it was necessary to add a proxy to the site to forward requests from the client). But what I am missing is the ability to edit data. For this to work I am going to need to turn to the WFS-T capabilities of GeoServer and that means having an editable source of data. So I’ve decided to create a PostGIS database. The rest of this article is really just about that experience and it may be a little disjointed as I’m writing it as I go along. (continue reading…)

1 Comment :, , , , , more...

More progress on SilverMap

by Nick on Jun.07, 2009, under GeoServer, GIS, Silverlight

SilverMap is not really aimed at becoming a real product, it is simply to allow me to get real experience of developing a Silverlight application that makes use of my GIS skills. I had already managed to create a viewer that pans around OpenStreetMaps. I added a few markers that also support clustering for when markers are too close to each other and now I support WMS.

Now some of you not from the GIS world may be wondering what WMS is. It stands for Web Map Service and essentially allows you to form a request for a bitmap (usually) of the area you are interested in. You then display this bitmap in your browser and, voila, you have a basic map. Alternatively you can render the bitmap over (or under) other data and then things start to become more interesting because a source of data from one server can be combined with another server and the user gets the impression of a single map with all the content they want.

Something that hindered me was that I wanted to use a public WMS server so that I did not have to find a way to make one work for me on my own computer at home. There are two drawbacks here, firstly I could not find one that was fast enough and secondly even if I had found such a server I would probably not have been able to make it work with Silverlight as Microsoft has created a mechanism within Silverlight that prevents an application running from one domain from calling a server in another domain. There is a workaround that involves putting a file on the server telling the Silverlight application that it is OK to make the cross domain call but the chances of me getting some public WMS server to place this file in their server is probably nill.

The rest of this blog covers the creation of my own WMS server and the problems I encountered. (continue reading…)

3 Comments :, , , , , more...

Silverlight and cross domain security

by Nick on Jun.02, 2009, under GeoServer, Silverlight

I was trying to create a WMS viewer using Silverlight. Not that there are not plenty of ways to view WMS without Silverlight, I just fancied a challenge. For anyone not in the GIS world, WMS is a defined HTTP based protocol that allows requests for maps to be served in a standard fashion. Essentially you send a request to the WMS server and it returns your map as an image (or any other mime type that the server knows how to render). (continue reading…)

Leave a Comment more...

GeoServer

by Nick on May.29, 2009, under GeoServer

GeoServer is an open source Java map server. Their site is http://geoserver.org/. Now I want to say from the outset that in the past I’ve  had some terrible experiences with installing open source software on my Windows machine. I don’t want to apportion blame because generally it’s not possible to determine who is to blame for what. However my normal experience goes something like this.

  • Decide an open source solution sounds worth investigating.
  • Download the necessary package.
  • Download all the other necessary packages required to make it work.
  • Install all these extra items.
  • Find that for some reason it does not work on my machine and spend two weeks trying to find a web site that can help.
  • Give up and go back to commercial software.

Well here is how it went for GeoServer.

  • Downloaded the package.
  • Downloaded the required Java JDK package.
  • Installed both.
  • Ran startup script.
  • Visited local URL mentioned in instructions.
  • Fell off seat when I realised it was working.

That was it and I am really delighted. Even the performance seems quite reasonable although so far I’ve not seen anything in the data that is too demanding. Now what I have to do is to add some extra data sources and make it work. The real pity is that I do not have a publicly visible server that will allow me to show you the results.

The reason I’ve set up this server is quite simple. Although I really love the results that you can get using GeoMedia Web Map, I really do not have the money to spare to afford a license. I’ve actually discussed this matter with Intergraph and some people agree with me that what they need is some kind of developer license that protects their intellectual property while at the same time allows developers such as myself to create imaginative solutions based on their products that will require an end user to buy a license from Intergraph. Don’t get me wrong, I’m not singling out Intergraph here. To my certain knowledge neither ESRI nor Safe Software have any kind of developer license either and the result is tha free software such as GeoServer can start taking a share of the market.

Now perhaps some of you will be concluding that I am anti open source. Not in the least bit, but I do have a problem with products that are essentially unsupported. OK OK there are thousands of developers out there beavering away to fix the problems that might arise in open source software but there is nobody that I can politely ask to fix or threaten with legal action (whichever floats my boat). There is nothing about commercial software that guarantees suitability for purpose but you can be reasonably certain that the developers have someone hovering over them telling them to damn well get it fixed.

But back to GeoServer. It promises quite a lot. It promises WMS, WFS, and WCS that are compliant with OGC standards. It offers the ability to read data from a multitude of data sources and because they are delivered via open standards you are not tied to a specific front end. Granted the compliance certificate seems to have been given against a relatively old version of these standards but nevertheless I’ve already seen that it is quite able to generate usable maps that can be read by other open source viewers (and in theory closed source ones too if I were able to afford them).

My real aim is to see if I can encourage the server to deliver data via WMS and WFS to my Silverlight viewer. If I can do that then there is a good chance that I can make that same viewer work with other WMS and WFS servers. The main restriction I have at the moment is that any server I use must support EPSG:900913 which is the coordinate system used by Google Maps and the like.

Well that’s all I really want to say for now. If I can make any progress then I’ll report back here.

4 Comments more...

Interview with a potential employer

by Nick on May.29, 2009, under GIS, Job Hunting

I’ve litterally just come home from an interview with a potential employer. It was a very refreshing experience with the guy interviewing me seeming really genuinely interested in finding out about me. I mentioned that I was going to see an old client at which point he said that someone he knew had just started working there. I told him the name of the guy I was visiting and sure enough it was the same person. GIS is really quite a small world. We chatted for ages about some of the old projects I’d worked on and how much of that had any GIS involement. We even chatted about my concert photography for a while and shared our musical interests. Now whether or not I’m offered a job depends on them finding a suitable client but the whole experience was really positive.

Leave a Comment more...

GIS Projects

by Nick on May.28, 2009, under GIS

Like most technologies, it seems that there is an unlimited amount you can learn in the GIS world. I’ve started working on a Silverlight application that looks a little like a very basic Google Maps, you can see this here. It uses the Open Streets map source rather than Google’s own maps and a reason for this is because that source is far less restrictive with how I can use the tiles. Another area I’m trying to get into is the open source GeoServer. A reason for this is that it is simply a free WMS/WFS server that I can use to try to see if my Silverlight maps can read from WMS. I could try to connect to one of the non open source solutions but that potentially means spending a lot of money.

I’ve had limited success with MapServer in the past so my attempt at installing GeoServer was very encouraging. I had to install a JDK first but after that installing GeoServer on Windows really was very easy. The only hurdle I fell at was assuming it would be running under IIS and not getting the first map out until I’d realised my mistake and started the GeoServer session using the installed script.

Another very popular technology at the moment is the open source client, Open Layers. This is a JavaScript library that allows various spatial data sources to be viewed in the client. I’ve set up a page on this site here but really this is little more than a copy an paste of a sample from the Open Layers examples site. Google Maps has been available for some time and there are a couple of examples on this site. I would have included Virtual Earth but it seems that Microsoft have gone all commerical and now you need to pay for a license. Can’t say I blame them really but I don’t remember hearing about that.

While Open Layers focusses on the browser based user another technology is the UDig application that allows connections from various server technologies to be displayed. Another contender that should not be overlooked here is the Google Earth application. It really has a superb interface and also allows some other data sources to be overlayed.

On my Windows Mobile device I have installed Google Maps again. This is not quite the flexible application that is available for the desktop but it certainly is a great deal better value (being free) than other options such as the dedicated navigation systems that require you to download the maps you want and then pay again when they need upgrading.

At this point I should mention that I am not some kind of Hippy software engineer. As far as I am concerned people who do a hard day’s work should be rewarded for their work. Sadly it seems that a lot of people are happy to give their work away (thanks WordPress) and as long as this is the case I am happy to look into the results. However it does seem that when Microsoft try to give something away for free they are accused to anti competitive practices, when a hippy software engineer does the same, well that’s cool man.

Leave a Comment more...

GIS and me

by Nick on May.28, 2009, under GIS

Just thought I’d drop together a brief summary of my involvement with GIS and where I see it going. About 10 years ago (in late 1999) I started a contract with one of the big GIS players (Intergraph) just up the road in Hoofddorp. I stayed with them for more than two years and picked up a lot of skills relating to the GIS field. Back then relatively few people used GIS other than big organisations and local authorities and very few people had a navigation system in the car and Google had yet to invent their maps.

Ten years on and it is much easier to explain to people what you do by simply dropping the abreviation GIS into a sentence. It is quite normal to find that a web site uses a map on their site to allow people to navigate to their offices or event and almost everyone has a navigation system. Actually GIS is more than a navigation system and Google maps really only scratches the surface of GIS but I have the feeling that in ten years time we will have made another giant leap.

Another indication of the strides that have been made in the GIS market are the open source solutions that have cropped up. The advatange that Intergraph had in the past was that you really only had a few people to turn to if you were going to be in the GIS world. However with the addition of free software there are many more options open. I firmly believe that this has helped Intergraph (and the other big GIS palyers) because they have not only been encouraged to take their role within the Open Geospatial Consortium (OGC) very seriously. Actually Intergraph already did even ten years ago but now there are standards they can adhere to that genuinely allow interoperability such as WFS, WMS, and WCS. Further more the latest versions of their software deliver much more functionality and support much higher quality map output. Arguably all these improvements have been made to keep ahead in an increasingly competitive market.

Already maps are playing an increasingly bigger role in the lives of everyday people. A lot of this has been driven by cheap fast bandwidth. Ten years ago the average person would not have had a good time visiting Google maps (if it had existed) and the idea of using your phone as a map would have been laughable. But now many people regularly use their mobile phone to retrieve maps. There are loads of sites that offer services such as weather, traffic, navigation and loads more. There are already plans to incorporate live services within GPS systems, how brilliant will it be when you effectively have a full web browser in the dashboard and you can not only see where the local tourist attractions are but also check actual opening times and even reserve tickets. For the travelling sales rep or service engineer there are already systems that allow the central office to send them off to a new client but these are very dedicated systems. Imagine if it were no less common for your assistant back at the office to be able to push the location details of the next person to visit to your navigation system just as they might currently plan a meeting in your agenda.

Leave a Comment more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!