Skip to main content

Visualize geospatial data from SQL SERVER 2008 R2

With the development of digital maps such as OpenStreetMaps and Google Earth or Google Maps and other GIS systems many of the softwares need to processing geospatial data which are stores in databases. In the new version of the SQL Server 2008 R2 some new features to working with geographic data were introduced. First of all developers are able to work with two new datatypes which where implemented as a .NET common language runtime (CLR) data type:
  • geography 
  • geometry
These types support methods and properties that allow for the creation, comparison, analysis, and retrieval of spatial data. The difference between then is the geometry data type (called planar) supported by SQL Server conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification while the geography data type (geodetic) stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

After short theoretical introduction it`s time for implementation. First of all we need  to create in our test database. Let's our table stores information about provinces in Poland and its SQL code be like this:

CREATE TABLE [dbo].[Provinces](
[ProvinceID] [int] IDENTITY(1,1) NOT NULL,
[CountryID] [int] NOT NULL,
[ProvinceName] [nvarchar](255) NOT NULL,
[ProvinceNameLocal] [nvarchar](255) NULL,
[geom] [geography] NOT NULL,
[Population] [int] NULL, 
CONSTRAINT [PK_Province] PRIMARY KEY CLUSTERED ([ProvinceID] ASC)) ON [PRIMARY]

As can see the Provinces table stores information about Primary Key, CountryID (Foreign Key), province full name as well as local name, population and province border which is the most important in this context. Initially, the table is of course empty but can be populated with data by using several inserts available here (may looks complicated).
After data were inserted now we can preview them by using simple query in Sql Server Managment Studio by typing SELECT ProvinceName,Geom FROM Provinces and than switch to Spatial results tab (figure 1.). 
Figure 1.
Geospatial data were visualized but only in external tools and we want to be able to embed such map in our own solution. We don`t have to write rendering engine by our own but we can use already done one. Now it`s time to open the SQL Server Business Intelligence Development Studio (it`s located in the Sql Server 2008 R2 folder in Start) and create new project of type Report Server Project. After type any name for new solution next step is to establish connection with database to create DataSource. In new window type custom name for new instance of the dataset and choose datasource You added before.
  • Right click on the Report folder and from context menu DON`T select Add New Report. This time choose  Add New Item and than select Report file. This because the designer don`t supports spatial data.
  • On the new report designer, open Toolbox and drag MAP control on the report and after new wizard displays choose SQL Server spatial query radio.
  • Now select second option "Add a new dataset with SQL Server spatial data" 
  • By clicking Edit you can create a custon connection string to the database in which our table witch geospatial data is located (figure 2.)
Figure 2. New DataSource
  • In the command window type the same SQL query which we use last time (SELECT [ProvinceID]     ,[CountryID],[ProvinceName],[ProvinceNameLocal],[geom],[Population]  FROM [dbo].Provinces) (figure 3.)

    Figure 3. New DataSet
  • In the next window you can see visualized spatial data (figure 4) . In this step optionally You can embed You map in Bing Maps context as well as control edge accuracy by changing quality (in performance case use lower resolution) 
Figure 4. Visualized data in wizard
  • Next step allow You to control how Your maps will be looks like. You have three option to choose but in this article we use second option 'Color Analytical Map' - this option required to select dataset one more again in next step.
  • Last step allow developer to choose which field will be user for coloring maps fields. In this example on the map sum of the population for each province will be evaluated (by choosing from dropdown list 'Field to visualize') (figure 5.).
Now when You Run Your project You can see You visualized spatial data in the report. This report can be deployed onto report server and be accessed by Internet or embedded in Web application.
Figure 5. Setting visualize options
Read more...

Labels:

Popular posts from this blog

Using Hortonworks Hive in .NET

A few months ago I decided to learn a big data. This sounds very complex and of course it is. All these strange names which actually tells nothing to person who is new in these area combined with different way of looking at data storage makes entire topic even more complex. However after reading N blogs and watching many, many tutorials today I finally had a chance to try to write some code. As in last week I managed to setup a Hortonworks distribution of Hadoop today I decided to connect to it from my .NET based application and this is what I will describe in this post. First things first I didn`t setup entire Hortonworks ecosystem from scratch - I`d love to but for now it`s far beyond my knowledge thus I decided to use a sandbox environment provided by Hortonworks. There are multiple different VMs available to download but in my case I`ve choose a Hyper-V. More about setting this environment up you can read here . Picture 1. Up and running sandbox environment. Now whe...
more -->

Creating common partial class with Entity Framework

When we use the Entity Framework (EF) in multilayer information systems sometimes we want to extend classes generated by EF by adding some common properties or functions. Such operation can`t be conduct on *.edmx data model so we need to make some improvement in our solution. Let`s begin... Lets assumed that in our soulution we have only three layer (three project): Client console application which has reference to the second layer  - ' ConsoleApplication ' project name Class library project with class interfaces only - ' Interfaces ' project name Class library class implementation and data model referenced to 'Interfaces' project - ' Classes ' project name. Picture 1. Solution structure. Now when we have all solution structure we can focus on data model. In the ' Classes ' project we create a new folder named ' Model ' and inside add new item of ADO.NET Entity Data Model named ' Learning.edmx ' - it may be empty ...
more -->

Full-Text Search with PDF in Microsoft SQL Server

Last week I get interesting task to develop. The task was to search input text in PDF file stored in database as FileStream. The task implementation took me some time so I decided to share it with other developers. Here we are going to use SQL Server 2008 R2 (x64 Developers Edition), external driver from Adobe, Full-Text Search technology and FileStream technology.Because this sems a little bit comlicated let`s make this topic clear and do it step by step. 1) Enable FileStream - this part is pretty easy, just check wheter You already have enabled filestream on Your SQL Server instance - if no simply enable it as in the picture below. Picture 1. Enable filestream in SQL Server instance. 2) Create SQL table to store files  - mainly ther will be PDF file stored but some others is also be allright. Out table DocumentFile will be created in dbo schema and contain one column primary key with default value as sequential GUID. Important this is out table contains FileSt...
more -->