Adding to my experience with Oracle from the last post. Turns out that the ‘Well Known Text’ (WKT) representation of MultiPoint geometries differs between Oracle and MS SQL Server,MySQL. Consider the ‘STMPointFromText‘ method in MS SQL Server, the representation of the MultiPoint geometry it expects is like the example shown below.
MULTIPOINT(-122.360 47.656, -122.343 47.656)
As you can see, the Latitude & Longitudes are separated by a space and the coordinate pairs are separated by a comma and that’s it. Here is how Oracle expects the WKT for a MultiPoint geometry to be represented.
MULTIPOINT((-122.360 47.656), (-122.343 47.656))
Oracle expects the coordinate pairs to also be enclosed with parenthesis. Wikipedia seems to agree with the Oracle representation also. Apparently, the initial OGC specifications were not clear and the community started using the first representation for MultiPoint geometries. Some of the .NET GIS libraries I work with use the first version of the MultiPoint geometry WKT. But OGC has clarified the specifications and accordingly, the second version used by Oracle is the correct one. MS SQL Server and MySQL use the first version of the MultiPoint geometry WKT. This has turned out to be a pain for us and probably for other gis developers out there also.
I had written this tool 3-4 months ago and it had been sitting on my machine waiting to be wrapped with an installer project. I finally got around to building a setup and deployment project for it and it is ready to distribute. The tool can be downloaded below
Once installed, you should be able to open the “Tools -> Customize” dialog in ArcCatalog
Under the “GeoSpatial.NET” Categories, the “ArcToSQL2008” command should be available. Drag the command to any of the toolbars.
Select the FeatureClass you want to export to the SQL Server 2008 database and click the “ArcToSQL2008” button on the toolbar. The dialog below should pop-up. Enter the name of the SQL Server 2008 database server and click connect. The Database drop-down will get populated with all available databases on the server. Select a database from the list and enter the name of the table that you want the FeatureClass exported to. Click OK. The FeatureClass will get exported and a message box should pop-up when the import is completed.
Some highlights of the tool:
Exports FeatureClasses in a Projected Co-ordinate System as SQLGeometry in SQL 2008.
Exports FeatureClasses in a Geographic Co-ordinate System as SQLGeography in SQL 2008.
Any errors that occur during the export are logged in a log file found under in the install directory.
The tool is built using a broader GIS framework that should enable developers to customize the export process to their liking. I still have a little bit of house cleaning on the code for the broader GIS framework. I will post when it is all done. Hopefully that will be real soon. Contact me if you want to look at it sooner…
Things left to do:
Better UI showing the progress of the export operation.
More flexibility to the export operation by allowing the user to select the fields to be exported.
The export operation can be made faster by using the geometry builder classes to construct the SQLGeometry and SQLGeography objects. I took the shortcut for now and just parsed out the geometries from WKT strings.
Please let me know if you find any bugs in the tool using the “contact me” link on the right pane of this blog.