PostgreSQL MySQL 兼容性之 - Gis类型

PostGIS的GIS功能相比MySQL强大太多，本文仅仅列举了MySQL支持的部分。
欲了解PostGIS请参考:
http://postgis.net/docs/manual-2.2/reference.html
PostGIS有几百个操作函数, 对GIS支持强大。

POINT

MySQL

  POINT
    PointFromText('POINT(10 10)')
    PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))

PostgreSQL

  # PostgreSQL
  point
    point( x , y )
  # PostGIS
  point

LINESTRING

MySQL

  LINESTRING

  CREATE TABLE gis_line  (g LINESTRING);
  SHOW FIELDS FROM gis_line;
  INSERT INTO gis_line VALUES
    (LineFromText('LINESTRING(0 0,0 10,10 0)')),
    (LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
    (LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));

  GLENGTH
    Length of a LineString value
  ST_ENDPOINT
    Returns the endpoint of a LineString
  ST_NUMPOINTS
    Returns the number of Point objects in a LineString
  ST_POINTN
    Returns the N-th Point in the LineString
  ST_STARTPOINT
    Returns the start point of a LineString

PostgreSQL

  # PostGIS
  LINESTRING

  ST_Length — Returns the 2D length of the geometry if it is a LineString or MultiLineString. geometry are in units of spatial reference and geography are in meters (default spheroid)
  ST_Length2D — Returns the 2-dimensional length of the geometry if it is a linestring or multi-linestring. This is an alias for ST_Length
  ST_3DLength — Returns the 3-dimensional or 2-dimensional length of the geometry if it is a linestring or multi-linestring.
  ST_LengthSpheroid — Calculates the 2D or 3D length of a linestring/multilinestring on an ellipsoid. This is useful if the coordinates of the geometry are in longitude/latitude and a length is desired without reprojection.
  ST_Length2D_Spheroid — Calculates the 2D length of a linestring/multilinestring on an ellipsoid. This is useful if the coordinates of the geometry are in longitude/latitude and a length is desired without reprojection.

  ST_EndPoint — Returns the last point of a LINESTRING or CIRCULARLINESTRING geometry as a POINT.

  ST_NumPoints — Return the number of points in an ST_LineString or ST_CircularString value.

  ST_PointN — Return the Nth point in the first linestring or circular linestring in the geometry. Return NULL if there is no linestring in the geometry.

  ST_StartPoint — Returns the first point of a LINESTRING geometry as a POINT.

  http://postgis.net/docs/manual-2.2/reference.html
  PostGIS有几百个操作函数, 对GIS支持强大。

POLYGON

MySQL

  Polygon properties

  ST_AREA
    Area of a Polygon
  ST_ExteriorRing
    Returns the exterior ring of a Polygon as a LineString
  ST_InteriorRingN
    Returns the N-th interior ring for a Polygon
  ST_NUMINTERIORRINGS
    Number of interior rings in a Polygon

