2

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.

4
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 14:03

1 Answer 1

6

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;
4
  • 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, 2016 at 13:33
  • You are most welcome. Yes, that is normal. Do you want more spatial_ref_sys tables? Why?
    – wondim
    Apr 2, 2016 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, 2016 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, 2016 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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