SRID 4326 and Geometry
As a side note to the excellent, comprehensive, and current answer by MikeT. Many people seem to ask this question because they want to set the SRID on a POINT column.
CREATE TABLE foo ( geom geometry(Point,4326) );
But when they do they run into problems with what seems like the best method of creating a point, but alas they run into trouble.
INSERT INTO foo (geom) VALUES ( ST_MakePoint(1,2) );
ERROR: Geometry SRID (0) does not match column SRID (4326);
From there, they reason they have two options
- Set the SRID manually,
ST_SetSRID( ST_MakePoint(1,2) ) which is the right-most way but crufty, or
- Construct from text using
ST_GeomFromText, this is logically slower and doesn't need benchmarks: PostgreSQL has to parse the constructor's arguments from the text. It's also extremely ugly itself.
Alas, there is another way.
The default SRID for
geography is 4326. If you're new, I would suggest using
geography instead of
geometry. In fact, generally if you don't know the difference you likely want
geography. You can switch the columns pretty easily.
ALTER TABLE foo ADD COLUMN geog geography(point,4326);
UPDATE foo SET geog = geom::geography;
ALTER TABLE foo DROP COLUMN geom;
Now insertion is easier because the type is already default-associated with SRID 4326. Now you can explicitly cast to
geography, or just let the implicit cast work
ST_MakePoint(x,y) -- implicit cast and srid
ST_MakePoint(x,y)::geography -- explicit cast, implicit srid
ST_SetSRID( ST_MakePoint(3,4), 4326 ) -- explicit cast and srid
Which looks like this, (they all insert hte same thing)
INSERT INTO foo (geog) VALUES
-- implicit cast and SRID
( ST_MakePoint(1,2) ),
-- explicit cast, implicit SRID
( ST_MakePoint(1,2)::geography ),
-- explicit cast and SRID
( ST_SetSRID( ST_MakePoint(3,4), 4326 )::geography );
Converting to text and then forcing PostgreSQL to parse the text with
ST_GeogFromText is silly and slow.