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?
more ▼

asked Jan 20, 2011 at 03:54 AM in Default

fluff_mechanic gravatar image

21 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Jan 20, 2011 at 04:18 AM

WilliamD gravatar image

25.9k 17 19 41

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
Jan 20, 2011 at 10:05 AM Oleg
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.
Jan 22, 2011 at 01:18 PM fluff_mechanic

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.
Jan 23, 2011 at 11:26 AM WilliamD

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.
Jan 24, 2011 at 04:47 AM fluff_mechanic

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.
Jan 24, 2011 at 05:02 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

Open/read/scan/recover autocad file due to [http://www.autocad.fixtoolbox.com][1]

[1]: http://www.autocad.fixtoolbox.com
more ▼

answered Jun 23, 2012 at 11:57 AM

ryanmendez gravatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 20, 2011 at 03:54 AM

Seen: 1790 times

Last Updated: Jun 23, 2012 at 11:57 AM