1

I was trying to convert a roadnetwork table.Its geometry contains both MULTILINESTRING and LINESTRING.I converted all the MULTILINE string to Linestring using the query

update all_india_merge set the_geom=ST_GeometryN(the_geom,1) where astext(the_geom) <> 'LINESTRING';

After conversion Some data has been lost.How can I recover those geometries. Please Help me.

  • You have updated your table by writing the first parts of the multilinestring objects into the_geom and over the original data. If the update has been committed it is permanent now. I hope you can find a backup of your data. If you are very lucky your database may offer a way to rollback. – user30184 Jun 30 '14 at 13:29
1

As @user30184 has stated you have performed an update on your RDBMS data without wrapping it in a transaction. Unfortunately there is no way to recover the data without resorting to a backup (unless you have an audit table, which are not as commonly used as they should be). To recover your data you will have to restore from a backup.

In the future, utilize transactions when doing update queries on your data. This helps protect you in the event that you do something in SQL without drinking enough coffee first, like leaving the where clause off of your update query. Not that I've ever done that and spent the next two hours tearing my beard out trying to fix the data and then finally resorting to backups, of course. Well, the nice thing is that since it has happened to you, you'll definitely make sure that it never happens again. I'm not sure what RDBMS you are using, but the syntax in PostgreSQL/PostGIS is something like below:

BEGIN; --begin statement wrap
UPDATE accounts SET balance = balance - 100.00 --do our damage here
    WHERE name = 'Alice'; --what we should include, but might forget if we are sleepy
-- SELECT * FROM accounts --make sure we didn't bone everything up
COMMIT; -- leave out this part till we make sure we didn't bone up, then execute it to write to db

And in SQL Server, provided by StuartLC:

begin transaction -- Just in case my query goofs up
update table whatever with whatever
select ... from table ... -- check that the correct updates / deletes / inserts happened
-- commit transaction  -- Only commit if the above check succeeds.

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.