SQL Server 2008 Spatial Data Application series five: Using spatial data types in data tables

Original: SQL Server 2008 Spatial Data Application series five: Using spatial data types in data tables

tips, the prerequisites for reading this blog post are as follows:

1. This sample is based on Microsoft SQL Server R2 Commissioning.

2. Experience in Transact-SQL programming and use of Management Studio.

3. Be familiar with or understand the spatial data types in Microsoft SQL Server 2008.

4, with corresponding (such as OGC) of GIS professional theoretical knowledge.

5, other related knowledge.

The previous articles covered knowledge points about spatial data types in SQL Server 2008, learned what spatial data is, what spatial data types are, and how to become a spatial object instance that defines the underlying in SQL. These knowledge points are mainly for the subsequent study of spatial data applications and to do geospatial analysis to prepare. This article will continue to cover application knowledge points for spatial data types in SQL Server 2008, mainly on how to use spatial data type columns in a data table, how to store spatial data into spatial data types, and how to query and preview spatial data and other related knowledge points.

Choosing to use spatial data types means that you must add a spatial data type column to the SQL data table to store the spatial data. There are usually two situations:

1. Create a new table with a spatial data type.

2. Add a Spatial Data type column field to the existing data table.

Create a new table of spatial data types

There is no difference between creating a new table with a spatial data type and creating a normal table, the only difference being that the spatial data type field is used. The following SQL demonstrates creating a data table with a spatial data type:

CREATE TABLE [Cities] (
[ID] [int] IDENTITY(1,1)  not NULL,
[CityName] [varchar](255)  not NULL,
[citylocation] [Geometry]  not NULL
)
GO

Ii. adding spatial data types to existing tables

Adding a Spatial data type column to an existing table and adding a new column to a normal table is the same as using the ALTER TABLE XXX add YYY datatype command to complete. The following code demonstrates adding a spatial data type field to the new table you just created:

--Add a Spatial data type field to an existing table
ALTER TABLE [Cities]
ADDGeo Geography
GO

Iii. Developing spatial reference identifiers (SRID) for spatial data type columns

Any Spatial data type field can be assigned an appropriate spatial reference identity (Srid), and some spatial data type fields must also have an SRID. Implementation of the Spatial Data Type column assignment Srid is also very simple, SQL provides an assignment command to complete, and the following SQL command demonstrates the implementation of assigning Srid to the newly added spatial data type above.

--to assign the Srid of a spatial data type column
ALTER TABLE [Cities]
ADD CONSTRAINT [Enforce_srid_geographycolumn]
CHECK(Geo. Stsrid= 4326)
GO

Iv. inserting spatial data into spatial data type fields

Inserting the Data Guide table also uses the "INSERT into table ..." syntax implementation, the difference is that when inserting spatial data, the data needs to be formatted to be successfully inserted into the spatial data type column. For both (geography and geometry) spatial data types, the system provides specialized spatial formatting syntax for processing, such as by inserting wkt text into a spatial data column, using the Geometry::stgeomfromtext () or the Geography::stgeomfromtext () function to convert the spatial data type format of the wkt text.

Insert  intoCities
(cityname,citylocation)
Values
('Chongqing', Geometry::stgeomfromtext ('POLYGON (107.04352 28.870554, 107.043891 28.873231 ...)',4326));

Note: The above SQL code block is implemented to insert the spatial data of the WKT format of Chongqing urban area into the citylocation field of the cities table, which is intercepted in the code snippet because it is too long in the wkt.

Iv. querying spatial data type data

Querying spatial data is almost indistinguishable from normal data queries, and it also uses the "Select xxx from table where x=y" method to implement data queries. The following SQL command shows the data record that the query just inserted:

Select *  fromCities;

　　　　　　

You can also use system functions to convert the spatial results of a query into wkt text format output, and the following SQL command details the use of the function:

--convert spatial data to wkt text
DECLARE @chongqinggeometry;
Select @chongqing =citylocation fromCitieswhereID=1;
Select @chongqing  asChongqing;
Select @chongqing. ToString () asChongqing;
Select @chongqing. Stastext () asChongqing; --WKT

V. Related information

[1], Spatial database (Baidu Encyclopedia): http://baike.baidu.com/view/1194566.htm

[2], using spatial data to achieve location intelligence: http://tech.ddvip.com/2008-04/120816526343873.html

[3], Basic space Object (MSDN): http://msdn.microsoft.com/de-de/library/bb964711.aspx

[4], the OGC method on the geometry instance: http://msdn.microsoft.com/zh-cn/visualc/bb933960.aspx

[5], the extension method on the geometry instance: http://msdn.microsoft.com/zh-cn/library/bb933880.aspx

[6], OGC static geometry method: Http://msdn.microsoft.com/zh-cn/library/bb933894.aspx

[7], Extended static geometry method: Http://msdn.microsoft.com/zh-cn/library/bb933805.aspx

Copyright notice

This article is an original article, welcome to reprint and note the source of the article, its copyright belongs to the author and the blog Garden altogether. In order to preserve the author's passion for creation, please mark the source of this article in the obvious place after reprint.

Author: Beniao

Article source: http://beniao.cnblogs.com/or http://www.cnblogs.com/

SQL Server 2008 Spatial Data Application series five: Using spatial data types in data tables