Title: Theory / Unimportant / CS / Cloud / AWS / Database / Redshift / Spatial
Tags: automatedNewLineAfterCommaSpace
On every note of this deck, unless a note says otherwise:
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-functions.html

Name: Materialized views
Definition: A view, which is stored, and kept up-to-date when the relations used in the view definition change
Page: 123
Chapter: 4
Section: 2.3
Context: Database
Abbreviation: MV
Intuition: Cache of a view

Name: Spatial data
Definition: the position and shape of a geometry in a defined space (a spatial reference system).
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html
Context: Redshift

Name: Spatial reference system identifier
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html
Context: Redshift
Example: weather reports
Example2: airline route
Abbreviation: SRID
Example3: map directions,
Example4: store locations

Typ: GEOMETRY
Meaning: which contains spatial data and optionally the data's spatial reference system identifier
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html
Context: Redshift

Question: The subtypes of GEOMETRY
Answer: POINT
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html
Context: Redshift
Answer2: LINESTRING
Answer3: POLYGON
Answer4: MULTIPOINT
Answer5: MULTILINESTRING
Answer6: MULTIPOLYGON
Answer7: GEOMETRYCOLLECTION

Question: Representation of geometric data
Answer: GeoJSON
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html
Context: Redshift
Answer2: well-known text
Answer3: extended well-known text
Answer4: well-known binary
Answer5: extended well-known binary

Question: queries on geographical data
Answer: Distance between two points
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html
Context: Redshift
Answer2: Whether one area (polygon) contains another
Answer3: Whether one linestring intersects another linestring or polygon

Instruction: ST_IsValid()
Page: https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html
Context: Redshift
Returns: whether the argument is valid geographical data

Name: World Geodetic System
Definition: a standard for use in cartography, geodesy, and satellite navigation including GPS. This standard includes the definition of the coordinate system's fundamental and derived constants, the ellipsoidal (normal) Earth Gravitational Model (EGM), a description of the associated World Magnetic Model (WMM), and a current list of local datum transformations.[1]
Page: https://en.wikipedia.org/wiki/World_Geodetic_System
Context: Redshift
Abbreviation: WGS

Name: A polygonal cahin
Definition: a curve specified by a sequence of points [$]\displaystyle (A_{1},A_{2},\dots ,A_{n})[/$]￼ called its vertices. The curve itself consists of the line segments connecting the consecutive vertices.
Page: https://en.wikipedia.org/wiki/Polygonal_chain
Context: Redshift

Name: Open Geospatial Consortium
Definition: development and implementation of open standards for geospatial content and services, sensor web and Internet of Things, GIS data processing and data sharing.
Page: https://en.wikipedia.org/wiki/Open_Geospatial_Consortium
Abbreviation: OGC
Typ: an international voluntary consensus standards organization

Name: Simple features (access)
Definition: a set of standards that specify a common storage and access model of geographic feature made of mostly two-dimensional geometries (point, line, polygon, multi-point, multi-line, etc.) used by geographic information systems
Page: https://en.wikipedia.org/wiki/Simple_Features

Name: A feature
Definition: a phenomenon that exists at a location in the space and scale of relevance to geography; that is, at or near the surface of the Earth, at a moderate to global scale.
Page: https://en.wikipedia.org/wiki/Geographical_feature
Context: Geography
Example: Almost all geographic information, such as that represented in maps, geographic information systems, remote sensing imagery, statistics, and other forms of geographic discourse, consists of descriptions of geographic features, including their inherent nature, their spatial form and location, and their characteristics or properties.

Typ: LineString
Meaning: Represents a polygonal chain
Page: https://en.wikipedia.org/wiki/Geographical_feature
Context: Geography
Implementation: Sequence of coordinate of the vertices

Typ: Polygon
Meaning: Represents a polygon chain
Page: https://en.wikipedia.org/wiki/Geographical_feature
Context: Geography
Implementation: sequence of coordinate of vertices.

Typ: MultiPoint
Meaning: Represents a set of point
Page: https://en.wikipedia.org/wiki/Geographical_feature
Context: Geography
Implementation: Sequence of coordinate of the points

