Earth is neither flat nor a perfect sphere but is a non-standard ellipsoid. Therefore, there is no perfect formula for rapidly and accurately calculating the distance between two points at different longitudinal and latitudinal locations. The coordinate system emerges in response.
An SRID indicates coordinates of data. For example:
Supported SRIDs can be queried from PostGIS, such as query coordinates related to Beijing and China.
postgres=# select * from spatial_ref_sys where srtext ~* 'beijing'; postgres=# select * from spatial_ref_sys where srtext ~* 'china';
Data can be converted between different coordinate systems. GIS calculations must be performed in the same coordinate system.
SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450, 743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)) As wgs_geom; wgs_geom --------------------------- POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009, -71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.177684 8522251 42.3902896512902)); (1 row) --3D Circular String example SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=2249;CIRCULARSTRING(743238 2967416 1,743238 2967450 2,743265 2967450 3,743265.625 2967416 3,743238 2967416 4)'),4326)); st_asewkt -------------------------------------------------------------------------------------- SRID=4326;CIRCULARSTRING(-71.1776848522251 42.3902896512902 1,-71.1776843766326 42.3903829478009 2, -71.1775844305465 42.3903826677917 3, -71.1775825927231 42.3902893647987 3,-71.1776848522251 42.3902896512902 4)
SRID stands for Spatial Reference Identifier.
Each spatial instance has an SRID. An SRID corresponds to a spatial reference system based on a specific ellipsoid, and it can be used for either flat-earth mapping or round-earth mapping. A spatial column can contain objects with different SRIDs . However, only space instances with the same SRID are applicable to the data operations using spatial methods of SQL Server. The result of any spatial method derived from two spatial instances is valid only when these two instances have the same SRID. The SRID is based on the same unit of measurement, data, and project for determining the coordinates of the instances. The most common unit of measurement of SRIDs is meters or square meters.
For two spatial instances with different SRIDs, NULL is returned when a method of the geometry or geography data type is used. For example, for the following predicate term to return a non-NULL result, the two geometry instances (geometry 1 and geometry 2) must have the same SRID.
geometry1.STIntersects(geometry2) = 1
Note: The spatial reference identifier system is defined by the European Petroleum Survey Group (EPSG) standard. It provides a collection of standards developed for cartography, surveying, and geodetic data storage. This standard is owned by the Surveying and Positioning Committee of the International Association of Oil and Gas Producers (IOGP).
The SRID of a geometry instance is 0 by default.
Also, in SQL Server, the SRID of a geometry instance is 0 by default. With geometry spatial data, the specific SRID of the spatial instance is not required for calculation. Therefore, instances can reside in undefined planar space. If undefined planar space needs to be specified for calculation using methods of the geometry data type, the SQL Server database engine uses SRID 0.
Geography instances must use supported SRIDs.
SQL Server supports EPSG standards-compliant SRIDs. The calculation needs to be performed by using SQL Server-supported SRIDs of geography instances or the method needs to be used with the geography spatial data. The SRID must match one of the SRIDs displayed in the sys.spatial_reference_systems catalog view.
As described above, when spatial data is calculated by using the geography data type, the result varies according to the ellipsoid used in data creation because each ellipsoid is assigned with a specific SRID.
When methods are used on geography instances, SQL Server uses the default SRID 4326, which maps to the WGS 84 spatial reference system. If you want to use a spatial reference system other than WGS 84 or SRID 4326, you need to determine the specific SRID of the regional spatial data.
EPSG stands for European Petroleum Survey Group. It was established in 1986 and then reorganized and renamed as the International Association of Oil & Gas Producers (IOGP) in 2005. It is responsible for maintaining and publishing Dataset parameters of coordinate reference systems and coordinate conversion descriptions. The Dataset has been widely accepted, used, and is distributed through a web publishing platform. The Dataset also serves as a storage file in a Microsoft Access database and can be accessed by MySQL, Oracle, PostgreSQL, and other databases through SQL scripts.
At present, different combinations of existing ellipsoids and projection coordinate systems correspond to different IDs, which are called EPSG codes in EPSG. An EPSG code represents information such as a specific ellipsoid, unit, geographic coordinate system, or projected coordinate system.
SRID stands for a spatial reference identifier, which is a parameter in the OGC standard and is consistent with an EPSG code.
The spatial reference system (SRS) parameter is used to represent a coordinate system in WMS 1.1.1 and earlier versions, and the coordinate reference system (CRS) parameter is used to represent a coordinate system in WMS 1.3 and later versions.
As defined in Wikipedia, Spatial Reference Identifier (SRID) is a unique value used to unambiguously identify projected, unprojected, and local spatial coordinate system definitions. These coordinate systems form the heart of all GIS applications.
Virtually all major spatial vendors have created their SRID implementation or refer to an authority, such as the EPSG.
Note: As of 2005, the EPSG SRID values are now maintained by the International Association of Oil & Gas Producers (OGP) Surveying & Positioning Committee.
Take an OGC request as an example:
SRS=EPSG:4326 represents the World Geodetic System (also known as WGS1984)
WKT: text description of the spatial reference system.
Reference ellipsoids, reference planes, projection manners, units of coordinates, etc., all have their corresponding EPSG values, as listed in the following table.
Beijing geodetic coordinate system 1954, Gauss-Krüger projection (also known as Equiangular Transverse Elliptic Cylinder Projection)
The projected coordinate system is represented in WKT form as follows:
PROJCS["Gauss_Kruger", GEOGCS["GCS_Beijing_1954", DATUM["D_Beijing_1954", SPHEROID["Krasovsky_1940",6378245.000000,298.299997264589]] ] PEIMEM["Greenwich",0] UNIT["degree",0.0174532925199433] //地理单位：0.0174532925199433代表与米之间的转换 ], PROJECTION["Gauss_Kruger"], PARAMETER["False_Easting",13500000.000000], PARAMETER["False_Northing",0], PARAMETER["Central_Meridian",75.000000], PARAMETER["Scale_Factor",1.0], PARAMETER["Latitude_Of_Origin",0.0], UNIT["Meter",1.0]] ;
How Can We Combine PostgreSQL and MySQL to Make the Whole Greater than the Sum of the Parts? (Part 2)
Sorting GiST Indexes by Leveraging Nearest-neighbor Enhancement in PostgreSQL 9.1
digoal - December 18, 2020
digoal - February 16, 2021
digoal - December 23, 2020
digoal - January 19, 2021
digoal - January 25, 2021
digoal - December 21, 2020
Fully managed and less trouble database servicesLearn More
An online MPP warehousing service based on the Greenplum Database open source programLearn More
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
Leverage cloud-native database solutions dedicated for FinTech.Learn More
More Posts by digoal