Groups keyboard shortcuts have been updated
Dismiss
See shortcuts
Translating Tracing Data
20 views
Skip to first unread message
Marcel Sayre
Apr 21, 2020, 4:50:31 AM4/21/20
to CATMAID
Hello everyone,
Thanks!
Marcel
I have a stack of image data on CATMAID which has been incorrectly positioned in 3D coordinate space. Unfortunately, I realized this only after we began tracing and we now have ~70 neurons + connectors which all need to be translated to the correct coordinate values. In short, I need to do the following things:
1.) Export all tracing data from the current CATMAID instance
2.) Translate all tracing data in the following way:
x (new) = x (old) + 103935.96
y (new) = y (old) + 77665.5567
z (new) = z (old) + 166425.408
3.) Import adjusted tracing data back into CATMAID instance
So far I am able to do this just fine with the neuron .swc skeletons using Nat + Nat-catmaid packages (thanks to Greg!) and the CATMAID .swc Importer widget. However, I'm wondering how I can apply this same transformation to the connectors while preserving input/output associations?
Thanks!
Marcel
Albert Cardona
Apr 21, 2020, 6:54:35 AM4/21/20
to cat...@googlegroups.com
Hi Marcel,
Back up your database, then open psql and translate the point coordinates directly. A single SQL command would do it.
Then adjust the volume offsets etc. in the admin pages of CATMAID’s web client.
Best,
Albert
--
You received this message because you are subscribed to the Google Groups "CATMAID" group.
To unsubscribe from this group and stop receiving emails from it, send an email to catmaid+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/catmaid/a1ebbca2-804f-482b-b3cd-e02b88ecb237%40googlegroups.com.
Tom Kazimiers
Apr 21, 2020, 12:15:59 PM4/21/20
to cat...@googlegroups.com
Hi Marcel,
Albert is right, updating nodes through the database is likely the
better approach, because it avoids many moving parts you are dealing
with at the moment and is faster and more robust too:
Assuming that you need to update all treenodes and synapses in a
particular project, you can do the following:
1. create a backup of your database (pg_dumpall is likely the quickest
for you)
2. connect to your db:
manage.py dbshell
3. start a new transaction:
BEGIN;
4. list projects to get ID of correct projcet
SELECT * FROM project;
5. update all "location" entries with their new coordinates (using
project_id=1 and factor 1000 as example:
UPDATE location SET location_x=location_x * 1000, location_y=location_y * 1000, location_z=location_z * 1000
WHERE project_id = 1;
6. Check if everything looks good:
SELECT * from location LIMIT 10;
7. If everything looks good, commit your changes:
COMMIT;
8. Exit database: Ctrl + D
Note that if there is an error inside the transaction (between begin and
commit), you need to rollback the transaction and start again before you
can issue any new commands: ROLLBACK; and then BEGIN; again.
Of course you don't need a transaction to execute the UPDATE, but when
working with production data in the database, this safeguard is always
advisable. It's easy to make a typo and a wrong DELETE can be really
fast.
The above assumes of course that all treenodes and connectors in a
particular project (with ID=1 in example above) need to be
multiplied by 1000 in X, Y and Z. Of course only a subset or other
operations can be done as well.
Using SQL has the benefit of being able to work on all data that needs
updates at once in a transactional manner. I agree though that it might
be nice to have such an operation exposed in the admin view might be a
good idea.
CATMAID uses table inheritance in its database schema and more detailed
tables like treenode or connector inherit from the location table. With
an UPDATE statement like above, all child tables from location will be
updated, too (which is what you want here).
Best,
Tom
>To view this discussion on the web visit https://groups.google.com/d/msgid/catmaid/10982510-6123-41A8-9DE8-289FC880DC59%40gmail.com.
Albert is right, updating nodes through the database is likely the
better approach, because it avoids many moving parts you are dealing
with at the moment and is faster and more robust too:
Assuming that you need to update all treenodes and synapses in a
particular project, you can do the following:
1. create a backup of your database (pg_dumpall is likely the quickest
for you)
2. connect to your db:
manage.py dbshell
3. start a new transaction:
BEGIN;
4. list projects to get ID of correct projcet
SELECT * FROM project;
5. update all "location" entries with their new coordinates (using
project_id=1 and factor 1000 as example:
UPDATE location SET location_x=location_x * 1000, location_y=location_y * 1000, location_z=location_z * 1000
WHERE project_id = 1;
6. Check if everything looks good:
SELECT * from location LIMIT 10;
7. If everything looks good, commit your changes:
COMMIT;
8. Exit database: Ctrl + D
Note that if there is an error inside the transaction (between begin and
commit), you need to rollback the transaction and start again before you
can issue any new commands: ROLLBACK; and then BEGIN; again.
Of course you don't need a transaction to execute the UPDATE, but when
working with production data in the database, this safeguard is always
advisable. It's easy to make a typo and a wrong DELETE can be really
fast.
The above assumes of course that all treenodes and connectors in a
particular project (with ID=1 in example above) need to be
multiplied by 1000 in X, Y and Z. Of course only a subset or other
operations can be done as well.
Using SQL has the benefit of being able to work on all data that needs
updates at once in a transactional manner. I agree though that it might
be nice to have such an operation exposed in the admin view might be a
good idea.
CATMAID uses table inheritance in its database schema and more detailed
tables like treenode or connector inherit from the location table. With
an UPDATE statement like above, all child tables from location will be
updated, too (which is what you want here).
Best,
Tom
Marcel Sayre
Apr 22, 2020, 10:03:47 PM4/22/20
to CATMAID
Hi Tom and Albert,
Thank you both for your quick responses! We've updated the coordinate values in our psql tables as you both suggested and this appears to have worked! We are experiencing another issue now, but I think this may be related to a previous issue that we are currently working through with Tom.
All the best,
Marcel
>> To unsubscribe from this group and stop receiving emails from it, send an email to cat...@googlegroups.com.
>> To view this discussion on the web visit https://groups.google.com/d/msgid/catmaid/a1ebbca2-804f-482b-b3cd-e02b88ecb237%40googlegroups.com.
>
>--
>You received this message because you are subscribed to the Google Groups "CATMAID" group.
>To unsubscribe from this group and stop receiving emails from it, send an email to cat...@googlegroups.com.
Tom Kazimiers
Apr 23, 2020, 12:02:42 PM4/23/20
to cat...@googlegroups.com
Hi Marcel,
Good to hear this worked for you! To also share this information with
the mailing list, I copy here what I wrote earlier to you when debugging
the problem you ran into (invisible nodes) after updating your location
table entries:
When you modify the location table directly, you need to run the
following command afterwards:
manage.py catmaid_rebuild_all_materializations
CATMAID computes and persists certain information based on information
in other tables, e.g. an edge representation between a treenode and its
parent, but also information like the cable length or number of nodes of
individual skeletons. These materializations are normally generated
automatically in the database when you change data (regardless from
where, e.g. manually with an UPDATE statement in psql). The command
above makes sure all of them are up-to-date. However, these trigger
functions exist only on the respective child tables in the table
inheritance hierarchy of the location table. They were not called since
you modified the location table directly and due to the fact that we use
statement level triggers for these updates on the child tables, and
these aren't executed on parent table updates for child tables [1].
That's however the only trigger that wasn't called here, so the above
command should be enough to fix it.
Best,
Tom
[1] https://www.postgresql.org/docs/12/trigger-definition.html
>To view this discussion on the web visit https://groups.google.com/d/msgid/catmaid/d170b6f3-3fad-437f-b4ff-e0f369d697d2%40googlegroups.com.
Good to hear this worked for you! To also share this information with
the mailing list, I copy here what I wrote earlier to you when debugging
the problem you ran into (invisible nodes) after updating your location
table entries:
When you modify the location table directly, you need to run the
following command afterwards:
manage.py catmaid_rebuild_all_materializations
CATMAID computes and persists certain information based on information
in other tables, e.g. an edge representation between a treenode and its
parent, but also information like the cable length or number of nodes of
individual skeletons. These materializations are normally generated
automatically in the database when you change data (regardless from
where, e.g. manually with an UPDATE statement in psql). The command
above makes sure all of them are up-to-date. However, these trigger
functions exist only on the respective child tables in the table
inheritance hierarchy of the location table. They were not called since
you modified the location table directly and due to the fact that we use
statement level triggers for these updates on the child tables, and
these aren't executed on parent table updates for child tables [1].
That's however the only trigger that wasn't called here, so the above
command should be enough to fix it.
Best,
Tom
[1] https://www.postgresql.org/docs/12/trigger-definition.html
>> <javascript:>> wrote:
>> >>
>> >> Hello everyone,
>> >>
>> >> I have a stack of image data on CATMAID which has been incorrectly
>> positioned in 3D coordinate space. Unfortunately, I realized this only
>> after we began tracing and we now have ~70 neurons + connectors which all
>> need to be translated to the correct coordinate values. In short, I need to
>> do the following things:
>> >>
>> >> 1.) Export all tracing data from the current CATMAID instance
>> >>
>> >> 2.) Translate all tracing data in the following way:
>> >>
>> >> x (new) = x (old) + 103935.96
>> >> y (new) = y (old) + 77665.5567
>> >> z (new) = z (old) + 166425.408
>> >>
>> >> 3.) Import adjusted tracing data back into CATMAID instance
>> >>
>> >>
>> >> So far I am able to do this just fine with the neuron .swc skeletons
>> using Nat + Nat-catmaid packages (thanks to Greg!) and the CATMAID .swc
>> Importer widget. However, I'm wondering how I can apply this same
>> transformation to the connectors while preserving input/output
>> associations?
>> >>
>> >> Thanks!
>> >> Marcel
>> >> --
>> >> You received this message because you are subscribed to the Google
>> Groups "CATMAID" group.
>> >> To unsubscribe from this group and stop receiving emails from it, send
>> an email to cat...@googlegroups.com <javascript:>.
>> >>
>> >> Hello everyone,
>> >>
>> >> I have a stack of image data on CATMAID which has been incorrectly
>> positioned in 3D coordinate space. Unfortunately, I realized this only
>> after we began tracing and we now have ~70 neurons + connectors which all
>> need to be translated to the correct coordinate values. In short, I need to
>> do the following things:
>> >>
>> >> 1.) Export all tracing data from the current CATMAID instance
>> >>
>> >> 2.) Translate all tracing data in the following way:
>> >>
>> >> x (new) = x (old) + 103935.96
>> >> y (new) = y (old) + 77665.5567
>> >> z (new) = z (old) + 166425.408
>> >>
>> >> 3.) Import adjusted tracing data back into CATMAID instance
>> >>
>> >>
>> >> So far I am able to do this just fine with the neuron .swc skeletons
>> using Nat + Nat-catmaid packages (thanks to Greg!) and the CATMAID .swc
>> Importer widget. However, I'm wondering how I can apply this same
>> transformation to the connectors while preserving input/output
>> associations?
>> >>
>> >> Thanks!
>> >> Marcel
>> >> --
>> >> You received this message because you are subscribed to the Google
>> Groups "CATMAID" group.
>> >> To unsubscribe from this group and stop receiving emails from it, send
>> >> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/catmaid/a1ebbca2-804f-482b-b3cd-e02b88ecb237%40googlegroups.com.
>>
>> >
>> >--
>> >You received this message because you are subscribed to the Google Groups
>> "CATMAID" group.
>> >To unsubscribe from this group and stop receiving emails from it, send an
>> email to cat...@googlegroups.com <javascript:>.
>> https://groups.google.com/d/msgid/catmaid/a1ebbca2-804f-482b-b3cd-e02b88ecb237%40googlegroups.com.
>>
>> >
>> >--
>> >You received this message because you are subscribed to the Google Groups
>> "CATMAID" group.
>> >To unsubscribe from this group and stop receiving emails from it, send an
>> >To view this discussion on the web visit
>> https://groups.google.com/d/msgid/catmaid/10982510-6123-41A8-9DE8-289FC880DC59%40gmail.com.
>>
>>
>
>> https://groups.google.com/d/msgid/catmaid/10982510-6123-41A8-9DE8-289FC880DC59%40gmail.com.
>>
>>
>
>--
>You received this message because you are subscribed to the Google Groups "CATMAID" group.
>To unsubscribe from this group and stop receiving emails from it, send an email to catmaid+u...@googlegroups.com.
>You received this message because you are subscribed to the Google Groups "CATMAID" group.
>To view this discussion on the web visit https://groups.google.com/d/msgid/catmaid/d170b6f3-3fad-437f-b4ff-e0f369d697d2%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages