Thursday, October 19th, 2017

SQL Server 2008: ????? ???? ??????

Published on Март 5, 2009 by   ·   Комментариев нет

??????? ??????? ? ?????????? ????????? ?????????? ???????? ??? ?????? ???????????? ????? ???? ??????, ????? ?????????? ? ??????? ???????. ???? ????? ????? ??????, ?????????? ? SQL Server 2008, ???????? ?????? ? ?????????? ??? ????? ??????? ??????.

???? ? ?????

????????? ?????? ?????? datetime, ???????????? SQL Server? ?? ????? ???????? ? ??????????? ? ???? ? ??????? ????????. ?????? ????? ???? ?????? ? date, time, datetime2 ? datetimeoffset ? ?????? ????, ??????? ?????? ? ??????? ? ???? ? ???????, ??????????? ??????? ???????? ???, ???????? ?? ????? ??????? ? ????????? ??????? ??????. ????? ?????????? ??? ??? ?????? ????? ???????????? ??? ????? ???? ?????? ?????? ??????????? datetime. ????????? ?? ??? ????? ?????? ??????.

??? ?????? date ????????? ???? ??? ?????????? ???????. ???????? ? ?? 1 ?????? 1000 ???? ?? 31 ??????? 9999 ???? (?? 0001-01-01 ?? 9999-12-31). ?????? ?????????? ???? ??????? ???? ?????? ??? ???????? ? ????? ???????? ? 10 ????. ???????? ???? ?????? ?????????? ????????? ????.

?? ???. 1 ????????, ??? ???????? ? ???????????????? ?????????? Date ? ????????? T-SQL. ?????????? @myDate1 ???????????????? ??????? ? ??????? ‘MM/DD/YYYY’. ?????????? @myDate2 ?? ????????????????, ??? ????? ????? ???????? NULL. ?????????? @myDate3 ???????????????? ?? ???? ????????? ??????? ??????????. ???????? ?????????? ????? ???? ? ????? ?????? ???????? ? ??????? ?????????? SELECT ??? SET, ??? ???????? ?? ??????? ????????? ???????? @myDate2. ??????? ??? ????? ????????? ? ? ????????. ?? ???. 2 ????????, ??? ??????? ??????? ? ????? ????????? ???.

????1?Create and initialize date variables in T-SQL scripts

DECLARE @myDate1 date = '01/22/2005'
DECLARE @myDate2 date
DECLARE @myDate3 date = GetDate()

SELECT @myDate2 = '2007-05-08 12:35:29.1234567 +12:15'

SELECT @myDate1 AS '@myDate1',
       @myDate2 AS '@myDate2',
       @myDate3 AS '@myDate3'

--Results
--@myDate1   @myDate2   @myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20

????2?Create a table with three date columns

USE TempDB
GO

CREATE TABLE myTable
(
    myDate1 date,myDate2 date,myDate3 date
)
GO

INSERT INTO myTable
VALUES('01/22/2005',
       '2007-05-08 12:35:29.1234567 +12:15',
       GetDate())

SELECT * FROM myTable

--Results
--myDate1    myDate2    myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20

??? ?????? time ????????? ????? ????? ??? ?????????? ????. ?? ??????? ?? 24-??????? ???????, ??????? ?????????????? ???????? ? ?? 00:00:00.0000000 ?? 23:59:59.9999999 (????, ??????, ??????? ? ???? ???????). ???????? ? ????? ??????? ????? ?????? ??? ???????? ???? ??????. ?? ????????? ???????????? 7 ???? ? ???????? 100 ??????????. ???????? ?????? ?? ?????????? ?????, ??????? ???????? ?? 3 ???? ??? ???? ????, 4 ???? ??? ?? 3 ?? 4 ???? ? ?? 5 ???? ??? ?? 5 ?? 7 ????.