PostgreSQL

  # PostGIS

  ST_Area — Returns the area of the surface if it is a Polygon or MultiPolygon. For geometry, a 2D Cartesian area is determined with units specified by the SRID. For geography, area is determined on a curved surface with units in square meters.
  ST_ExteriorRing — Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon. Will not work with MULTIPOLYGON
  ST_InteriorRingN — Return the Nth interior linestring ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range.
  ST_NumInteriorRings — Return the number of interior rings of the a polygon in the geometry. This will work with POLYGON and return NULL for a MULTIPOLYGON type or any other type

  ST_GeometryN — Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON, POLYHEDRALSURFACE Otherwise, return NULL.
  ST_IsEmpty — Returns true if this Geometry is an empty geometrycollection, polygon, point etc.
  ST_NRings — If the geometry is a polygon or multi-polygon returns the number of rings.
  ST_ForceRHR — Forces the orientation of the vertices in a polygon to follow the Right-Hand-Rule.
  ST_3DIntersects — Returns TRUE if the Geometries "spatially intersect" in 3d - only for points, linestrings, polygons, polyhedral surface (area). With SFCGAL backend enabled also supports TINS
  ST_Perimeter — Return the length measurement of the boundary of an ST_Surface or ST_MultiSurface geometry or geography. (Polygon, MultiPolygon). geometry measurement is in units of spatial reference and geography is in meters.
  ST_Perimeter2D — Returns the 2-dimensional perimeter of the geometry, if it is a polygon or multi-polygon. This is currently an alias for ST_Perimeter.
  ST_3DPerimeter — Returns the 3-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
  ST_CurveToLine — Converts a CIRCULARSTRING/CURVEPOLYGON to a LINESTRING/POLYGON
  ST_DumpRings — Returns a set of geometry_dump rows, representing the exterior and interior rings of a polygon.
  ST_LineToCurve — Converts a LINESTRING/POLYGON to a CIRCULARSTRING, CURVEPOLYGON
  ST_MinimumBoundingCircle — Returns the smallest circle polygon that can fully contain a geometry. Default uses 48 segments per quarter circle.
  ST_Polygonize — Aggregate. Creates a GeometryCollection containing possible polygons formed from the constituent linework of a set of geometries.
  ST_SimplifyPreserveTopology — Returns a "simplified" version of the given geometry using the Douglas-Peucker algorithm. Will avoid creating derived geometries (polygons in particular) that are invalid.
  ST_LocateAlong — Return a derived geometry collection value with elements that match the specified measure. Polygonal elements are not supported.
  ST_LocateBetween — Return a derived geometry collection value with elements that match the specified range of measures inclusively. Polygonal elements are not supported.

  http://postgis.net/docs/manual-2.2/reference.html
  PostGIS有几百个操作函数, 对GIS支持强大。

MultiPoint

MySQL

  MultiPoint(pt1,pt2,...)

PostgreSQL

  # PostGIS
  MultiPoint

MultiPolygon

MySQL

  MultiPolygon(poly1,poly2,...)
  

PostgreSQL

  # PostGIS
  MultiPolygon

ST_BUFFER

MySQL

  ST_BUFFER(g1,r),  BUFFER(g1,r)
  Returns a geometry that represents all points whose distance from geometry g1 is less than or equal to distance, or radius

PostgreSQL

  # PostGIS
  ST_Buffer — (T) Returns a geometry covering all points within a given distance from the input geometry.

ST_ConvexHull

MySQL

  ST_ConvexHull (g), ConvexHull(g)
  Given a geometry, returns a geometry that is the minimum convex geometry enclosing all geometries within the set. Returns NULL if the geometry value is NULL or an empty value.

PostgreSQL

  # PostGIS
  ST_ConvexHull — The convex hull of a geometry represents the minimum convex geometry that encloses all geometries within the set.

ST_INTERSECTION

MySQL

  ST_INTERSECTION(g1,g2)
  Returns a geometry that is the intersection, or shared portion, of geometry g1 and geometry g2.

PostgreSQL

  # PostGIS
  ST_Intersection — (T) Returns a geometry that represents the shared portion of geomA and geomB.
  ST_Difference — Returns a geometry that represents that part of geometry A that does not intersect with geometry B.

ST_PointOnSurface

MySQL

  ST_PointOnSurface (g), PointOnSurface(g)
  Given a geometry, returns a POINT guaranteed to intersect a surface.

PostgreSQL

  # PostGIS
  ST_PointOnSurface — Returns a POINT guaranteed to lie on the surface.

ST_SYMDIFFERENCE

MySQL

  ST_SYMDIFFERENCE(g1,g2)
  Returns a geometry that represents the portions of geometry g1 and geometry g2 that don't intersect.

PostgreSQL

  # PostGIS
  ST_Difference — Returns a geometry that represents that part of geometry A that does not intersect with geometry B
  ST_SymDifference — Returns a geometry that represents the portions of A and B that do not intersect. It is called a symmetric difference because ST_SymDifference(A,B) = ST_SymDifference(B,A).

ST_UNION

MySQL

  ST_UNION(g1,g2)
  Returns a geometry that is the union of the geometry g1 and geometry g2.

PostgreSQL

  # PostGIS
  ST_Union — Returns a geometry that represents the point set union of the Geometries.
  ST_UnaryUnion — Like ST_Union, but working at the geometry component level.

PostGIS reference

http://postgis.net/docs/manual-2.2/reference.html
PostGIS有几百个操作函数, 对GIS支持强大。