<< BattleShip - Now for SqlServer 2008 | The community works >>

Shapefile to SqlServer 2008 to WPF

by Morten 12/23/2007 3:55:00 PM

I've been playing with the spatial support of SQL Server 2008 today, but quickly hit two obstacles:

How do I get data into the database.
How do I visualize a query (apart from the Well-Known Text output).

Surprisingly there aren't really any good (free) tools out there yet to upload for instance shapefiles to Katmai. I pulled out some old code I had lying around and threw a small tool together that can upload a shapefile to the database either as geometry or geography. Katmai is pretty picky when it comes to the geography datatype, so make sure your shapefiles contain valid geometry and has vertices in the valid domain, or use the geometry format. You can download the tool below.

Next obstacle was visualizing the data. Playing with the spatial queries and not beeing able to see the results on a map quickly got boring. I haven't had a chance to play much with WPF, so I thought this was a good occation to play with it. Based on this MSDN article I managed to fairly quickly make a little tool that takes a SQL query as input and outputs the results in a WPF control. At the moment it only works with polylines and polygons - Points are simply ignored (tip: use STBuffer() on points to see them). It will look for the first column that contains a SqlGeometry or SqlGeography datatype and put that on the map. The remaining columns are shown in the box to the right when you hover over the shapes with the mouse.

The two tools are not perfect in any way (and there's no bug tracking support here :-)), but they provide a quick way to play with the spatial queries. Both tools requires Windows Authentification to access the sql server and runs on .NET 3.5. Furthermore the query tool uses the Microsoft.SqlServer.Types.dll that comes with installing Katmai (to parse the results), so for now you'll need to run it from a computer that has Katmai installed.

Read more and download SqlSpatialTools

8cda10ac-aa93-4b56-9497-904dc3c38223|7|5.0

Tags: sql server

GIS | SqlServer Spatial

Silverlight and WPF code reuse pitfalls. The idea is really great: Write your code once, and reuse it across all your platforms. This is on...BattleShip - Now for SqlServer 2008 Now here's an interesting use of the spatial support in Microsofts SQL Server 2008: Battleship...SQL Server 2008 Spatial Tools The SQL Spatial Tools consists of two tools to make it easy to get experience with the new ...

Comments

12/24/2007 12:10:47 AM

nice job Morten.

Does it have a command line component? That would be good for batch loading of shapefiles.

Scott

Scott

12/24/2007 4:35:54 AM

Nope. It was just made for enabling me to do some quick playing around with SqlServer. It's not a production tool in any way. It's a good idea though.

Morten

12/24/2007 5:19:32 AM

Morten,

Great work! I had run into the same issues myself and was working on a similar tool. Given the buzz about SQL 2008, I don't think it'll be long before these gaps are filled in.

Bill

Bill

1/15/2008 8:24:39 AM

Morten this is a great tool it has really helped with testing spatial queries, any chance you will be expanding this so that you can add data from feature classes, or would you be willing to make the source code availble so that we can try and expand on what you have done.

Great app

Brian

Brian

2/1/2008 3:58:31 PM

No I won't be adding much to the tool (features go in if I see a need for them), and for now it's all closed source. This is not a production tool, but a simple set of tools to test various spatial queries and get experience with the Spatial support.

Morten

2/6/2008 9:56:15 AM

Pingback from soci.hu

Soci blog » Blog Archive » SQL Server 2008 ÃºjdonsÃ¡gok 18. - tÃ©rbeli adattÃpusok 5.

soci.hu

2/7/2008 2:57:44 AM

Morten, I get an error when trying to load a shape file. It's been converted from a MapInfo TAB file. The file has 2 columns - ID and GEOM. When I select it to load using the Shape to Katmai tool I only see the ID column in the Attributes window. If I select the ID check box and upload I get the error "Name conflict on ID column and Shape data column". If I uncheck the ID check box and click upload it loads the GEOM column but leaves the ID column NULL.

I'm a DBA and shape file novice, as you may be able to tell.
Any help is appreciated

Ed Szumowski

2/7/2008 6:09:15 AM

I figured it out - the shape file had a column called 'ID' and the ID Column Name in the attributes window was also 'ID'.
Sorry to waste any time.

Ed Szumowski

2/7/2008 6:21:18 AM

Yeah, either uncheck the ID attribute column or rename the ID column that gets added (or give it an empty name which will skip creating the column).

Morten

2/9/2008 7:03:57 AM

I have the Nov. SQL 2008 CTP installed and just installed .Net SDK 3.5. However, after restarting I still get the generic error:
Shape2Katmai has encountered a problem and needs to close.

There's not much to say what may be wrong. Any clue on how to fix this?

Thanks,
Matt

Matt Penner

2/18/2008 3:02:55 AM

Please, Could you send me the source code ?

Andre Tude

2/23/2008 6:07:06 AM

Have a project that I need to programmtically convert SHP/KML to SQL 2008. This tool is the closest thing to that yet.

If anyone knows of a tool that can hlep lemme know (since he is not releasing the source)

matt

2/28/2008 7:59:44 AM

I would be interested in your source code for the shape to katami tool if you are willing to let us have it...

Dan Smith

2/29/2008 2:38:08 AM

Strage thing .

I used the latest version to move data to SQL 2008 and using MDNS only about half the shape files appear.. I check it in your tool and all the shapes are there.

So I used a old version of SHP2SQL and now it works perfect again.

So you changed something recently which is messing it up for use with MDNS.. any idea?

lucky I had that old version around or else I wouldve really been confused. I guess ill try using SqlCompare see if I can figure out what happened

matt

3/23/2008 12:42:43 AM