SQL Server: Working with Spatial Data Types

As you already know, the Database Engine supports two different data types in relation to spatial data: GEOMETRY and GEOGRAPHY. Both data types have several subtypes, which are either instantiable or noninstantiable. For each instantiable subtype, you can create instances and work with them. These instances can be used as values of a table’s columns, as well as variables or parameters. As you already guessed, noninstantiable types do not contain instances.

In a hierarchy of classes, the root class is usually noninstantiable, while the classes that build the leaves of the hierarchy tree are almost always instantiable.

The following two subsections describe how you can use these two data types to create and query spatial data. After that, the spatial indices will be introduced.

1. Working with the GEOMETRY Data Type

An example will help to explain the use of the GEOMETRY data type. Example 30.1 creates a table for nonalcoholic beverage markets in a given city (or state).

Example 30.1

USE sample;

CREATE TABLE beverage_markets

(id INTEGER IDENTITY(1,1),

name VARCHAR(25),

shape GEOMETRY);

INSERT INTO beverage_markets

VALUES (‘Coke’, GEOMETRY::STGeomFromText

(‘POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))’, 0));

INSERT INTO beverage_markets

VALUES (‘Pepsi’, GEOMETRY::STGeomFromText

(‘POLYGON ((300 0, 150 0, 150 150, 300 150, 300 0))’, 0));

INSERT INTO beverage_markets

VALUES (‘7UP’, GEOMETRY::STGeomFromText

(‘POLYGON ((300 0, 150 0, 150 150, 300 150, 300 0))’, 0));

INSERT INTO beverage_markets

VALUES (‘Almdudler’, GEOMETRY::STGeomFromText(‘POINT (50 0)’, 0));

The beverage_markets table has three columns. The first is the id column, the values of which are generated by the system because this column is specified with the IDENTITY property. The second column, name, contains the beverage name. The third column, shape, specifies the shape of the market area in which the particular beverage is the most preferred one. The first three INSERT statements create three areas in which a particular beverage is most preferred. All three areas happen to be a polygon. The fourth INSERT statement inserts a point because there is just one place where the particular beverage (Almdudler) can be bought.

NOTE If you take a look at the specification of the POINT (or POLYGON) data type in Example 30.1, you will see that this specification has an additional parameter as the last parameter, the spatial reference ID (SRID) parameter. This parameter is required, and for the GEOMETRY data type the default value is 0.

Example 30.1 introduces the first method in relation to the GEOMETRY data type: STGeomFromText(). This static method is used to insert the coordinates of geometric figures, such as polygons and points. In other words, it returns an instance of the GEOMETRY data type in the WKT format.

NOTE Generally, a type can have two different groups of methods: static methods and instance methods. Static methods are always applied on the whole type (i.e., class), while instance methods are applied on particular instances of the class. The invocation of methods from both groups is different. Static methods use the sign “::” between the type and the method (for instance, GEOMETRY::STGeomFromText; see Example 30.1), while instance methods use dot notation (for instance, @g.STContains; see Example 30.2).

Besides the STGeomFromText() method, the Database Engine supports three other similar static methods:

  • STPointFromText() Returns the WKT representation of an instance of the POINT data type
  • STLineFromText() Returns the WKT representation of an instance of the LINESTRING data type augmented with the corresponding elevation and measure values
  • STPolyFromText() Returns the WKT representation of an instance of the MULTIPOLYGON data type augmented with the corresponding elevation and measure values

Spatial data can be queried the same way as relational data. The following examples show a sample of the information that can be found from the content of the shape column of the beverage_markets table.

NOTE The Database Engine supports a lot of methods that can be applied to instances of the GEOMETRY data type. The following examples describe only some of the most important methods. For more information on other instance methods, refer to Microsoft Docs.

Example 30.2 shows the use of the STContains() method.

Example 30.2

Determine whether the shop that sells Almdudler lies within the area where Coke is the preferred beverage:

USE sample;

DECLARE @g geometry;

DECLARE @h geometry;

SELECT @h = shape FROM beverage_markets WHERE name =’Almdudler’;

SELECT @g = shape FROM beverage_markets WHERE name = ‘Coke’;

SELECT @g.STContains(@h);

The result is 0.

The STContains() method returns 1 if an instance of the GEOMETRY data type completely contains another instance of the same type, which is specified as a parameter of the method.

The result of Example 30.2 means that the shop that sells Almdudler does not lie within the area where the preferred beverage is Coke.

Example 30.3 shows the use of the STLength() method.

Example 30.3

Find the length and the WKT representation of the shape column for the Almdudler shop:

USE sample;

SELECT id, shape.ToString() AS wkt, shape.STLength() AS length

FROM beverage_markets

WHERE name = ‘Almdudler’ ;

The result is

The STLength() method in Example 30.3 returns the total length of the elements of the GEOMETRY data type. (The result is 0 because the displayed value is a point.) The ToString() method returns a string with the logical representation of the current instance. As you can see from the result of Example 30.3, this method is used to load all properties of the given point and to display it using the WKT format.

Example 30.4 shows the use of the STIntersects() method.

Example 30.4

