PostgreSQL MySQL 兼容性之 - Gis类型

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: PostGIS的GIS功能相比MySQL强大太多,本文仅仅列举了MySQL支持的部分。欲了解PostGIS请参考:http://postgis.net/docs/manual-2.2/reference.htmlPostGIS有几百个操作函数, 对GIS支持强大。 POINT MySQL

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支持强大。
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
3月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
2天前
|
关系型数据库 MySQL 数据库
市场领先者MySQL的挑战者:PostgreSQL的崛起
PostgreSQL(简称PG)是世界上最先进的开源对象关系型数据库,起源于1986年的加州大学伯克利分校POSTGRES项目。它以其丰富的功能、强大的扩展性和数据完整性著称,支持复杂数据类型、MVCC、全文检索和地理空间数据处理等特性。尽管市场份额略低于MySQL,但PG在全球范围内广泛应用,受到Google、AWS、Microsoft等知名公司支持。常用的客户端工具包括PgAdmin、Navicat和DBeaver。
21 4
|
9天前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
37 6
|
3月前
|
关系型数据库 Serverless 定位技术
PostgreSQL如何使用GIS函数计算两个点连线的中间点?
PostgreSQL如何使用GIS函数计算两个点连线的中间点?
255 62
|
1月前
|
分布式计算 关系型数据库 MySQL
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型 图像处理 光通信 分布式计算 算法语言 信息技术 计算机应用
55 8
|
3月前
|
关系型数据库 Serverless 定位技术
PostgreSQL GIS函数判断两条线有交点的函数是什么?
PostgreSQL GIS函数判断两条线有交点的函数是什么?
297 60
|
1月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
165 4
|
1月前
|
关系型数据库 MySQL PostgreSQL
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
54 1
|
2月前
|
关系型数据库 MySQL
用dbeaver创建一个enum类型,并讲述一部分,mysql的enum类型的知识
这篇文章介绍了如何在DBeaver中创建MySQL表的枚举(ENUM)字段,并探讨了MySQL中ENUM类型的一些行为特点,例如ENUM值的默认排序和在插入重复值时的表现。
61 1
用dbeaver创建一个enum类型,并讲述一部分,mysql的enum类型的知识

相关产品

  • 云数据库 RDS MySQL 版
  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版