PolarDB:Geometry model

Overview

The Open Geospatial Consortium (OGC) developed the Simple Features Access standard (SFA) to provide a model for geospatial data. It defines the fundamental spatial type of Geometry and operations which manipulate and transform geometry values to perform spatial analysis tasks.

GanosBase Geometry is a spatio-temporal engine extension of PostgreSQL (PolarDB for PostgreSQL). GanosBase Geometry uses SFA developed by OGC. It supports the geometry and geography data types to describe geometric objects, such as points, lines, and planes on planes and surfaces.

Features

GanosBase Geometry supports multiple methods for constructing, accessing, editing, processing, aggregating, and exporting spatial objects. It also supports relational queries and linear reference settings for spatial objects.

GanosBase Geometry is compatible with multiple data formats for direct input, such as WKT, WKB, EKWT, EWKB, KML, GML, GeoHash, and Geojson. You can also use other tools to import data of other formats such as Esri Shapefiles.

Scenarios

The geometry data type can be used for storing and analyzing geometric objects:

• Map creation and visualization

  Map visualization displays the distribution, trend, and relationship of data in the geographic space. Map visualization marks data such as points, lines, and planes, and uses elements such as map projection, color, and symbol to display the distribution of data in geographic space. This helps you with the map creation and visualization. The geometry data type can be used to represent various features on a map, such as roads, rivers, and buildings.

• Location service

  You can perform spatial queries, aggregation, and analysis of location data to find the best location, assess risks, or calculate the distance between two points. The geometry data type can describe punctual geo-objects and perform spatial processing on punctual geo-objects. This can provide support for location services.

• Spatial relationship query

  Spatial relationship queries can be used in various scenarios, such as spatial data mining and urban planning. You can quickly find the spatial entities that meet specific conditions by executing spatial relationship queries. This can provide support for your decision-making process. The geometry data type allows you to identify the spatial relationship between multiple geometric objects. For example, you can identify whether two objects intersect or are tangent to each other.

• Spatial analysis and computing

  Spatial analysis and computing can be used in various measurement scenarios, such as calculating the area of a building and the distance between two cities. The geometry data type supports various types of spatial analysis and calculations, such as calculating the area, length, distance, and centroid.

Overview of the geometry model

Geometry models shapes in the 2-dimensional or 3-dimensional coordinate system. The size and location of shapes are specified by their coordinates. Each coordinate has an x-coordinate value and a y-coordinate value, and may contain z-coordinate and m-coordinate values. The z-coordinate value is often used to represent elevation. The m-coordinate contains a measure value, which may represent time or distance. If z-coordinate or m-coordinate values are present in a geometry value, they must be defined for each point in the geometry. If a geometry has z-coordinate or m-coordinate values, the coordinate dimension is 3D. If a geometry has both z-coordinate or m-coordinate values, the coordinate dimension is 4D.

Spatial reference system

The spatial reference system (SRS) defines how to associate a Geometry object to a specific location on the surface of the Earth.

GanosBase uses an integer as the SRID to reference the SRS definition. A Scene object is associated with SRS by using its own SRID value.

For more information, see Spatial reference.

Formats for external use

The SFA specification defines two formats for representing geometry values for external use:

• Well-Known Text (WKT) provides a standard textual representation of spatial data.

• Well-Known Binary (WKB) provides a portable, full-precision representation of spatial data as binary data.

Both WKT and WKB include information about the type of the object and the coordinates which define it, but do not include the associated SRIDs. Non-binary data is not supported.

GanosBase provides the EKWT and EKWB formats on top of the WKT and WKB formats to support 3DZ, 3DM, and 4D data and embedding SRID information.

The EKWT or EKWB format is a superset of the WKT or WKB format. Any valid file of the WKT or WKB format is a valid file of the EKWT or EKWB format.

Geometry data types

• Atomic types

  Model	Description	Example
  Point	A 0-dimensional geometry that represents a single location in coordinate space.	POINT (1 2) * POINT Z (1 2 3) POINT ZM (1 2 3 4)
  LineString	A 1-dimensional line formed by a contiguous sequence of line segments. Each line segment is defined by two points, with the end point of one segment forming the start point of the next segment. LineStrings may be self-intersect.	LINESTRING (1 2, 3 4, 5 6)
  Polygon	A 2-dimensional planar region, delimited by an exterior boundary (the shell) and zero or more interior boundaries (holes). Each boundary is a LinearRing.	POLYGON ((0 0 0,4 0 0,4 4 0,0 4 0,0 0 0),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 0))

• Parameterized types

  Model	Description	Example
  EllipticalString	An elliptical arc.	ELLIPTICALSTRING(-2 0,2 0,0 0,0,0,0,2,0.5)
  Bezier3Curve	A third-order Bezier curve determined by four points.	BEZIER3CURVE(1 1, 2 2, 3 2, 3 1)

