Geometry in PostGIS Table wrong SRID and type

Question

I have loaded some points into a table as Multipoint, they have come through with an SRID of 0. I need to change these to have a SRID of 7844 and also change them to be of type Point. There are other records already in the table with type of Point and SRID of 7844.

I have seen how to change the SRID from unknown to another projection here, https://postgis.net/2013/08/30/tip_ST_Set_or_Transform/ using ST_Set.

I have also seen that in PostGIS you can use ST_Dump for multipart geometries. I have also just found Multipoint vs point PostGIS

How do I change specific records SRID and downgrade from multi to individual points in PostGIS in SQL without affecting the other records.

is it acceptable here? I wasn't completely sure what was happening when using ST_Set if it was applying it to the actual geometry column or individual records. — JamesLeversha, May 25 '20 at 11:37
they are all just single points within a multipoint. It will have to be row wise as there are some points in there with type of point. — JamesLeversha, May 25 '20 at 12:12

geozelot · Accepted Answer · 2020-05-25T12:25:41.003

Assuming the general case where

your table has a column geom of type GEOMETRY(GEOMETRY) (meaning that it allows for any SRID and any geometry type, which is the only way you were able to import those MultiPoints in the first place)
your geometries are in fact referenced in the given CRS, but were imported without the SRID

you will need to re-insert your data in the way you intend to, as an UPDATE cannot expand a single row into a set of rows. PostgreSQL offers data-modifying CTEs that break this down into a single statement:

WITH
    del AS (
        DELETE FROM <your_table>
            WHERE GeometryType(geom) = 'MULTIPOINT'
        RETURNING (<your_table>.*)
    )

INSERT INTO <your_table> ([<column_list>,] geom) (
    SELECT  [<column_list>,]
            ST_SetSRID(dmp.geom, 7844)
    FROM    del,
            LATERAL ST_Dump(geom) AS dmp
);

This will

find and DELETE rows where the geometry type is MultiPoint in the CTE (improve the WHERE filter if needed), RETURNING them into the virtual del table
INSERT a geometry dump of the deleted geometries in del back into <your_table> in the main INSERT query

However:

If your MultiPoints are strictly single part geometries (meaning that they always only contain one point), you are able to indeed run an UPDATE:

UPDATE  <your_table>
  SET   geom = ST_SetSRID(ST_GeometryN(geom, 1), 7844)
  WHERE GeometryType = 'MULTIPOINT'
;

Some notes:

if avoidable, do not have a generic GEOMETRY(GEOMETRY) column, especially without SRID constraint
this is then better handled on import, likely with a BEFORE INSERT trigger, or before import in the client application

because I had strictly single part geometries the second part of this answer solved my problem. thanks. — JamesLeversha, May 26 '20 at 07:45

score 1 · Answer 2 · answered May 25 '20 at 12:02

I understand you have MultiPoints and Points, both with SRID 7844 and 0, all in one column mytable.geom. Coordinates given with SRID 0 really are SRID 7844 and don't need to be transformed. This is a first shot:

SELECT
    st_setsrid(d.geom, 7844) geom
    FROM mytable, st_dump(st_multi(mytable.geom)) d;

(untested, as you gave no example data)

Geometry in PostGIS Table wrong SRID and type

2 Answers2