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
pointLINESTRING
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 LineStringPostgreSQL
# 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 PolygonPostgreSQL
# 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
MultiPointMultiPolygon
MySQL
MultiPolygon(poly1,poly2,...)
PostgreSQL
# PostGIS
MultiPolygonST_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 radiusPostgreSQL
# 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支持强大。