3

using

SELECT Find_SRID('public', 'myTable', 'myColumn');

I get zero as the result, what does it mean?

  • 1
    Could you show how you created the geometry? Just cut-n-paste the exact text. – BradHards Dec 4 '12 at 8:34
  • i'm not creating it, it's already in the DB, i just need to figure out the correct SRID of these geometries. thanks – Kourosh Dec 4 '12 at 8:36
  • 3
    My guess is you're choosing the wrong column, or the column wasn't built correctly. However you need to tell us more. Can you run "\d myTable" in the psql console? – BradHards Dec 4 '12 at 8:40
  • the column is "geom - geometry(MultiPolygon)" – Kourosh Dec 4 '12 at 8:45
5

From Postgis doc:

The syntax is find_srid(, , ) and the function returns the integer SRID of the specified column by searching through the GEOMETRY_COLUMNS table. If the geometry column has not been properly added with the AddGeometryColumns() function, this function will not work either.

So you shoud have a row in GEOMETRY_COLUMNS table for F_TABLE_SCHEMA 'public', F_TABLE_NAME 'mytable' and F_GEOMETRY_COLUMN 'mycolumn' where the SRID is 0. You can update this row with the correct SRID.

  • Juan, this means I need to remove the current geometry column and create a new one? is it possible to fix the current column? thanks – Kourosh Dec 4 '12 at 10:19
  • 1
    You don't need to remove the current geometry column. Just update the GEOMETRY_COLUMN entry with the actual SRID or if no entry in that table exists, insert a new row with your geometry column data (schema, table name, column name, srid, geometry type). It will help if you post the ouput of "\d myTable" in the psql console as @BradHards suggested. – juanluisrp Dec 4 '12 at 10:50
  • @juanluisrp, it looks like AddGeometryColumns() was not applied to some fields in my table, but everything works fine except Find_SRID(). What are the other benefits of applying AddGeometryColumns() ? – vak Sep 17 '15 at 12:29
  • 1
    @vak simply you don't need to manually do all the configuration that AddGeometryColumns() does. postgis.net/docs/PostGIS_FAQ.html#idp32809232 – juanluisrp Sep 22 '15 at 10:50
2

Complementing with 3 "as it's":

1. As John's answer of 2014,

SRID of 0 doesn't technically exist, it just means no SRID — ie, the default if you forget to set it.

Zero is a valid SRID, your PostGIS configurations will set for some default value, that in a fresh installation will be WGS84, srid 4326.

2. As the "see also browsing" of docs, there are a function to update, ST_SRID(), that do the same thing:

-- OLD postgis UPDATE t SET geom.STSrid=4326 WHERE to_update;
UPDATE t SET geom=ST_SetSRID(geom,4326) WHERE to_update;

There are a function specialized in updates, UpdateGeometrySRID(), and it must be used when your table declared something as geometry(MULTILINESTRING, 4326) instead generic geometry,

SELECT UpdateGeometrySRID('roads','geom',4326);

3. As showed by the guide, it is a shorter way to

ALTER TABLE roads
  ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326)
    USING ST_SetSRID(geom,4326);

(see more details at the guide)


NOTE

The OGC standard say nothing about "SRID zero" or "SRID default behaviour", but it is a good practice and a PostGIS project decision. The OGC standard is expressed and can be internally accessed by the spatial_ref_sys table,

 SELECT * FROM spatial_ref_sys where srid=0 or srid=4326;

this query shows only WGS84, not the srid=0. See more details about this standard table at Guide's "Using PostGIS / spatial_ref_sys".

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.