Determine whether the region that sells Coke intersects with the region that sells Pepsi:

USE sample;

DECLARE @g geometry;

DECLARE @h geometry;

SELECT @h = shape FROM beverage_markets WHERE name = ‘Coke’;

SELECT @g = shape FROM beverage_markets WHERE name = ‘Pepsi’;

SELECT @g.STIntersects(@h);

The result of Example 30.4 is 1 (TRUE), meaning that the two instances intersect.

In contrast to Example 30.3, where the column of a table is declared to be of the GEOMETRY data type, Example 30.4 declares the variables @g and @h using this data type.

(As you already know, table columns, variables, and parameters of stored procedures can be declared to be of the GEOMETRY data type.) The STIntersects() method returns 1 if a geometry instance intersects another geometry instance. In Example 30.4, the method is applied to both regions, declared by variables, to find out whether the regions intersect. Example 30.5 shows the use of the STIntersection() method.

Example 30.5

USE sample;

DECLARE @poly1 GEOMETRY = ‘POLYGON ((1 1, 1 4, 4 4, 4 1, 1 1))’;

DECLARE @poly2 GEOMETRY = ‘POLYGON ((2 2, 2 6, 6 6, 6 2, 2 2))’;

DECLARE @result GEOMETRY;

SELECT @result = @poly1.STIntersection(@poly2);

SELECT @result.STAsText();

The result is (the values are rounded):

POLYGON ((2 2, 4 2, 4 4, 2 4, 2 2))

The STIntersection() method returns an object representing the points where an instance of the GEOMETRY data type intersects another instance of the same type. Therefore, Example 30.5 returns the rectangle where the polygon declared by the @poly1 variable and the polygon declared by the @poly2 variable intersect. The STAsText() method returns the WKT representation of a GEOMETRY instance, which is the result of the example.

NOTE The difference between the STIntersects() and STIntersection() methods is that the former method tests whether two geometry objects intersect, while the latter displays the intersection object.

2. Working with the GEOGRAPHY Data Type

The GEOGRAPHY data type is handled in the same way as the GEOMETRY data type. This means that the same (static and instance) methods that you can apply to the GEOMETRY data type are applicable to the GEOGRAPHY data type, too. For this reason, only Example 30.6 is used to describe this data type.

Example 30.6

USE AdventureWorks;

SELECT SpatialLocation, City

FROM Person.Address

WHERE City = ‘Dallas’;

The result is

The Address table of the AdventureWorks database contains a column called SpatialLocation, which is of the GEOGRAPHY data type. Example 30.6 displays the geographic location of all persons living in Dallas. As you can see from the result of this example, the value in the SpatialLocation column is the hexadecimal representation of the longitude and latitude of the location where each person lives. (Example 30.10, later in the chapter, uses Management Studio to display the result of this query.)

3. Spatial Indices

Spatial indices are necessary to speed up retrieval operations on spatial data. The Database Engine supports two different spatial index types:

  • Spatial index
  • Auto grid index

The following two subsections describe these indices. The subsequent two subsections show you how to edit information concerning spatial indices and how to display spatial objects.

3.1. Spatial Index

A spatial index is defined on a table column of the GEOMETRY or GEOGRAPHY data type. These indices are built using B-trees, which means that the indices represent two dimensions in the linear order of B-trees. Therefore, before reading data into a spatial index, the system implements a hierarchical uniform decomposition of space. The index-creation process decomposes the space into a four-level grid hierarchy.

The CREATE SPATIAL INDEX statement is used to create a spatial index. The general form of this statement is similar to the traditional CREATE INDEX statement, but contains additional options and clauses, some of which are introduced here:

  • GEOMETRY_GRID clause Specifies the geometry grid tessellation scheme that you are using. (Tessellation is a process that is performed after reading the data for a spatial object. During this process, the object is fitted into the grid hierarchy by associating it with a set of grid cells that it touches.) Note that GEOMETRY_GRID can be specified only on a column of the GEOMETRY data type.
  • BOUNDING_BOX option Specifies a numeric four-tuple that defines the four coordinates of the bounding box: the X-min and Y-min coordinates of the lower-left corner, and the X-max and Y-max coordinates of the upper-right corner. This option applies only within the GEOMETRY_GRID clause.
  • GEOGRAPHY_GRID clause Specifies the geography grid tessellation scheme. This clause can be specified only on a column of the GEOGRAPHY data type.

Example 30.7 shows the creation of a spatial index for the shape column of the beverage_ markets table.

Example 30.7

USE sample;

GO

ALTER TABLE beverage_markets ADD CONSTRAINT prim_key PRIMARY KEY(id);

GO

CREATE SPATIAL INDEX i_spatial_shape

ON beverage_markets(shape)

USING GEOMETRY_GRID

WITH (BOUNDING_BOX = ( xmin=0, ymin=0, xmax=500, ymax=200 ),

GRIDS = (LOW, LOW, MEDIUM, HIGH),

PAD_INDEX = ON );

A spatial index can be created only if the primary key for the table with a spatial data column is explicitly defined. For this reason, the first statement in Example 30.7 is the ALTER TABLE statement, which defines this constraint.

