Archive for the ‘sql server 2008’ Category
Does SQL Azure have Allure?
I love SQL Server 2008 spatial functionality. Not only because it has effectively killed off the middle-man GIS database software, but it even sticks to OGC standards allowing the same data and tables to be used in nearly all GIS software. However I have some reservations about the use of SQL Azure for GIS applications.
FAQ
How is SQL Azure Database different from working with a hoster using SQL Server?
Developers do not have to install, setup, patch or manage any software. High Availability and fault tolerance is built-in and no physical administration of hardware, storage or servers is required.
If you have a good hosting solution then the same benefits apply (well maybe not the “self-healing” also mentioned). The only real difference I can tell is that you pay a lot more, to be able to scale more easily. Its unlikely your Local Planning Mapping system is suddenly going to need to scale up to meet the expectations of millions of new users, no matter how great the system looks.
MapInfo and SQL Server 2008
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.
Copying Spatial Data between SQL Server Databases
Aim - to copy a table containing geometry fields between two database servers.
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'
MapServer and SQL Server 2008 Plug-in
The SQL Server 2008 plugin for MapServer is a “native driver modeled on the PostGIS driver to
support 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.
SQL Server Spatial SQL
I’ve been working more and more with the SQL Server 2008 spatial queries. For standard queries I now rarely use the
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.
Read the rest of this entry »
3 MapServer & SQL Server 2008 Performance Tips
A current project I’ve been developing uses a combination of SQL Server 2008 and MapServer (for Windows) to
serve many WMS layers. As much as it irks developers, web users only care about two things – how nice the site looks, and how fast it is. Whether the correct information is displayed comes a poor third..
I’ve found a number of tweaks that have dramatically improved the WMS creation speed. In these examples I’m using a table in SQL Server 2008 with 50773 features (Irish Townlands if you’re asking).