4

I'm trying to insert a null value into a geometry column, which I cannot do: "ERROR: parse error - invalid geometry" So what I'm trying to accomplish is to erase a point from a map and save that information - the deletion of the point - to the database.

I'm sort of new to this whole spatial data thingie, so this is probably a very moronic question, but all I can find from the internet is how to insert points, polygons and whatnot, but not how to remove that information.

The table where the geometry column resides has other information as well, so removing a row is not something I can do. Thank you in advance.

  • 1
    Welcome to gis.stackexchange.com. Your table have geometry constraints implemented by Populate_Geometry_Columns, to ensure. To allow other to get you a better answer, could you please explain why you need to NULL some geometries? Maybe ther is another way. – Alexandre Neto Jul 29 '13 at 9:23
6
UPDATE "table" SET the_geom = NULL

This will work unless you have a NOT NULL-constraint on the geom-column.

  • 3
    Yes, indeed! Thank you for the swift reply. My problem was the way php handles NULL-values which is confusing, to say the least. Thus I was actually trying to save a 'null' instead of NULL. – John Smith Jul 29 '13 at 9:39
5

Common approach when creating spatial tables is ensure that certain constraints are accomplished. This common constraints can be checked in the documentation of populate_geometry_columns.

One of these constraints is add to the geometry field a NOT NULL check. If you really need a table that admits null geometries you can remove the constraint for that table:

ALTER TABLE my_table ALTER COLUMN my_column DROP NOT NULL;

But have null geometries is usually a bad idea, because some software can have problems handling it. Depending on your use case probably a better idea is have another table that keeps the removed points.

-2

It's maybe better to remove unnecessary points rather than setting a 'NULL' geometry.

To delete a point (the entire row will be deleted) you can use an expression like that:

DELETE FROM mytable WHERE id_point = 'xxxx';

if the ID column is an integer :

DELETE FROM mytable WHERE id_point = xxxx;

You must specify a WHERE clause otherwise your table will be empty.

  • why nor "ALTER TABLE xx add deleted boolean set default false" and "UPDATE TABLE xx SET deleted = true WHERE ... – simplexio Jul 29 '13 at 9:38
  • Right ! I think that I didn't understand the problem... after this sentence "so removing a row is not something I can do" delete query are adapted. – Benno Jul 29 '13 at 10:25

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.