Typ: GeometryCollection
Meaning: Represents a set of geometry element
Page: https://en.wikipedia.org/wiki/Geographical_feature
Context: Geography
Implementation: A seuqence of representation of geometry elements

Typ: MultiPolygon
Meaning: Represents a set of polygons
Page: https://en.wikipedia.org/wiki/Geographical_feature
Context: Geography
Implementation: A sequence of representation of polygons

Name: Well-known binary
Definition: A compact binary representation of geographical features
Page: https://en.wikipedia.org/wiki/Geographical_feature
Context: Geography
Abbreviation: WKB

Name: Shapefile
Definition: a geospatial vector data format for geographic information system (GIS) software
Page: https://en.wikipedia.org/wiki/Shapefile
Context: Geography

Question: What should be considered when importing a shapefile?
Answer: The geometry may have to be simplified to fit the size limit, this require to set precision
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-copy-shapefile.html
Context: RedShift Spatial

Name: The bounding box of G
Definition: the cross product (across dimensions) of the extents of the coordinates of all points in the geometry or geography
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Variables: G a geometry
Context: RedShift Spatial
Example: a rectangle that completely includes all points in the geometry
Applied to2: 2D geometry

Instruction: SupportsBBox(geom)
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Language: sql
Context: RedShift Spatial
Returns: Whether geom has a bounding box

Instruction: AddBBox(geom)
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Language: sql
Context: RedShift Spatial
Returns: The geometry geom with a bounding box

Instruction: DropBBox(geom)
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Language: sql
Context: RedShift Spatial
Returns: The geometry geom without its bounding box

Name: A valid point
Definition: An empty point
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: All point coordinates are finite floating point numbers.
Conjdef: Either
Name2: A simple point

Name: A valid linestring
Definition: The linestring is empty; that is, it contains no points.
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: All points in a nonempty linestring have coordinates that are finite floating point numbers.
Conjdef: Either
Definition3: The linestring, if not empty, must be one-dimensional; that is, it can't degenerate to a point.

Name: A valid ring
Definition: All points of the ring have coordinates that are finite floating point numbers.
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: The ring is closed; that is, its first point and its last point coincide.
Conjdef: all of
Definition3: The ring doesn't have any self-intersections.
Definition4: The ring is two-dimensional.

Name: A valid non-empty polygon
Definition: all rings are valid
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: The rings of the polygon have consistent orientations. That is, if you traverse any ring, the interior of the polygon is either to your right or to your left. This means that if a polygon's exterior ring is oriented clockwise or counterclockwise, all the polygon's interior rings must have the same counterclockwise or clockwise orientation.
Conjdef: all of
Definition3: All interior rings must be within the exterior ring of the polygon.
Definition4: Interior rings can't be nested; that is, an interior ring can't be within another interior ring.
Definition5: Interior and exterior rings can only intersect at a finite number of points.
Definition6: The interior of the polygon must be simply connected.

Name: A valid polygon
Definition: Either empty or a valid non-empty polygon
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial

Name: A valid multipoint
Definition: an empty multipoint
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: all points are valid according to the point validity definition.
Conjdef: either

Name: A valid multilinestring
Definition: an empty multilinestring
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: All linestrings in a nonempty multilinestring are valid according to the linestring validity definition.
Conjdef: either

Name: A valid non-empty multilipolygon
Definition: All polygons in the multipolygon are valid.
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: No two polygons in the multipolygon can intersect at an infinite number of points. In particular, this implies that the interior of any two polygons can't intersect and that they can only touch at a finite number of points.
Conjdef: All of

Name: A valid multilipolygon
Definition: Either an empty multipolygon or a valid non-empty multipolygon
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial

Name: A valid geometry collection
Definition: The geometry collection is empty; that is, it doesn't contain any geometries.
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: All geometries in a nonempty geometry collection are valid.
Conjdef: Either

Name: A simple linestring
Definition: no intersection appart possibly both ends of the multistring
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: no duplicate consecutive points
Conjdef: all of
Typ: A linestring

Name: A simple polygon
Definition: A valid polygon without duplicate consecutive point
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial

Name: A simple multipoint
Definition: A valid multipoint with distinct points
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial

Name: A simple multilinestring
Definition: All its linestrings are simple.
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial
Definition2: Any two linestrings of the multilinestring don't intersect, except at points that are boundary points of the two linestrings.
Conjdef: all of
Typ: A valid multilinestring

Name: A simple multilipolygon
Definition: A valid multipolygon that does not contain any duplicate consecutive points
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial

Name: A simple geometry collection
Definition: A geometry collection whose all elements are simple
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-terminology.html
Context: RedShift Spatial

Question: Maximal size of a geometry data
Answer: 1,048,447 bytes
Page: https://docs.aws.amazon.com/redshift/latest/dg/spatial-limitations.html
Context: RedShift Spatial

Instruction: GeometryType(geom)
Context: RedShift Spatial
Returns: The type of the underlying geometric point. Null on null

Instruction: ST_AddPoint(
  linestring,
  point
)
Context: RedShift Spatial
Returns: The same linestring, with point at the end

Instruction: ST_AddPoint(
  linestring,
  point,
  i
)
Context: RedShift Spatial
Returns: The same linestring, with point inserted at index i (  no elements are deleted, they are moved)

Instruction: ST_Angle(
  A,
  B,
  C
)
Context: RedShift Spatial
Returns: The angle A B C in radian

Instruction: ST_Angle(
  A,
  B,
  C,
  D
)
Context: RedShift Spatial
Returns: The angle between AB and CD in radian

Instruction: ST_Area(geometry_collection)
Context: RedShift Spatial
Returns: the sum of the area of the elements in the geometry collection

Instruction: ST_Area(polygon)
Context: RedShift Spatial
Returns: the area of the polygon

Instruction: ST_AsBinary(geom)
Context: RedShift Spatial
Returns: The WKB representation of geom

Instruction: ST_AsEWKB(geom)
Context: RedShift Spatial
Returns: The EWKB representation of geom

Instruction: ST_AsEWKT(geom)
Context: RedShift Spatial
Returns: The EWKT representation of geom

Instruction: ST_AsGeoJSON(geom)
Context: RedShift Spatial
Returns: The GeoJSON representation of geom

Instruction: ST_AsText(geom)
Context: RedShift Spatial
Returns: The WKT representation of geom

Instruction: ST_Azimut(
  A,
  B
)
Context: RedShift Spatial
Returns: The north-based cartesian azimuth of A and B

Instruction: ST_Boundary(geom)
Context: RedShift Spatial
Returns: ? what is a boundary?

Instruction: ST_Collect(
  A,
  B
)
Context: RedShift Spatial
Returns: The multipoint/multilinestring/multipolygon {A, B}
Variables: A,
  B points/linestring/polygon

Instruction: ST_Collect(
  A,
  B
)
Context: RedShift Spatial
Returns: The geometry collection {A, B}
Variables: A,
  B of different type

Instruction: ST_Collect(agg)
Context: RedShift Spatial
Returns: The multi point/linestring/polygon of content of agg
Variables: agg an aggretation of point/linestring/polygon

Instruction: ST_Collect(agg)
Context: RedShift Spatial
Returns: The geometry collection of content of agg
Variables: agg an aggretation of geometry of different type

Instruction: ST_Contains(A ,B)
ST_Withing(B, A)
Context: RedShift Spatial
Returns: Whether all points of B belongs to A and their interiors have nonempty intersection
Name: A contains B

Instruction: ST_ContainsProperly(A ,B)
Context: RedShift Spatial
Returns: Whether all points of B are interior points of A

Instruction: ST_ConvexHull(geom)
Context: RedShift Spatial
Returns: A geometry that represents the convex hull of the nonempty points contained in the input geometry.

Instruction: ST_CoveredBy(A, B),
ST_Covers(B, A)
Context: RedShift Spatial
Returns: Whether every point in A is a point in B

Instruction: ST_Crosses(
  A,
  B
)
Context: RedShift Spatial
Returns: Whether A and B crosses

Instruction: ST_Dimension(geom)
Context: RedShift Spatial
Returns: 0 on (multi)point, 1 on (multi)linestring, 2 on (multi)polygon, max on geometrycollection