???????? T-SQL ?? ???. 3 ??????????, ??? ??????? ?????????????? ???????? ????????????? ?????? ?????? ?? ???????? ??????????. ?????? ??? T-SQL ??????? ? ?????????????? ?? ?????????? ???????? ?????? ????????? ?????????? ???????. ???????? ? ????? ??????? ?????? ?????????? ????????????? ?? ????????. ????????, @myTime3 ????? ???????? ? ??? ????? ????? ???????. ?????????? ??????????, ??? ???????? ??????? ???? ?????? time ???????????? ???????? ? ?????, ? ??????? ??? ???????????. ?????, ?? ?????????? ? ????????, ?????????????.

???.?3?Display time data type’s variable precision

DECLARE @myTime  time = '01:01:01.1234567 +01:01'
DECLARE @myTime1 time(1) = '01:01:01.1234567 +01:01'
DECLARE @myTime2 time(2) = '01:01:01.1234567 +01:01'
DECLARE @myTime3 time(3) = '01:01:01.1234567 +01:01'
DECLARE @myTime4 time(4) = '01:01:01.1234567 +01:01'
DECLARE @myTime5 time(5) = '01:01:01.1234567 +01:01'
DECLARE @myTime6 time(6) = '01:01:01.1234567 +01:01'
DECLARE @myTime7 time(7) = '01:01:01.1234567 +01:01'

SELECT @myTime  AS '@myTime',
       @myTime1 AS '@myTime1',
       @myTime2 AS '@myTime2',
       @myTime3 AS '@myTime3',
       @myTime4 AS '@myTime4',
       @myTime5 AS '@myTime5',
       @myTime6 AS '@myTime6',
       @myTime7 AS '@myTime7'

--Results
--@myTime          @myTime1   @myTime2    @myTime3     @myTime4      
------------------ ---------- ----------- ------------ -------------
--01:01:01.1234567 01:01:01.1 01:01:01.12 01:01:01.123 01:01:01.1235
--
--@myTime5       @myTime6        @myTime7
---------------- --------------- ----------------
--01:01:01.12346 01:01:01.123457 01:01:01.1234567

DROP TABLE myTable

??? ?????? time ????? ??????? ??? ??????? ? ???????. ???????? T-SQL DROP TABLE myTable ?? ???. 4 ??????? ??????? ?myTable1? ? ????????? ? ??? ??? ??????? ???????. ????? ? ??????? ?????????? ??????; ?????????? ??????? ???????????? ? ??????? ?????????? SELECT.

???.?4?Create myTable1

USE TempDB
GO

CREATE TABLE myTable1
(
    myTime1 time(1),
    myTime2 time(2),
    myTime3 time(3)
)
GO

INSERT INTO myTable1
VALUES('01:30:01.1234567',
       '02:34:01.1234567',
       '03:01:59.1234567')

SELECT * from myTable1

--Results
--myTime1    myTime2     myTime3
------------ ----------- ------------
--01:30:01.1000000 02:34:15.1200000 03:01:59.1230000

DROP TABLE myTable1

Datetimeoffset ? Datetime2

??? ?????? datetimeoffset ???????????? ???????? ? ??????? ?????. ??? ?????? time ?? ???????? ???????? ????? ? ???????? ?????? ??? ???????? ???????. ?? ????????? ??????, ??? ?? ?????, ????? ????? ?????, ??? ????? ? ????? ????? ??????? ??????????? ?? ???????? ? ??????. ????? ???????? ????? ??????????? ??? + ??? — ??:??.

???? ??? ??????? ?????????? datetimeoffset ? ?????????????? ?? ?? ???????? ??????? 8:52 ?? ???????????? ?????????????? ???????:

DECLARE @date DATETIMEOFFSET = '2007-11-26T08:52:00.1234567-08:00'
PRINT @date
--Results
--2007-11-26 08:52:00.1234567 -08:00

