Table Column Types for Spatial Geometry Data

MySQL Tutorials - Herong's Tutorial Examples

∟Table Column Types for Different Types of Values

∟Table Column Types for Spatial Geometry Data

Describes 8 types of table column to store Spatial Geometry Data: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, GEOMETRYCOLLECTION.

There is 8 types of table column to store Spatial Geometry Data: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, GEOMETRYCOLLECTION.

"GEOMETRY" - A Spatial Geometry data which could be Point, LineString, Polygon, etc.

"POINT" - A Spatial data representing a single point in the space.

"LINESTRING" - A Spatial data representing a connected string of line segments.

"POLYGON" - A Spatial data representing a polygon enclosed by one or more closed line strings.

"MULTIPOINT" - A Spatial data representing a collection of points.

"MULTILINESTRING" - A Spatial data representing a collection of line strings.

"MULTIPOLYGON" - A Spatial data representing a collection of polygons.

"GEOMETRYCOLLECTION" - A Spatial data representing a collection of spatial objects.

Examples of look up value column types, SpatialColumns.sql:

-- SpatialColumns.sql
-- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
--
CREATE TABLE SpatialData (
   Line VARCHAR(2),
   G GEOMETRY,
   P POINT,
   L LINESTRING,
   PL POLYGON
   );
INSERT INTO SpatialData VALUES (
   '1',
   ST_GeomFromText('POINT(1 1)'),
   Point(1,1),
   ST_LineStringFromText('LINESTRING(0 0, 1 0, 1 1, 0 1)'),
   ST_PolygonFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
   );

SELECT Line, ST_Astext(G), ST_Astext(P), ST_Astext(L), ST_Astext(PL)
   FROM SpatialData;

DROP TABLE SpatialData;

Running SpatialColumns.sql on MySQL 8.0 server gives you:

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < SpatialColumns.sql

Line    ST_Astext(G)    ST_Astext(P)    ST_Astext(L)    ST_Astext(PL)
1       POINT(1 1)      POINT(1 1)      LINESTRING(0 0,1 0,1 1,0 1)
   POLYGON((0 0,1 0,1 1,0 1,0 0))