What Is Srid 0 for Geometry Columns

What is SRID 0 for geometry columns?

A SRID of 0 doesn't technically exist, it just means no SRID -- ie, the default if you forget to set it. So, technically, you can still perform distance, intersection and all other queries, so long as both sets of geometries have a SRID of 0. If you have one field of geometries with a SRID of 0 and another set with a SRID that actually exists, you will most likely get very strange results. I remember scratching my head once when not getting any results from a spatial query in exactly this situation and SQL Server did not complain, just 0 results (for what is is worth Postgis will actually fail, with a warning about non-matching SRIDs).

In my opinion, you should always explicitly set the SRID of your geometries (or geographies, which naturally will always be 4326), as not only does it prevent strange query results, but it means you can convert from one coordinate system to another. Being able to convert on the fly from lat/lon (4326), to Spherical Mercator (3857), as used in Google Maps/Bing, which is in meters, or some local coordinate system, such as 27700, British National Grid, also in meters, can be very useful. SQL Server does not to my knowledge support conversion from one SRID to another, but as spatial types are essentially CLR types, there are .NET libraries available should you ever need to do so, see Transform/ Project a geometry from one SRID to another for an example.

If you do decide to change you geometries, you can do something like:

UPDATE your_table SET newGeom = geometry::STGeomFromWKB(oldGeom.STAsBinary(), SRID);

which will create a new column or to do it in place:

UPDATE geom SET geom.STSrid=4326;

where 4326 is just an example SRID.

There is a good reference for SRIDs at http://spatialreference.org/, though this is essentially the same information as you find in sys.spatial_reference_systems.

Geometry column: STGeomFromText and SRID (what is an SRID?)

So I ended up talking with an ex-military guy yesterday who was a radar/mapping specialist.
Basically, he knew exactly what that number (4326) was, where it came from, and why it is there.

It is an industry standard for computing geography. The problem is that the earth is not a perfect sphere (it bulges in the middle), and SRID 4326 accounts for that.

As I stated, the table sys.spatial_reference_systems lists all of the code and what they are. But the short version is that you are really only going to use 4326 unless you have a very specific reason to use something different.

Why does SQL Server Geometry accept SRIDs?

Someone just voted up this question so I thought I'd answer it. From what I can tell, this only purpose of assigning SRIDs to geometry types is for metadata. If you externally transformed the geographic data into projected form, you could then store it as a geometry type in SQL Server as it is now flat.

The SRID is not used in any calculations, but exists for record keeping. In fact, if two geometry variables have different SRIDs then SQL Server will throw an error when trying to preform an operation.

Input geometry has unknown (0) SRID

Some PostGIS functions rely on SRS, such as ST_Transform. You have to specify which SRS you're transforming from, otherwise the conversion script has no reference to compute the new coordinates, e.g. from EPSG:25832 to EPSG:4326:

SELECT ST_Transform('SRID=25832;POINT(1 1)',4326);

.. otherwise it will raise an exception

SELECT ST_Transform('POINT(1 1)',4326); -- <-- WKT literal without SRS
ERROR:  ST_Transform: Input geometry has unknown (0) SRID

With ST_SetSRID you can set the SRS to geometries in case they haven't any - as your example suggests, e.g. .

SELECT ST_Transform(
         ST_SetSRID('POINT(1 1)'::geometry,25832),
         4326);

The same principle goes for CREATE TABLE and INSERT / UPDATE statements. When creating a table we declare the SRS as follows ..

CREATE TABLE t (geom geometry(point,4326));

.. so PostGIS expects that all incoming geometries have the same SRS ..

INSERT INTO t VALUES ('SRID=4326;POINT(1 1)'); 

.. otherwise it raises an exception too

INSERT INTO t VALUES ('SRID=25832;POINT(1 1)'); 
ERROR:  Geometry SRID (25832) does not match column SRID (4326)

Postgis: create column with srid in procedure

You can use format() to create your DDL dynamically.

DO $$
DECLARE
    sridval int;
BEGIN
 sridval := (SELECT srid FROM project_options); 
 EXECUTE FORMAT('ALTER TABLE poi ADD COLUMN coords geometry(point, %s)',sridval);
END $$;

You can also skip the variable declaration by passing the query itself as parameter:

DO $$
BEGIN
  EXECUTE FORMAT('ALTER TABLE poi ADD coords geometry(point, %s)',
                 (SELECT srid FROM project_options));
END $$;

And

DO $$
BEGIN
 EXECUTE FORMAT('ALTER TABLE poi ADD coords geometry(point, %s) 
                 GENERATED ALWAYS AS (ST_MakePoint(longitude, latitude)) STORED;',
                (SELECT srid FROM project_options));
END $$;

• This code assumes that project_options has a single row

Demo: db<>fiddle

Polygon insertion issue (due to SRID) on Postgres

Two things:

• You can add a geometry column directly in PostGIS 2.0+ just using the ALTER TABLE command. ALTER TABLE foo ADD COLUMN geom Geometry(Polygon,4326)
• As the error says, the geometry you're creating from JSON lacks the SRID of the column. So you need to ensure it is set. The easiest way is with ST_SetSrid, so (abbreviated) UPDATE foo SET geom = ST_SetSRID(ST_GeomFromGeoJSON(...),4326)

Error with inserting a point into a geometry (point) typed column in Postgress with PostGIS

Use the EWKT representation:

'SRID=4326;POINT(58.183594 22.593726)'

---

Related Topics