??????, ??????? ?????????????? ?????????? datetimeoffset (? ???????? ? @date) ????????????? ?????? ???????, ? ????????????? ?? ???????? ????????? ???????? ? ????????. ???????? ???? ? ??????? ??????????? ????? ????????? T. ???? ??????? ???????? ???????? ??????? ?? ???????? ?????. ????? ??????? ? ?????????? ??????? ??? ???????? ????? ??? ????????. ???? ?????? ? ???? ?? ???? ???????? ISO 8601, ?????????????? ????? ?????? datetimeoffset. (ISO 8601 ? ??? ????????????? ???????? ?????? ???? ? ???????.)

???????? ?????????? ??????? ???????? ??? ??, ??? ??? ???? ?????? time, ?? ????????? ? ?? ?? ???? ????. ???????? ? ????? ??.

??? ?????? datetime2 ? ??? ?????????? ????????? ???? datetime. ?? ???????????? ??????? ???????? ??? ? ??????? ???????? ? ????? ???????, ???????? ???????? ????????. ???????? ??? ???? datetime2 ? ?? 1 ?????? 0001 ?? 31 ??????? 9999, ? ??????? ?? ????????? ????????? datetime ?? 1 ?????? 1753 ?? 31 ??????? 9999. ??? ? ? ???? time, ???????? ???????? ????? ??????? ? ???? ??????. ???????? ??? datetime ???????? ???????????? ??? ????? ? ???????? ??????? ?? 00:00:00 ?? 23:59:59.999. ??? ??? ????????? ? ???????????????? ?? ????? ? ???? ?????????? ??????? ?????????? datetime2:

DECLARE @datetime2 DATETIME2 = GetDate();
PRINT @datetime2

--Results
--2007-11-26 09:39:04.1370000

?????? ????????? ?? ????? ??? ?????? hierarchyid. ???? ??? ?????? ???????? ? ?????????? ????? ?????????? ?????? ? ???????, ? ?? ? ??????????? ??????? ? ???? ??? ???????.

??? ?????? hierarchyid

??? ?????? hierarchyid ????????? ????????? ????????? ????? ?????????? ?????? ? ???????, ??? ????, ????? ?????? ??????? ? ????????. ?????? ??????? ???? ??? ??????, ?????? ???? ?????? MyCompany ? ???????? ?? ??????? ? ??????????? ? ??????? ???????? ? ???. 5.

???.?5?Create and populate the MyCompany database

USE MASTER
GO

CREATE DATABASE MyCompany
GO
USE MyCompany
GO

--Create a table called employee that will store
--the data for the employees for MyCompany.

CREATE TABLE employee
(
    EmployeeID int NOT NULL,
    EmpName    varchar(20) NOT NULL,
    Title      varchar(20) NULL,
    Salary     decimal(18, 2) NOT NULL,
    hireDate   datetimeoffset(0) NOT NULL,
)
GO

--These statements will insert the data for the employees of MyCompany.

INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00, '2000-05-23T08:30:00-08:00')

INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(271, 'John',   'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(119, 'Jill',   'Specialist', 14000.00, '2007-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(269, 'Wanida', 'Assistant', 8000.00, '2003-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(272, 'Mary',   'Assistant', 8000.00, '2004-05-23T09:00:00-08:00')
GO
--Results
--EmployeeID  EmpName Title      Salary   hireDate
------------- ------- ---------- -------- --------------------------
--6           David   CEO        35900.00 2000-05-23 08:30:00 -08:00
--46          Sariya  Specialist 14000.00 2002-05-23 09:00:00 -08:00
--271         John    Specialist 14000.00 2002-05-23 09:00:00 -08:00
--119         Jill    Specialist 14000.00 2007-05-23 09:00:00 -08:00
--269         Wanida  Assistant  8000.00  2003-05-23 09:00:00 -08:00
--272         Mary    Assistant  8000.00  2004-05-23 09:00:00 -08:00

?????????? ?????????? ?? ???. 6 ??????? ???? ??????, ????????? ?? ????? ??????? ???????????. ??? ??????? ? ???? ?????? MyCompany ?? ????? ???????????? ?????????. ??? ????????? ??? ??????????? ???? ??????, ??? ????????? ???????????? ??????????? ?? ???? ?????????? ????? ??? ??? ?????? ? ??? ?????????.

Declare t sql

???. 6 ??????? ??????????? MyCompany

?????? ??????? ?????????? ???? ????? ????? ????? ?????????? ?????????. ????????, ?????? ????? ????? ????? ????? ??????????, ??? ??, ??????? ???????? ??? MyCompany ?? ???. 7. ??? ?????????? MyCompany ???????????? ????? ?????????? (CEO) ??????? (David). ????????? ???????????? ????????, ??? ????? (Jill). ?????????, ??? ???? (Mary), ???????????? ????? ????????????? ?????. ? ???????? ????????????????, ????????? ?????????? MyCompany ????? ??????? ???????, ??? ?????????? ??? ?? ?????. ?????, ?? ????? ?????, ?? ????? ??? ?? ????????????; ?? ?????????? ??? ????????. ??????????, ?????????????? ????? ???????, ????????? ????. ????? ???? ?????????? ??????????? ??? ???????????. ? ?????? ????? ???? ??????? ??????????, ??????? ?????, ????? ?????????? ?????? ??????.

Типы sql server 2008

???. 7 ??????????????? ????????? MyCompany

???????? ?? ???. 8 ?????? ???? ?????? MyCompany ? ??????? ???? ?????? hierarchyid, ???????? ?????????, ??????????????? ????????? ?????????? MyCompany. ??? ?????????? ??????? ???? hierarchyid ???????????? ?????????? ALTER TABLE. ?????, ? ??????? ?????? hierarchyid GetRoot ??????????? ???? ??????. ????? ? ?????? ??????????? ?????????????? ????? ??????? ???????? ? ??????? ?????? GetDescendant.

???.?8?Rebuild the database using hierarchyid

DELETE employee
GO
ALTER TABLE employee ADD OrgNode hierarchyid NOT NULL
GO

DECLARE @child hierarchyid,
@Manager hierarchyid = hierarchyid::GetRoot()

--The first step is to add the node at the top of the
--tree. Since David is the CEO his node will be the
--root node.

INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00,
       '2000-05-23T08:30:00-08:00', @Manager)

--The next step is to insert the records for
--the employees that report directly to David.

SELECT @child = @Manager.GetDescendant(NULL, NULL)

INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)

SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(271, ?John',   ?Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)

SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(119, ?Jill',   ?Specialist', 14000.00,
       ?2007-05-23T09:00:00-08:00', @child)

--We can now insert the employee that reports to
--Sariya.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 46

INSERT INTO employee
VALUES(269, ?Wanida', ?Assistant', 8000.00,
       ?2003-05-23T09:00:00-08:00', @manager)

--Next insert the employee that report to John.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 271

INSERT INTO employee
VALUES(272, ?Mary',   ?Assistant', 8000.00,
       ?2004-05-23T09:00:00-08:00', @manager)

????? ?????????? ??????? ???? ?????? ? ???????? ?????????, ?????????? ??????? ??????????? ????? ??????? ? ??????? ?????? ???????:

SELECT EmpName, Title, Salary, OrgNode.ToString() AS OrgNode
FROM employee ORDER BY OrgNode
GO
--Results
--EmpName  Title      Salary    OrgNode
---------- ---------- --------- -------
--David    CEO        35900.00  /
cheap cialis online
--Sariya Specialist 14000.00 /1/ --Wanida Assistant 8000.00 /1/1/ --John Specialist 14000.00 /2/ --Mary Assistant 8000.00 /2/1/ --Jill Specialist 14000.00 /3/

OrgNode ? ??????? hierarchyid. ?????? ?????? ????? ????? (/) ? ??????????? ???????? ???? ? ?????? ????????. ????? ????????? ? ?????, ??? ?????????? ????? ??????. ????? (Sariya), ???? (John) ? ???? ???????????? ????? ??????? ? ????? ?? ??? ?????, ??? ??????, ??? ??? ? ?????? ???? ????????. ????? 1, 2 ? 3 ?????????? ?????????? ????? ???????????????? ????????? ????. ??? ??????? ???????? ??????? ?????????. ???????? ???? ????? ???????, ????????? ? ????????? ??? ??????. ???? ??, ????????, ??????? ?????????? ????? ?????? ? ?????, ???? ????????? ????? ????????? ? ??????????? ??? /2.1/.

????? ????? ????? ?? ??????, ???????, ???? ???????????? ????? ????????, ????? ??????? ?????? ????? ?????:

DECLARE @Sariya hierarchyid

SELECT @Sariya = OrgNode
FROM employee WHERE EmployeeID = 46

SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetAncestor(1) = @Sariya
GO
--Results
--EmpName Title     Salary  OrgNode
--------- --------- ------- -------
--Wanida  Assistant 8000.00 /1/1/

???? ?????? ?????????? ????? hierarchyid GetAncestor, ??????? ?????????? ???????????? ???? ???????? ???? hierarchyid. ? ??????????? ????, ?????????? @Sariya ??????????? ?? ???? ???????? ?????. ??? ?????????? ??????, ??? ????? ? ?????? ???????? ?????? ??????????, ??????????????? ????? ???. ??????? ??????, ???????????? ???????????, ?????????????? ????? ????? ??????, ?????? ???????? ?? ????????? ?? ???? ?? ??????, ? ????? ?????? ???? ???????????, ??? ??????? ????-?????? ? ??? ???? ?????.

??????? hierarchyid ?????? ????? ??????????, ?????? ??? ?????????? ???, ??????????? ??? ????????????? ???? ??????, ??????? ?? ???????? ????? ???????? ????? (??????? ?????? ???????? ?????????? ????). ????????, ????? ???? ? ??????????????? ???????? 100000 ??????????? ?? ??????? ??????? ????????? ????? ???????, ?????? ???????? ???? ???? ?????.

??? ?????? hierarchyid ???????????? ????????? ???????, ???????????? ?????? ? ?????????????? ???????. ??? ???????? ?? ???. 9. ????????? ?????????? ? ???? ??????? ????? ????? ?? SQL Server Books Online (??????????? ????? ?? SQL Server) (msdn2.microsoft.com/ms130214).

???.?9?Methods provided by the hierarchyid data type

????? ????????
GetAncestor ?????????? hierarchyid, ??????????????? n-???? ?????? ????? ???? hierarchyid.
GetDescendant ?????????? ???????? ???? ????? ???? hierarchyid.
GetLevel ?????????? ????? ?????, ?????????????? ??????? ??? ???? hierarchyid ? ????? ????????.
GetRoot ?????????? ???????? ???? hierarchyid ????? ?????? ????????. ???????????.
IsDescendant ?????????? true, ???? ?????????? ???????? ???? ???????? ???????? ????? ???? hierarchyid.
Parse ????????? ???????? ????????????? ???????? ? ???????? hierarchyid. ???????????.
Reparent ??????????? ???? ? ?????? ????????? ? ????????.
ToString ?????????? ??????, ?????????? ?????????? ????????????? ????? hierarchyid.

???????????????? ???? ??????

????????????????? ?????? ? ??? ??????, ???????????? ?????????????? ???????????? ? ?????, ??????????????? ?? ?????. ??? ????? ???? ?????????, ?????? ? ???? ???????????? ?????. ? SQL Server 2008 ???? ?????????????? (geography) ? ?????????????? (geometry) ???? ?????? ??? ?????? ? ???? ???????????.

??? ?????? geography ???????? ? ?????????? ??? ???????????? ?????. ?????? ???????????? ????? ?????????? ??? ???????? ???????? ?????? ???????????. ?????????? ? ????????? ???????? ??????? ? ????????. ??? ?????? ?????? ??????? ??? ??????????, ????????? ? ???????? ???????????, ??????? ????????????? ? ?????????????? ????????????, ???????? ???????? ? ?????? ???????????. ??? ?????? ????? ????????????, ???? ?????? ???????? ? ???? ????? ? ??????.

??? ?????? geometry ???????? ? ????????? ??????? ??? ??????? ??????? ?????. ? ???? ?????? ????? ????????? ??????? ????????? ?? ???????????? ?????. ?????? ??????? ????? ?? ????????? ? ?????? ???????? ??????????? ?????, ??????? ????????????, ? ?????? ???????, ??? ???????? ???????? ??????????, ????????, ? ???? ?????? ??????????, ???????????? ?????????? ????? ????????.

???? geography ? geometry ????????? ?? ????????? ????????, ???????? ? ???????? Well-Known Text (WKT) ??? Well-Known Binary (WKB). ??? ??????? ??? ??????????? ???????????????? ??????, ????????? ? ??????? ???????? ????????? ???????????????????? ??????????? (Open Geospatial Consortium (OGC) Simple Features) ??? ???????????? SQL (SQL Specification). ?? ???. 10 ??????????? ???? ????? ????????? ????????, ?????????????? SQL Server 2008.

???.?10?Vector objects supported by SQL Server 2008

?????? ????????
Point ????????????.
MultiPoint ????? ????????????.
LineString ????? ?? ???? ??? ????? ?????, ??????????? ???????.
MultiLineString ????? ?????? (linestring).
Polygon ??????????? ???????, ??????????? ??????? ???????? ?????? (linestring).
MultiPolygon ????? ??????????????? (polygon).
GeometryCollection ???????? ????? geometry.

????? ??????? ??? geography ? ????? ??? ??????????? ?????????? ?????????, ?????? ???? ???????? ??? geography ? ????? ???????? T-SQL, ??? ?? ???. 11. ????? ????? ??????? ???? ?? ??????? ? ???. 12 ? ???????? ?????? ???????? ?????? ?????????? ??????? ? ????????????? ????????????????? ????????? (Spatial Reference ID) (SRID). SRID ? ??? ??????? ????????????? ????????????????? ?????????, ????????? ??????????? ??????? ?????????????? ???????????? (European Petroleum Survey Group). ??? ????? ?????? ??????????, ????????????? ??? ???????????, ???????? ? ???????? ????????????? ??????. ?????? SRID ?????? ??? ?????????????? ???????? ???????????? ??? ??????????. ??? ?????, ?????? ??? ????? ? ?? ????????? ?????. SQL Server 2008 ????? ????????? ??????? ?????? ?? ?????????? SRID.

???. 12?Construct objects for geography and geometry

????? ????????
STGeomFromText ??????? ???????? geography ?? ?????? ?? ?????.
STPointFromText ??????? ??????? geography Point ?? ?????? ?? ?????.
STMPointFromText ??????? ??????? geography MultiPoint ?? ?????? ?? ?????.
STLineFromText ??????? ??????? geography LineString ?? ?????? ?? ?????.
STMLineFromText ??????? ??????? geography MultiLineString ?? ?????? ?? ?????.
STPolyFromText ??????? ??????? geography Polygon ?? ?????? ?? ?????.
STMPolyFromText ??????? ??????? geography MultiPolygon ?? ?????? ?? ?????.
STGeomCollFromText ??????? ??????? geography Geometry?Collection ?? ?????? ?? ?????.

???.?11?Create points, lines, and polygon geometry

DECLARE @geo1 geometry
SELECT @geo1 = geometry::STGeomFromText('POINT (3 4)', 0)
PRINT @geo1.ToString()

DECLARE @geo2 geometry
SELECT @geo2 = geometry::Parse('POINT(3 4 7 2.5)')
PRINT @geo2.STX;
PRINT @geo2.STY;
PRINT @geo2.Z;
PRINT @geo2.M;

DECLARE @geo3 geography;
SELECT @geo3 = geography::STGeomFromText(
    'LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326);
SELECT @geo3.ToString();

--Results
--POINT (3 4)
--3
--4
--7
--2.5

DECLARE @gx geometry; 
SET @gx = geometry::STPolyFromText(
    'POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);
PRINT @gx.ToString();
--Results
--POLYGON ((5 5, 10 5, 10 10, 5 5))

???????? ????? geography ? geometry

???? ?????? geography ? geometry ??????? ??? ?????? ? ?????????? ?????? ??????, ??????? ??????? ????? ? ????????? ???????? ???????? ????? ????. ? ???? ?????? geometry, ?????????? ? ??????? ???????? ? ??? ?? ???????? ?????????, ??? ? ?????????? ????????. ????????, ?????????? ????? ??????? (0,0) ? (6,8) ?????? ????? 10 ??????. ? ???? geography ?????, ?? ?????????? ????????????? ??????????, ???????????? ? ???????? ?????? ? ???????.

???? ?????????? ???????? ?????? ?????? ? ???????, ??? ?????? GEOMETRY ?????????? ???????????? ??????????. ????????? ??? T-SQL ??????????? ?????????? ????? POINTS (???????) (90 0) ? (90 180). ??? ??? ????????? ?? ???????? ??????, ??????? ?????????? ?????? ???? ?????? 0. ? ?????? GEOMETRY ????????? ?????????? ??????????? 180.

DECLARE @g1 GEOMETRY, @g2 GEOMETRY, @g3 GEOGRAPHY, @g4 GEOGRAPHY
SELECT @g1 = GEOMETRY::STGeomFromText('POINT (90 0)', 0)
SELECT @g2 = GEOMETRY::STGeomFromText('POINT (90 180)', 0)

SELECT @g3 = GEOGRAPHY::STGeomFromText('POINT (90 0)', 4326)
SELECT @g4 = GEOGRAPHY::STGeomFromText('POINT (90 180)', 4326)
SELECT @g2.STDistance(@g1) AS 'GEOMETRY',
       @g4.STDistance(@g3) AS 'GEOGRAPHY';

--Results
--GEOMETRY               GEOGRAPHY
------------------------ ----------------------
--180                    0

?????????? ???????????????? ?????? ??? ???? ????? ?????? ???? ???????????. ? ????????? ???????, ???????????? ????? ?????? geometry, ?????????? ?????????????? ?? ???????? ?????? ????????. ????????, ????????????? ? ???????????? ((0, 0), (10, 0), (0, 20), (0, 0)) ? ?? ?? ?????, ??? ? ????????????? ((0, 0), (0, 20), (10, 0), (0, 0)). ????????, ??? ?????? geography ?????????? ??????, ? ??????? ?????????? ?????????? ?????????? ??????????????. ???????, ????????, ?????? ????? ????????. ????????? ?? ?????????????, ??????????? ???? ???????, ? ????????? ??? ?????? ?????????? ??? ??????, ??? ??? ?????? ? ??????? geography, ?????????? ? ???????????? ?????? ??????????? ?????.

???? ? ?????????????? ???????????, ????????????? SQL Server 2008 ?? ??? ?????? geography. ????????, ?????? ??????? ?????? ???????? ? ???? ?????????. ??????? ???????????????? ??????? ??????????? ? ??????? ArgumentException. ???? ?????? geography, ????????? ???? ??????, ?????????? NULL, ???? ?????????? ??????? ?? ???????? ? ???? ?????????.

? SQL Server ???? ????????? ???????, ??????????? ????????? ???????? ? ????????? geography ? geometry. ?? ???. 13 ???????? ????????? ??????? ????????????? ??????? ?????? ? ????????????????? ??????? ? SQL Server 2008. ? ?? ???? ? ???????????? ?????? ?????? ?????????? ?? ???? ?????????, ?? ?? ??????? ????? ?????? ???????? ? SQL Server Books Online.

???.?13?Working with spatial data

DECLARE @gm geometry;
DECLARE @gg geography;
DECLARE @h geography;

SET @gm = geometry::STGeomFromText('POLYGON((0 0, 13 0, 3 3, 0 13, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @gm.STArea();

--Results
--38

SET @gg = geography::STGeomFromText('LINESTRING(0 0, 5 5)', 4326);
--Calculate the distance to a point slightly offset from the LINESTRING.
SET @h = geography::STGeomFromText('POINT(4 4)', 4326);
SELECT @gg.STDistance(@h);

--Results
-- 430.182777043046

--Calculate the distance to a point on the LINESTRING.
SET @h = geography::STGeomFromText('POINT(5 5)', 4326);
SELECT @gg.STDistance(@h);

--Results
-- 0

DECLARE @temp table ([name] varchar(10), [geom] geography);

INSERT INTO @temp values ('Point', geography::STGeomFromText('POINT(
5 10)', 4326));
INSERT INTO @temp values ('LineString', geography::STGeomFromText(
'LINESTRING(13 5, 50 25)', 4326));
--Calculate the distance to a point on the LINESTRING.
--Display the number of dimensions for a geography object stored in a --table variable.
INSERT INTO @temp values ('Polygon', geography::STGeomFromText(
'POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326));

SELECT [name], [geom].STDimension() as [dim]
FROM @temp;

--Results
--name       dim
------------ -----------
--Point      0
--LineString 1
--Polygon    2

???????, ??? ??? ?????????? ? ???? ????? ????? ?????? ? SQL Server 2008 ???????? ??????.

?c??????: TechNet Magazine


Смотрите также:

Tags: , ,

Readers Comments (Комментариев нет)

Comments are closed.

Exchange 2007

Проведение мониторинга Exchange 2007 с помощью диспетчера System Center Operations Manager 2007 (часть 3)

Если вы хотите прочитать предыдущие части этой серии статей, перейдите по ссылкам: Проведение мониторинга Exchange 2007 с помощью диспетчера System ... [+]

Практическое рассмотрение перехода с Exchange 2003 на Exchange 2007 (часть 1)

Введение В этой статье из нескольких частей я хочу показать вам процесс, который недавно использовал для перехода с существующей среды Exchange 2003 ... [+]

Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (часть 2)

Если вы пропустили первую часть этой серии, пожалуйста, прочтите ее по ссылке Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (Часть ... [+]

Мониторинг Exchange 2007 с помощью диспетчера System Center Operations Manager 2007 (часть 2)

Если вы пропустили предыдущую часть этой серии статей, перейдите по ссылке Мониторинг Exchange 2007 с помощью диспетчера System Center Operations ... [+]

Подробное рассмотрение подготовки Active Directory для Exchange 2007 (часть 5)

Если вы пропустили предыдущие части этой серии статей, перейдите по ссылкам: Подробное рассмотрение подготовки Active Directory для Exchange 2007 (часть 1) ... [+]

Установка и настройка Exchange 2007 из командной строки (Часть 3)

If you missed the previous parts in this article series please read: Exchange 2007 Install and Configuration from the command line (Part ... [+]

Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (часть 1)

Инструмент ExRCA Текущий выпуск инструмента предоставляется только в целях тестирования и оснащен 5 опциями: Тест подключения Outlook 2007 Autodiscover Тест подключения Outlook 2003 RPC ... [+]

Развертывание сервера Exchange 2007 Edge Transport (часть 5)

Если вы хотите прочитать предыдущие части этой серии статей, перейдите по ссылкам: Развертывание сервера Exchange 2007 Edge Transport (часть 1) Развертывание ... [+]

Установка и настройка Exchange 2007 из командной строки (часть 2)

Если вы пропустили первую статью данного цикла, пожалуйста, перейдите по ссылке: Exchange 2007 Install and Configuration from the command line (Part ... [+]

Использование интегрированных сценариев Using Exchange Server 2007 – часть 2: генерирование отчетов агента Transport AntiSpam Agent

Если вы пропустили предыдущую часть этой серии статей, перейдите по ссылке Использование интегрированных сценариев Using Exchange Server 2007 – часть ... [+]