Home    >    Case Studies    >    GIS with Spatial Data of SQL Server 2008 to Manage Farms

Case Study: GIS with Spatial Data of SQL Server 2008 to Manage Farms

Abstract

The client has been dedicated to Farm Informationization Construction with an information system to manage farms. We are required to customize one crucial functional module for this system based on GIS, which will be used to provide more data for end-users' statistical analysis and manage crops in a more direct way.

Requirements

Whilst statistically analyzing each index for crops, area (acres) was essential as basic data. However, current system didn't directly include such data in both field device data and server communication data. Our job was to develop an API to calculate these areas and graphically present corresponding shapes of lands.

The data in field device and server communication contained the field management commands and geographical information in a system custom format. We needed to access geographic information from such data. That was to say, we need generate shapefiles. Then we calculated the area of lands covered in the shapefiles. The end-users would have a more direct visual representation to manage farms.

Solution

Since all the data in the system was of custom format. Therefore, first we need convert the customized data to shapefiles. Why we use shapefile? Shapefile is an open format for special data developed by ESRI. Meanwhile, Shapefile is an important exchange format and can exchange data between the current system and a 3rd party product. Besides, all map controls support shapefile. In this way, we could easily present geographic information like land areas, numbers of crops and others on map control.

How to calculate the land areas in shapefile? It was the most difficult question we met. In the beginning, we tried to do the calculation in a simple way---resolved shapefile to a set of polygons and then calculate areas of polygons. Unfortunately, we found there was a huge difference between actual area and calculated area. After deeper researches, we found the main reason was longitude and latitude in shapefile was different from polygons' coordinate position. If we kept calculating polygons' areas, we had to map the longitude and latitude system to the plane coordinate system, which would be both time and effort consuming.

Luckily, we have many GIS project development experiences. We were aware that we could take advantage of spatial data of SQL Server 2008 like:

  • Use the new geography data type to store geodetic spatial data and perform operations on it;
  • Use the new geometry data type to store planar spatial data and perform operations on it;
  • Take advantage of new spatial indexes for high performance queries;
  • Use the new spatial results tab to quickly and easily view spatial query results directly from within Management Studio;
  • Extend spatial data capabilities by building or integrating location-enabled applications through support for spatial standards and specifications.

Because of above advantages of spatial data, we successfully accomplished all the features of API. In addition, in the process of development, we noted the dots of polygons in shapefile were counterclockwise while the dots in SQL Server 2008 were clockwise. Moreover, we also experienced the quickness and efficiency to do area calculation with SQL Server 2008 spatial feature.

Benefits to client

As fewer people get involved in SQL Server 2008 spatial data, there are limited materials for us to refer. Hence, it was a challenge for us to finish the project in a tight schedule. However, thanks to former accumulated experience and a seamless cooperation with the client's team, we finally accomplish the API with a high efficiency and good quality. The client is satisfied with our delivery and our performance.