7

using

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

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

4
  • 1
    Could you show how you created the geometry? Just cut-n-paste the exact text.
    – BradHards
    Commented Dec 4, 2012 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
    Commented Dec 4, 2012 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
    Commented Dec 4, 2012 at 8:40
  • the column is "geom - geometry(MultiPolygon)"
    – Kourosh
    Commented Dec 4, 2012 at 8:45

2 Answers 2

7

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.

4
  • 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
    Commented Dec 4, 2012 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
    Commented Dec 4, 2012 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
    Commented Sep 17, 2015 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
    Commented Sep 22, 2015 at 10:50
5

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 and acknowledge you have read our privacy policy.

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