1

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.

  • What is the version of your Postgis ? You probably want to alter the projection ? Did you see ST_Transform ? You can use ST_tranform in you overlap query for example "... where ST_Overlaps(a.geom,st_tranform(b.geom,5514)).." – WKT Apr 2 '16 at 13:28
  • What do you get by running SQL query select * from spatial_ref_sys where srid=5514;? If result is empty your PostGIS does not support EPSG:5514. That projection is nasty for proj4 (projection library that PostGIS is using), read github.com/OSGeo/proj.4/issues/233. – user30184 Apr 2 '16 at 13:31
  • result is not empty, i get this: 5514;"EPSG";5514;"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 (...)";"+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 " – hanznv Apr 2 '16 at 13:52
  • If you did what was suggested in the answer you have added that line to the table and result is no longer empty. – user30184 Apr 2 '16 at 14:03
3

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;
  • Thank you for help. :) Please..One more thing.. For one schema I have one spatial_ref_sys? Or one spatial_ref_sys is for all schemas in database? – hanznv Apr 2 '16 at 13:33
  • You are most welcome. Yes, that is normal. Do you want more spatial_ref_sys tables? Why? – wondim Apr 2 '16 at 13:37
  • now in my schema i have two tables: TABLE and SPATIAL_REF_SYS with SRID:5514, but it still says: ERROR: invalid SRID: 5514 not found in spatial_ref_sys CONTEXT: SQL statement "SELECT UpdateGeometrySRID('',$1,$2,$3,$4)" – hanznv Apr 2 '16 at 13:50
  • It is working on my side. Check if you have added srid 5514 using the added query on the answer, which is SELECT * FROM spatial_ref_sys WHERE srid = 5514; – wondim Apr 2 '16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.