Instruction: ST_Disjoint(
  geom1,
  geom2
)
Context: RedShift Spatial
Returns: whether geom1 and geom2 are disjoint

Instruction: ST_Distance(
  geom1,
  geom2
)
Context: RedShift Spatial
Returns: Euclidean distance between geom1 and geom2

Instruction: ST_DistanceSphere(
  geom1,
  geom2,
  r
)
Context: RedShift Spatial
Returns: The distance between geom1 and geom2 assuming they are on a sphere of radius r

Instruction: ST_DistanceSphere(
  geom1,
  geom2
)
Context: RedShift Spatial
Returns: The distance between geom1 and geom2 assuming they are on a sphere of radius the earth

Instruction: ST_DWithing(
  geom1,
  geom2,
  threshold
)
Context: RedShift Spatial
Returns: Whether the distance between geom1 and geom2 is at most threshold

Instruction: ST_EndPoint(linestring)
Context: RedShift Spatial
Returns: The last point of linestring

Instruction: ST_Envelope(geom)
Context: RedShift Spatial
Returns: the minimum bounding box of the input geometry,

Instruction: ST_Equals(
  geom,
  geom2
)
Context: RedShift Spatial
Returns: they have equal point sets and their interiors have a nonempty intersection.

Instruction: ST_ExterionRing(geom)
Context: RedShift Spatial
Returns: a closed linestring that represents the exterior ring of an input polygon.

Instruction: ST_GeometryN(
  geom,
  i
)
Context: RedShift Spatial
Returns: The i element of the set geom. A non-set type being considered as a set of size 1
Assuming: 1-based index

Instruction: ST_GeomFromXXXX(input)
Context: RedShift Spatial
Returns: Transform an input encoded in format XXX as a geometry

Instruction: ST_InteriorRingN(
  geom,
  i
)
Context: RedShift Spatial
Returns: a closed linestring corresponding to the interior ring of an input polygon at the index position.

Instruction: ST_Intersects(
  geom1,
  geom2
)
Context: RedShift Spatial
Returns: Whether the two input geometries have at least one point in common

Instruction: ST_isPolygonCCW(polygon)
Context: RedShift Spatial
Returns: Whether the all contained polygons are counterclockwise

Instruction: ST_isPolygonCW(polygon)
Context: RedShift Spatial
Returns: Whether the all contained polygons are clockwise

Instruction: ST_isclosed(geom)
Context: RedShift Spatial
Returns: Whether all contained multiline have same start and endpoint
and
whether for each contained polygon, all of their rings have same start and end point

Instruction: ST_IsCollection(geom)
Context: RedShift Spatial
Returns: Whether  the input geometry is one of the following subtypes: GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, or MULTIPOLYGON.

Instruction: ST_IsEmpty(geom)
Context: RedShift Spatial
Returns: Whether geom is empty. I.e. does not contain a non-empty point

Instruction: ST_IsSimple(geom)
Context: RedShift Spatial
Returns: Whether geom is simple

Instruction: ST_IsValid(geom)
Context: RedShift Spatial
Returns: Whether geom is valid

Instruction: ST_Length(
  geom
),
  ST_Length2D(
  geom
)
Context: RedShift Spatial
Returns: the Cartesian length of an input linear geometry

Instruction: ST_LengthSphere(geom)
Context: RedShift Spatial
Returns: the length of a linear geometry in meters
Assuming: points are given with latitude and longitude in degree

Instruction: ST_LineFromMultiPoint(multipoint)
Context: RedShift Spatial
Returns: a linestring with the points in the order in which they appear in the multipoint

Instruction: ST_LineInterpolatePoint(
  linestring,
  fraction
)
Context: RedShift Spatial
Returns: A point on linestring at fraction on the line from the start point

Instruction: ST_MakeEnvelope(
  xmin,
  ymin,
  xmax,
  ymax
)
Context: RedShift Spatial
Returns: ?

Instruction: ST_MakeEnvelope(
  xmin,
  ymin,
  xmax,
  ymax
)
Context: RedShift Spatial
Returns: A cartesian box with bottom-left corner xmin, ymin, and upper-right corner xmax, ymax