• Collection types

  Model	Description	Example
  MultiPoint	A collection of Points.	MULTIPOINT ( (0 0), (1 2) )
  MultiLineString	A collection of LineStrings.	MULTILINESTRING ( (0 0,1 1,1 2), (2 3,3 2,5 4) )
  MultiPolygon	A collection of non-overlapping and non-adjacent Polygons. Polygons in the collection may touch only at a finite number of points.	MULTIPOLYGON (((1 5, 5 5, 5 1, 1 1, 1 5)), ((6 5, 9 1, 6 1, 6 5)))
  GeometryCollection	A collection of heterogeneous collection of geometries.	GEOMETRYCOLLECTION ( POINT(2 3), LINESTRING(2 3, 3 4))
  CompoundCurve	A compound line object that can contain lines, elliptical arcs, and Bezier curves. The end point of every component (except the last) must be coincident with the start point of the following component.	COMPOUNDCURVE((1 0,2 0),ELLIPTICALSTRING(2 0 ,4 0, 3 0 ,1,0,0,1,0.5)) COMPOUNDCURVE((1 2,2 0),BEZIER3CURVE(2 0,20 20 ,30 10,10 10))
  CurvePolygon	A compound surface object that contains curves. The objects in a CurvePolygon must be closed.	CURVEPOLYGON(ELLIPTICALSTRING(2 0,2 0,0 0,0,0,0,2,0.5),CIRCULARSTRING(-0.5 0,0.5 0,-0.5 0)) CURVEPOLYGON(BEZIER3CURVE(1 1, 2 2, 3 1, 1 1),CIRCULARSTRING(1.7 1.2,1.9 1.2,1.7 1.2))

Spatial data column view

In GanosBase, the geometry_columns view reads all geometry columns from the system catalog table of the database. This view follows the Simple Features Specification for SQL standards defined by OGC.

The spatial data column view contains the following columns:

You can execute the following statement to query all geometry data columns in the current database:

SELECT * FROM geometry_columns;

Index

Spatial indexes allow GanosBase to avoid global sequential scans of the database when processing large spatial datasets. Spatial indexes organize data in a tree structure that can be quickly traversed to find a specific record.

GanosBase provides the following types of spatial index for spatial data:

Overview

This section describes how to use the GanosBase Geometry engine, including extension creation, table creation, data import, index creation, spatial analysis and measurement, and spatial relationship identification.

Syntax

• Create an extension.

  -- Create a geometry extension.
  CREATE extension ganos_geometry cascade;

  Note

  Create the extension in the public schema to avoid permission issues.

  CREATE extension ganos_geometry WITH schema public cascade;

• Create a geometry table.

  -- Method 1: Create a table that contains a geometry field.
  CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25), geom geometry(LINESTRING,3857) );
  
  -- Method 2: Create a regular table and then add a geometry field to the table.
  CREATE TABLE ROADS (ID int4, ROAD_NAME varchar(25));
  SELECT AddGeometryColumn( 'roads', 'geom', 3857, 'LINESTRING', 2);

• Add geometry constraints.

  ALTER TABLE ROADS ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(geom));

• Import geometry data.

  INSERT INTO roads (id, geom, road_name)
    VALUES (1,ST_GeomFromText('LINESTRING(191232 243118,191108 243242)',3857),'North Fifth-Ring Road');
  INSERT INTO roads (id, geom, road_name)
    VALUES (2,ST_GeomFromText('LINESTRING(189141 244158,189265 244817)',3857),'East Fifth-Ring Road');
  INSERT INTO roads (id, geom, road_name)
    VALUES (3,ST_GeomFromText('LINESTRING(192783 228138,192612 229814)',3857),'South Fifth-Ring Road');
  INSERT INTO roads (id, geom, road_name)
    VALUES (4,ST_GeomFromText('LINESTRING(189412 252431,189631 259122)',3857),'West Fifth-Ring Road');
  INSERT INTO roads (id, geom, road_name)
    VALUES (5,ST_GeomFromText('LINESTRING(190131 224148,190871 228134)',3857),'East Chang'an Avenue');
  INSERT INTO roads (id, geom, road_name)
    VALUES (6,ST_GeomFromText('LINESTRING(198231 263418,198213 268322)',3857),'West Chang'an Avenue');

• Query geometry object information.

  SELECT id, ST_AsText(geom) AS geom, road_name FROM roads;
  
  --------------------------------
   id | geom| road_name
  --------+-----------------------------------------+-----------
    1 | LINESTRING(191232 243118,191108 243242) | North Fifth-Ring Road
    2 | LINESTRING(189141 244158,189265 244817) | East Fifth-Ring Road
    3 | LINESTRING(192783 228138,192612 229814) | South Fifth-Ring Road
    4 | LINESTRING(189412 252431,189631 259122) | West Fifth-Ring Road
    5 | LINESTRING(190131 224148,190871 228134) | East Chang'an Avenue
    6 | LINESTRING(198231 263418,198213 268322) | West Chang'an Avenue
  (6 rows)

• Create an index.

  -- Create a GiST index.
  CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );
  CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);
  VACUUM ANALYZE [table_name] [(column_name)];
  
  -- Example:
  Create INDEX sp_geom_index ON ROADS USING GIST(geom);
  VACUUM ANALYZE ROADS (geom);
  
  -- Create a BRIN index.
  CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );
  CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_3d);
  CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_4d);
    -- Create a BRIN index with specified block size.
  CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]);

