Geospatial Data in SQL Server/WKT

From Wikibooks, open books for an open world
< Geospatial Data in SQL Server
Jump to: navigation, search

Well-known text (WKT) is a text markup language for representing vector geometry objects on a map, spatial reference systems of spatial objects and transformations between spatial reference systems. A binary equivalent, known as well-known binary (WKB) is used to transfer and store the same information on databases, such as PostGIS. The formats are regulated by the Open Geospatial Consortium (OGC) and described in their Simple Feature Access and Coordinate Transformation Service specifications.

Geometric Objects[edit]

Geometric objects that can be represented with WKT are: points, lines, polygons, TINs and Polyhedron]. Multi geometries are also available to represent more than one geometry of the same dimension in a single object, and geometries of different dimensions can be stored in a geometry collection.

Coordinates for geometries may be 2D (x, y), 3D (x, y, z), 4D (x, y, z, m) with a m value that is part of a linear reference system or 3D with a m value (x, y, m). Three dimensional geometries are designated by a Z after the geometry type and geometries with a linear reference system have a M after the geometry type.

WKT geometries are used throughout OGC specifications and are present in applications that implement these specifications. For example, PostGIS contains functions that can convert geometries to and from a WKT representation, making them human readable.

The following are some example geometric WKT strings.

 POINT(6 10)
 LINESTRING(3 4,10 50,20 25)
 POLYGON((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2))
 MULTIPOINT(3.5 5.6,4.8 10.5)
 MULTILINESTRING((3 4,10 50,20 25),(-5 -8,-10 -8,-15 -4))
 MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2)),((3 3,6 2,6 4,3 3)))
 GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10))
 POINT ZM (1 1 5 60)
 POINT M (1 1 80)

Spatial Reference Systems[edit]

A WKT string for a spatial reference system describes the datum, geoid, coordinate system, and map projection of the spatial objects.

Well-known text is used extensively throughout many GIS programs. ESRI uses WKT in the shapefile *.prj file.

The following is an example of a spatial reference system WKT string.

COMPD_CS["OSGB36 / British National Grid + ODN",
    PROJCS["OSGB 1936 / British National Grid",
        GEOGCS["OSGB 1936",
            DATUM["OSGB_1936",
                SPHEROID["Airy 1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],
                TOWGS84[375,-111,431,0,0,0,0],
                AUTHORITY["EPSG","6277"]],
            PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
            UNIT["DMSH",0.0174532925199433,AUTHORITY["EPSG","9108"]],
            AXIS["Lat",NORTH],
            AXIS["Long",EAST],
            AUTHORITY["EPSG","4277"]],
        PROJECTION["Transverse_Mercator"],
        PARAMETER["latitude_of_origin",49],
        PARAMETER["central_meridian",-2],
        PARAMETER["scale_factor",0.999601272],
        PARAMETER["false_easting",400000],
        PARAMETER["false_northing",-100000],
        UNIT["metre",1,AUTHORITY["EPSG","9001"]],
        AXIS["E",EAST],
        AXIS["N",NORTH],
        AUTHORITY["EPSG","27700"]],
    VERT_CS["Newlyn",
        VERT_DATUM["Ordnance Datum Newlyn",2005,AUTHORITY["EPSG","5101"]],
        UNIT["metre",1,AUTHORITY["EPSG","9001"]],
        AXIS["Up",UP],
        AUTHORITY["EPSG","5701"]],
    AUTHORITY["EPSG","7405"]]

Transformations[edit]

A WKT format is defined to describe the transformation methods and parameters used to convert coordinates between two different spatial reference systems.

Below are two examples of WKT transformation descriptions.

PARAM_MT["Mercator_2SP", 
    PARAMETER["semi_major",6370997.0], 
    PARAMETER["semi_minor",6370997.0], 
    PARAMETER["central_meridian",180.0], 
    PARAMETER["false_easting",-500000.0], 
    PARAMETER["false_northing",-1000000.0], 
    PARAMETER["standard_parallel_1",60.0]]
PARAM_MT["Affine",
    PARAMETER["num_row",3],
    PARAMETER["num_col",3],
    PARAMETER["elt_0_1",1],
    PARAMETER["elt_0_2",2],
    PARAMETER["elt_1_2",3]]