Inserting and invalid geometry PostGIS

Question

I have created a function that runs some validity checks on tables/views in postgres/postgis. Postgres 9.6.11 and PostGIS 2.3. I now need to test this function so I need create a table with an invalid geometry in it. Preferable one that can be corrected with ST_MakeValid and one that wouldn't be corrected by that, so I can test all use cases. How does one insert an invalid geometry into a postgis table.

JGH JGH · Accepted Answer · 2019-04-02T12:52:59

It is not a simple task and it is very version dependent.

You can try to brake a valid geometry, or you can try to insert an invalid one.

For example, this line creates an invalid line (2 points, start = end point)

SELECT st_setsrid(st_geomfromtext('LINESTRING(480 270,480 270)'),3857);

ST_MakeValid will transform it to a point.

or create a self intersecting polygon

SELECT st_isValid('polygon((0 0, 1 0, 0 1, 1 1, 0 0))'::geometry);
NOTICE:  Self-intersection at or near point 0.5 0.5
 st_isvalid
------------
 f

ST_MakeValid will transform it to a multipolygon

You can also find more sample cases here

Inserting and invalid geometry PostGIS

1 Answers