• Measure and analyze spatial data.

  --Create Table bc_roads:
  Column  | Type  | Description
  ------------+-------------------+-------------------
  gid | integer   | Unique ID
  name| character varying | Road Name
  the_geom| geometry  | Location Geometry (Linestring)
  
  --Create table bc_municipality:
  Column | Type  | Description
  -----------+-------------------+-------------------
  gid| integer   | Unique ID
  code   | integer   | Unique ID
  name   | character varying | City / Town Name
  the_geom   | geometry  | Location Geometry (Polygon)
  
  -- Calculate the length.
  SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;
  
  km_roads
  ------------------
  70842.1243039643
  (1 row)
  
  -- Calculate the area.
  SELECT ST_Area(the_geom)/10000 AS hectares FROM bc_municipality WHERE name = 'PRINCE GEORGE';
  
  hectares
  ------------------
  32657.9103824927
  (1 row)

• Spatial relationship identification.

  --ST_Contains
  SELECT  m.name, sum(ST_Length(r.the_geom))/1000 as roads_km
  FROM
    bc_roads AS r, bc_municipality AS m
  WHERE
    ST_Contains(m.the_geom,r.the_geom)
  GROUP BY m.name
  ORDER BY roads_km;
  
  name| roads_km
  ----------------------------+------------------
  SURREY  | 1539.47553551242
  VANCOUVER   | 1450.33093486576
  LANGLEY DISTRICT| 833.793392535662
  BURNABY | 773.769091404338
  PRINCE GEORGE   | 694.37554369147
  ...
  
  --ST_Covers,a circle covering a circle
  SELECT ST_Covers(smallc,smallc) As smallinsmall,
      ST_Covers(smallc, bigc) As smallcoversbig,
      ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
      ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
  FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
      ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
      --Result
   smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
  --------------+----------------+-------------------+---------------------
   t| f  | t | f
  (1 row)
  
  --ST_Disjoint
  SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
   st_disjoint
  ---------------
   t
  (1 row)
  SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
   st_disjoint
  ---------------
   f
  (1 row)
  
  --ST_Overlaps
  SELECT ST_Overlaps(a,b) As a_overlap_b,
          ST_Crosses(a,b) As a_crosses_b,
          ST_Intersects(a, b) As a_intersects_b, ST_Contains(b,a) As b_contains_a
  FROM (SELECT ST_GeomFromText('POINT(1 0.5)') As a, ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)')  As b)
      As foo
  
  a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
  ------------+-------------+----------------+--------------
  f   | f   | t  | t
  
  --ST_Relate
  SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
  st_relate
  -----------
  t
  
  --ST_Touches
  SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(1 1)'::geometry);
   st_touches
  ------------
   f
  (1 row)
  
  SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);
   st_touches
  ------------
   t
  (1 row)
  
  --ST_Within
  SELECT ST_Within(smallc,smallc) As smallinsmall,
      ST_Within(smallc, bigc) As smallinbig,
      ST_Within(bigc,smallc) As biginsmall,
      ST_Within(ST_Union(smallc, bigc), bigc) as unioninbig,
      ST_Within(bigc, ST_Union(smallc, bigc)) as biginunion,
      ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion
  FROM
  (
  SELECT ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) As smallc,
      ST_Buffer(ST_GeomFromText('POINT(50 50)'), 40) As bigc) As foo;
  --Result
   smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
  --------------+------------+------------+------------+------------+------------
   t| t  | f  | t  | t  | t
  (1 row)

• Access geometry objects.

   SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
   st_issimple
  -------------
   t
  (1 row)
  
   SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
   st_issimple
  -------------
   f
  (1 row)
  
  
  -- Query the largest city that has traffic circles in the terrain.
  SELECT gid, name, ST_Area(the_geom) AS area
  FROM bc_municipality
  WHERE ST_NRings(the_geom) > 1
  ORDER BY area DESC LIMIT 1;
  
  gid  | name | area
  -----+--------------+------------------
  12   | Anning        | 257374619.430216
  (1 row)

• Delete the extension (optional).

  -- Delete the geometry extension.
  Drop extension ganos_geometry cascade;

SQL reference

For more information, see Geometry SQL reference.