question

The 30 pages white paper Paul Randal mentions in the blog has been published by Microsoft in October 2008. Here is the link to the [FILESTREAM Storage in SQL Server 2008][1] article. [1]: http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx

Thanks for your suggestion regarding the use of FileStream, I am deep into the linked article and have found it very encouraging. Most of the image data will be static and rarely need updating, except during its early formative stage, so Filestream appears to fit the bill. Where I remain unclear is in the use of new data types and associated functionality. For example, I have noted that geospatial data is catered for in SQL Server R2 by two new data types (for geometry data and geographical data) along with R2's new rendering capabilty. Am I right in thinking that I should use these new features in conjunction with FileStream (e.g. Maps in NTFS file system, GIS polygons in SQL Server)? I'm equally unsure about CAD data for the same reasons, although (clemently) R2 doesn't appear to have anything new to offer here. Finally, I'd appreciate some reassurance that my use of both role-based and object-based security with the image data will still be possible.

it really depends upon what you want to achieve with the GIS data. If you are using SQLL Server as "dumb-storage" then you won't win anything by storing them in the new data type columns. If you want to do anything special with the GIS data then the new data types can be of interest - finding and displaying two points on a map and calculate distances, find locations in a radius of X miles from a starting point etc. You don't have to couple the two things unless you want to. The security aspect is possible to cover - but better done via a new question.

Thanks again William. The application will focus on housing construction projects with all land elements covered by GIS data and all building elements covered by CAD data. The two elements overlap to an extent in the case of a building plot (GIS polygon) and the building solum/footprint (CAD data). Calculations will be performed on the GIS data, for distances between two points and size of land areas. Similarly, calculations will be performed on CAD data, but also between GIS elements and CAD elements (e.g. [1] ratio of land on a building plot used by the building solum, and [2] distance between the building edges and nearest building plot boundary). I have noted that your professional interest lies in pipelines, underground elements (e.g. pipes and cabling) will also play a part in the application so I'd be interested in how you structure your data.

I'm afraid that that information is dealt with by a separate division of my company - I have not had chance to play with that yet! I don't think they are using the new data types though - they were being dragged into the modern world of 2k5 the last I heard. By the sounds of it, you could store the CAD files in FileStream just as a storage layer. You would then build up the meta data for the CAD files to coincide with the GIS data (both using the new data types). You would then have the native geometry/geography data types and their methods within SQL Server to do your analysis. As stated, the new data types are only of interest if you actually want to do the calculations in SQL server. If you are doing that work externally and only storing the information, then you can save a bit of hassle by storing the information in one of the older native formats.