8

I was under the impression that the geography type only uses the lat,long based coordinates EPSG:4326. And I didn't remember having to specify a CRS for a geography column.

However, from this boundless example:

SELECT * FROM geography_columns;

          f_table_name    | f_geography_column | srid |   type
--------------------------+--------------------+------+----------
 nyc_subway_stations_geog | geog               |    0 | Geometry
 airports                 | geog               | 4326 | Point

It can have other SRIDs such as 0 as shown above.

Does geography type use 4326 only?

  • 1
    A SRID of 0 is also assigned when you don't specifically assign one. I recall a debate among the Postgis developers as to whether a missing SRID should be reported as -1 or 0. I was in favour of -1, 0 was chosen. If you look at the definition of the geometry_columns view you will discover a function, typemod_get_srid, which appears to be the culprit, see ` #define TYPMOD_SET_SRID(typmod, srid) ((typmod) = (((typmod) & 0xE00000FF) | ((srid & 0x001FFFFF)<<8))) ` – John Powell Dec 15 '17 at 14:57
7

Based on PostGIS website, 4326 used to be the only supported CRS for geography types, but not any more:

Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326). For PostGIS 2.2 and above, any long/lat based spatial reference system defined in the spatial_ref_sys table can be used. You can even add your own custom spheroidal spatial refence system as described in geography type is not limited to earth.

5

While what laser says is true, I believe the reason for the SRID appearing as 0 is due to a decision made by Postgis developers that an undefined SRID should be reported as 0 -- there was a debate, in which -1 was also suggested. (I know this because I took part in said debate on IRC, and favoured -1, as it think it is more obviously undefined than 0).

If you look at the definition of the geography_columns view,

\df+ geography_columns

SELECT current_database() AS f_table_catalog,
  n.nspname AS f_table_schema,
  c.relname AS f_table_name,
  a.attname AS f_geography_column,
  postgis_typmod_dims(a.atttypmod) AS coord_dimension,
  postgis_typmod_srid(a.atttypmod) AS srid,
  postgis_typmod_type(a.atttypmod) AS type
FROM pg_class c,
  pg_attribute a,
  pg_type t,
  pg_namespace n
WHERE t.typname = 'geography'::name AND a.attisdropped = false AND a.atttypid = t.oid AND a.attrelid = c.oid AND c.relnamespace = n.oid AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege(c.oid, 'SELECT'::text);

you will see a refence to a function postgis_typmod_srid. Following that rabbit hole, you will find the function defined here as:

#define TYPMOD_SET_SRID(typmod, srid) ((typmod) = (((typmod) & 0xE00000FF) | ((srid & 0x001FFFFF)<<8))).

It is easy enough to confirm this by creating a geography column and then looking at geography_columns. Given that you could also create a geometry column with any custom spheroid, it seems a bit pointless to allow this functionality in the geography type, given the prevalence of WGS84.

`

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.