PostGIS SRID不存在的问题处理

标签

PostgreSQL , PostGIS , SRID , spatial_ref_sys

背景

GIS数据有一定的标准来表示它的数据，

The OpenGIS "Simple Features Specification for SQL" defines standard GIS object types,
the functions required to manipulate them, and a set of meta-data tables.
In order to ensure that meta-data remain consistent,
operations such as creating and removing a spatial column are carried out through special procedures defined by OpenGIS.

There are two OpenGIS meta-data tables:
SPATIAL_REF_SYS and GEOMETRY_COLUMNS.
The SPATIAL_REF_SYS table holds the numeric IDs and textual descriptions of coordinate systems used in the spatial database.

http://www.sharpgis.net/post/2007/05/05/Spatial-references2c-coordinate-systems2c-projections2c-datums2c-ellipsoids-e28093-confusing

当你使用PostGIS时，如果报这样的错误，说明SRID的信息没有写入spatial_ref_sys表，PostgreSQL无法找到合适的GIS数据表达方法。

ERROR: invalid SRID: 5514 not found in spatial_ref_sys

http://gis.stackexchange.com/questions/187770/change-srid-of-geometry-column

stackoverflow中也有类似的提问

问题

I have in my table column geom with geometry, data type of this column is geometry(Point,102067).
I want to change SRID from 102067 to 5514, but if I use this command  

select UpdateGeometrySRID('my_schema', 'table', 'geom', 5514) ;  

docs say: ERROR: invalid SRID: 5514 not found in spatial_ref_sys CONTEXT:
SQL statement "SELECT UpdateGeometrySRID('',$1,$2,$3,$4)"  

I found, that EPSG:5514 = EPSG:102067 (both is S-JTSK_Krovak_East_North),
but in second table I have EPSG:5514, and for example comand ST_Contains says: Operation on mixed SRID geometries.

回答

The error happened because EPSG:5514 is no added in spatal_ref_sys table. So you can add it using the following query.  

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 5514, 'EPSG', 5514, '+proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=bessel +towgs84=589,76,480,0,0,0,0 +units=m +no_defs ', 'PROJCS["S-JTSK / Krovak East North",GEOGCS["S-JTSK",DATUM["System_Jednotne_Trigonometricke_Site_Katastralni",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[589,76,480,0,0,0,0],AUTHORITY["EPSG","6156"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4156"]],PROJECTION["Krovak"],PARAMETER["latitude_of_center",49.5],PARAMETER["longitude_of_center",24.83333333333333],PARAMETER["azimuth",30.28813972222222],PARAMETER["pseudo_standard_parallel_1",78.5],PARAMETER["scale_factor",0.9999],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","5514"]]');  

Source: https://epsg.io/5514  

Click on the side menu under PostGIS to see the insert query.  

Check if SRID-5514 is inserted using:  

SELECT * FROM spatial_ref_sys WHERE srid = 5514;
Once you have inserted it, you can then change the SRID of your geometry column using the code:  

SELECT UpdateGeometrySRID('your_schema', 'table', 'geom', 5514);
You can then check the change using:  

SELECT ST_SRID(geom) FROM table LIMIT 1;

解决方法也很简单，插入SRS数据即可，找到postgis对应的版本SQL。

https://raw.githubusercontent.com/greenplum-db/postgis/4fefce57cbd28e006db60070434611228294bef3/spatial_ref_sys.sql

参考

http://spatialreference.org/ref/epsg/4326/

http://gis.stackexchange.com/questions/187770/change-srid-of-geometry-column