question

fluff_mechanic avatar image
fluff_mechanic asked

GIS and CAD data storage and image rendering

I am developing an application within the construction industry that will involve a significant amount of image processing. Most of these images will be in the form of GIS maps and CAD drawings (2D and 3D), but photographs and videos will also be included. Key to the success of the application will be the ability to drill down through the layers of various images and jump in and out of related details. My question is simply this: how can MS SQL Server R2 help?
database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
You can use FileStream, which will perform quite well when serving up files that are of a large enough size (above 1MB shows faster speeds than a fileserver IIRC) Take a look at [High-Performance FileStream Tips and Tricks][1] for some pointers better usage of FileStream. You should be able to build a hierarchy of pictures in SQL Server using either the Hierarchy data type, or by standard hierarchical constructs in T-SQL. That would aid you in the drill down / related pictures part of the implementation. [1]: http://www.sqlskills.com/blogs/paul/post/High-performance-FILESTREAM-tips-and-tricks.aspx
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
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
1 Like 1 ·
fluff_mechanic avatar image fluff_mechanic commented ·
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.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·
fluff_mechanic avatar image fluff_mechanic commented ·
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.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·
Show more comments
ryanmendez avatar image
ryanmendez answered
Open/read/scan/recover autocad file due to [ http://www.autocad.fixtoolbox.com][1] [1]: http://www.autocad.fixtoolbox.com
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.