The subsequent CREATE SPATIAL INDEX statement creates the index using the GEOMETRY_GRID clause. The BOUNDING_BOX option specifies the boundaries inside which the instance of the shape column will be placed. The GRIDS option specifies the density of the grid at each level of a tessellation scheme. (The PAD_INDEX option specifies that the FILLFACTOR setting should be applied to the index pages as well as to the data pages in the index.)

3.2. Auto Grid Index

The strategy that the auto grid index uses is to pick the right trade-off between performance and efficiency, which is different from the strategy used for a regular spatial index. The auto grid index uses eight levels of tessellation for better approximation of objects of various sizes. (The already described spatial index uses only four user-specified levels of tessellation.)

Example 30.8 shows the creation of a geometry auto grid index.

Example 30.8

USE sample;

CREATE SPATIAL INDEX auto_grid_index

ON beverage_markets(shape)

USING GEOMETRY_AUTO_GRID

WITH (BOUNDING_BOX = (xmin=0, ymin=0, xmax=500, ymax=200 ),

CELLS_PER_OBJECT = 32, DATA_COMPRESSION = page);

The GEOMETRY_AUTO_GRID clause describes the created index as a geometry auto grid index. The CELLS_PER_OBJECT clause specifies the maximum number of cells that tessellation can count per object. The DATA_COMPRESSION clause specifies whether the compression is enabled for the particular spatial index and, if so, which compression type is used. (In Example 30.8, page compression is used on data. Two other options are NONE and ROW.) All other clauses of the CREATE SPATIAL INDEX statement are described immediately following Example 30.7.

3.3. Editing Information Concerning Spatial Indices

The system supports, among others, three catalog views related to spatial data:

  • spatial_indexes
  • spatial_index_tessellations
  • spatial_reference_systems

The sys.spatial_indexes view represents the main index information of the spatial indices (see Example 30.9). Using the sys.spatial_index_tessellations view, you can display the information about the tessellation scheme and parameters of each of the existing spatial indices. The sys.spatial_reference_systems view lists all the spatial reference systems supported by the system. (Spatial reference systems are used to identify a particular coordinate system.) The main columns of the view are spatial_reference_id and well_known_text. The former is the unique identifier of the corresponding reference system, while the latter describes that system. Example 30.9 shows the use of the sys.spatial_indexes catalog view.

Example 30.9

USE sample;

SELECT object_id, name, type_desc

FROM sys.spatial_indexes;

The result is

The catalog view in Example 30.9 displays the information about the existing spatial indices, created in Examples 30.7 and 30.8.

3.4. Displaying Spatial Objects

Microsoft extended the functionality of SQL Server Management Studio to display spatial data in a graphical form. Two examples will be presented to show this functionality. Example 30.10 displays the union of two GEOMETRY data types (LineString and Polygon), while Example 30.11 displays a MultiPolygon instance, which is a collection of zero or more Polygon instances.

Example 30.10

USE sample;

DECLARE @rectangle1 GEOMETRY = ‘POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))’;

DECLARE @line GEOMETRY = ‘LINESTRING (0 2, 4 4)’;

SELECT @rectangle1

UNION ALL

SELECT @line

To display the spatial object that is built as a result of Example 30.10, execute the batch in Example 30.10 and click the Spatial Results tab (which is next to the Results tab in the lower part of the editor in Management Studio). In the case of Example 30.10, Management Studio displays a rectangle showing the content of the @rectangle1 variable and the line from the @line variable (see Figure 30-2).

NOTE If you want to display multiple objects of the GEOMETRY data type using Management Studio, you have to return them as multiple rows in a single table. For this reason, Example 30.10 uses two SELECT statements combined into one using the UNION ALL clause. (Otherwise, only one point at a time will be displayed.)

Example 30.11 creates a MultiPolygon instance, which is shown in Figure 30-3.

Example 30.11

DECLARE @g1 geometry =

‘MultiPolygon(((2 0, 3 1, 2 2, 1.5 1.5, 2 1, 1.5 0.5, 2 0)),

((1 0, 1.5 0.5, 1 1, 1.5 1.5, 1 2, 0 1, 1 0)))’;

select @g1

4. Editing Information Concerning Spatial Data

You can use two system stored procedures to display information in relation to spatial data:

  • sp_help_spatial_geometry_histogram
  • sp_help_spatial_geography_histogram

NOTE The syntax and functionality of both system procedures is similar, so I will discuss only the first one.

The sp_help_spatial_geometry_histogram system stored procedure returns the names and values for a specified set of properties of a geometry spatial index. The result is returned in a table format. You can choose to return a core set of properties or all properties of the index. Example 30.12 shows the use of this system stored procedure.

Example 30.12

USE sample;

DECLARE @query geometry

=’POLYGON((-90.0 -180.0, -90.0 180.0, 90.0 180.0, 90.0 -180.0,-90.0 -180.0))’;

EXEC sp_help_spatial_geometry_index

 ‘beverage_markets’, ‘auto_grid_index’, 0, @query;

The sp_help_spatial_geometry_index system procedure in Example 30.12 displays the properties of the spatial index called auto_grid_index, created in Example 30.8.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *