search

performant-software / Neatline

A lightweight framework for building interactive maps and publishing them on the web.
www.neatline.org
Other
105 stars 34 forks source link

Cannot get geometry object from data you send to the GEOMETRY field. #490

Closed tpegues2 closed 3 years ago

tpegues2 commented 4 years ago

Having issues when exporting database for Omeka Classic with older version of mysql to a newer version of mysql. Neatline database entries give me a "spatial data" error. This is keeping us from upgrading to the newer versions of Omeka Classic which use a newer release that the old versions of Neatline plugin don't fully support. There are some work arounds but we would prefer not to do anything that is not supported and may not be supported in the future by Neatline.

See a post I found below:

I had this same error (Cannot get geometry object from data you send to the GEOMETRY field) but when trying to import spatial data from a mysql dump. I found that some rows had "null" (X is null or Y is null) spatial data even though the column was "NOT NULL"..

Check if you have the same problem as I'm describing with this SQL:

SELECT id FROM locations WHERE X(coordinates) IS NULL OR Y(coordinates) IS NULL; If you have some rows then this is what worked for me:

UPDATE locations SET coordinates = POINT(0,0) WHERE X(coordinates) IS NULL OR Y(coordinates) IS NULL; Then try your mysqldump (or from phpmyadmin) and import again.

tpegues2 commented 4 years ago

Attempting to export and import one of the databases for my omeka 2.6.1 instance. I am currently running mysql 5.1 and trying to import the database into mysql 8.0. I get the following error when I attempt the import. It appears to be associated with the Neatline Plugin. ERROR 1416 (22003) at line 703: Cannot get geometry object from data you send to the GEOMETRY field. I also tried to import the mysql dump into mysql 5.7 and got the very same error message. Is there a fix available for this?

eam5 commented 4 years ago

Hi tpegues2,

We've run into this issue with migrating to new versions of MySQL before, and have a potential solution - please see this troubleshooting post in our documentation for further info: https://neatline.org/docs/troubleshooting/

If you have any questions, or need further troubleshooting, please respond here and I will pass the issue on to our developers.

Thank you, Elizabeth

tpegues2 commented 4 years ago

Hi Elizabeth,

Same results after trying the recommendations from the neatline website. I could not import the database into my mysql 5.7.1 version from my 5.1 version of mysql.

use {database name}; select astext(coverage) from omeka_neatline_records where astext(coverage) is null;

When I ran the select statement above that they recommended nothing came back so it might require a different fix. See below.

Tharrow Pegues

mysql> select AsText(coverage) from omeka_neatline_records where AsText(coverage) is null; Empty set (0.01 sec)

mysql> update omeka_neatline_records set coverage=GeomFromText('POINT(0 0)') where astext(coverage) is null; Query OK, 0 rows affected (0.01 sec) Rows matched: 0 Changed: 0 Warnings: 0

mysql> select AsText(coverage) from omeka_neatline_records where AsText(coverage) is null; Empty set (0.00 sec)

From: eam5 notifications@github.com Reply-To: scholarslab/Neatline reply@reply.github.com Date: Tuesday, June 16, 2020 at 4:31 PM To: scholarslab/Neatline Neatline@noreply.github.com Cc: Tharrow Pegues tpegues2@kennesaw.edu, Author author@noreply.github.com Subject: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi tpegues2,

We've run into this issue with migrating to new versions of MySQL before, and have a potential solution - please see this troubleshooting post in our documentation for further info: https://neatline.org/docs/troubleshooting/

If you have any questions, or need further troubleshooting, please respond here and I will pass the issue on to our developers.

Thank you, Elizabeth

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-644996130, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHX2JS2CY6AADKWXPALRW7JDFANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.

shane-et-al commented 4 years ago

Hi Tharrow, I really thought that we figured out the migration issues with the null coverage, but I guess we missed something. If your records data isn't too sensitive, could I have you send a copy of your coverage column to me at shane@virginia.edu?

select astext(coverage) from omeka_neatline_records;

tpegues2 commented 4 years ago

Any updates? Did you get the information I sent?

Tharrow

From: shane-et-al notifications@github.com Reply-To: scholarslab/Neatline reply@reply.github.com Date: Thursday, June 25, 2020 at 12:41 PM To: scholarslab/Neatline Neatline@noreply.github.com Cc: Tharrow Pegues tpegues2@kennesaw.edu, Author author@noreply.github.com Subject: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi Tharrow, I really thought that we figured out the migration issues with the null coverage, but I guess we missed something. If your records data isn't too sensitive, could I have you send a copy of your coverage column to me at shane@virginia.edumailto:shane@virginia.edu?

select astext(coverage) from omeka_neatline_records;

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-649681976, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHVBGWU5M6MSY6YC2CTRYN4ZRANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.

tpegues2 commented 4 years ago

My apologies. I see it was a typo on my part. ☹

From: Tharrow Pegues tpegues2@kennesaw.edu Date: Thursday, July 9, 2020 at 7:54 AM To: scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com, "shane@virginia.edu" shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Any updates? Did you get the information I sent?

Tharrow

From: shane-et-al notifications@github.com Reply-To: scholarslab/Neatline reply@reply.github.com Date: Thursday, June 25, 2020 at 12:41 PM To: scholarslab/Neatline Neatline@noreply.github.com Cc: Tharrow Pegues tpegues2@kennesaw.edu, Author author@noreply.github.com Subject: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi Tharrow, I really thought that we figured out the migration issues with the null coverage, but I guess we missed something. If your records data isn't too sensitive, could I have you send a copy of your coverage column to me at shane@virginia.edumailto:shane@virginia.edu?

select astext(coverage) from omeka_neatline_records;

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-649681976, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHVBGWU5M6MSY6YC2CTRYN4ZRANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.

tpegues2 commented 4 years ago

Hi there,

Hmm. I actually didn't get your email with your exhibit data until this morning for some reason, but I'm glad to hear that you figured out what the problem was.

Best, Shane

Shane Lin

Senior Developer

University of Virginia Library Scholars' Lab

From: Tharrow Pegues tpegues2@kennesaw.edu Sent: Thursday, July 9, 2020 7:57 AM To: scholarslab/Neatline reply@reply.github.com; scholarslab/Neatline Neatline@noreply.github.com; Lin, Shane (ssl2ab) shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

My apologies. I see it was a typo on my part. ☹

From: Tharrow Pegues tpegues2@kennesaw.edu Date: Thursday, July 9, 2020 at 7:54 AM To: scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com, "shane@virginia.edu" shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Any updates?

Did you get the information I sent?

Tharrow

From: shane-et-al notifications@github.com Reply-To: scholarslab/Neatline reply@reply.github.com Date: Thursday, June 25, 2020 at 12:41 PM To: scholarslab/Neatline Neatline@noreply.github.com Cc: Tharrow Pegues tpegues2@kennesaw.edu, Author author@noreply.github.com Subject: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi Tharrow, I really thought that we figured out the migration issues with the null coverage, but I guess we missed something. If your records data isn't too sensitive, could I have you send a copy of your coverage column to me at shane@virginia.edumailto:shane@virginia.edu?

select astext(coverage) from omeka_neatline_records;

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-649681976, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHVBGWU5M6MSY6YC2CTRYN4ZRANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.

tpegues2 commented 4 years ago

Hi Shane,

It is not resolved. I sent a follow up email.

Thanks,

Tharrow

From: "Lin, Shane (ssl2ab)" shane@virginia.edu Date: Thursday, July 9, 2020 at 3:03 PM To: Tharrow Pegues tpegues2@kennesaw.edu, scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi there,

Hmm. I actually didn't get your email with your exhibit data until this morning for some reason, but I'm glad to hear that you figured out what the problem was.

Best, Shane

Shane Lin

Senior Developer

University of Virginia Library Scholars' Lab

From: Tharrow Pegues tpegues2@kennesaw.edu Sent: Thursday, July 9, 2020 7:57 AM To: scholarslab/Neatline reply@reply.github.com; scholarslab/Neatline Neatline@noreply.github.com; Lin, Shane (ssl2ab) shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

My apologies. I see it was a typo on my part. ☹

From: Tharrow Pegues tpegues2@kennesaw.edu Date: Thursday, July 9, 2020 at 7:54 AM To: scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com, "shane@virginia.edu" shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Any updates?

Did you get the information I sent?

Tharrow

From: shane-et-al notifications@github.com Reply-To: scholarslab/Neatline reply@reply.github.com Date: Thursday, June 25, 2020 at 12:41 PM To: scholarslab/Neatline Neatline@noreply.github.com Cc: Tharrow Pegues tpegues2@kennesaw.edu, Author author@noreply.github.com Subject: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi Tharrow, I really thought that we figured out the migration issues with the null coverage, but I guess we missed something. If your records data isn't too sensitive, could I have you send a copy of your coverage column to me at shane@virginia.edumailto:shane@virginia.edu?

select astext(coverage) from omeka_neatline_records;

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-649681976, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHVBGWU5M6MSY6YC2CTRYN4ZRANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.

shane-et-al commented 4 years ago

Oh, I misconstrued your email. I will take a look at this this week.

On Jul 9, 2020 3:27 PM, tpegues2 notifications@github.com wrote:

Hi Shane,

It is not resolved. I sent a follow up email.

Thanks,

Tharrow

From: "Lin, Shane (ssl2ab)" shane@virginia.edu Date: Thursday, July 9, 2020 at 3:03 PM To: Tharrow Pegues tpegues2@kennesaw.edu, scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi there,

Hmm. I actually didn't get your email with your exhibit data until this morning for some reason, but I'm glad to hear that you figured out what the problem was.

Best, Shane

Shane Lin

Senior Developer

University of Virginia Library Scholars' Lab

From: Tharrow Pegues tpegues2@kennesaw.edu Sent: Thursday, July 9, 2020 7:57 AM To: scholarslab/Neatline reply@reply.github.com; scholarslab/Neatline Neatline@noreply.github.com; Lin, Shane (ssl2ab) shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

My apologies. I see it was a typo on my part. ☹

From: Tharrow Pegues tpegues2@kennesaw.edu Date: Thursday, July 9, 2020 at 7:54 AM To: scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com, "shane@virginia.edu" shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Any updates?

Did you get the information I sent?

Tharrow

From: shane-et-al notifications@github.com Reply-To: scholarslab/Neatline reply@reply.github.com Date: Thursday, June 25, 2020 at 12:41 PM To: scholarslab/Neatline Neatline@noreply.github.com Cc: Tharrow Pegues tpegues2@kennesaw.edu, Author author@noreply.github.com Subject: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi Tharrow, I really thought that we figured out the migration issues with the null coverage, but I guess we missed something. If your records data isn't too sensitive, could I have you send a copy of your coverage column to me at shane@virginia.edumailto:shane@virginia.edu?

select astext(coverage) from omeka_neatline_records;

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-649681976, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHVBGWU5M6MSY6YC2CTRYN4ZRANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.

— You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-656308948, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAR3Z4ZXEBO4HUQQFF4PN63R2YK3FANCNFSM4N76M2QA.

tpegues2 commented 3 years ago

Hi Shane,

Any updates or do you need anything else from me?

Many Thanks,

Tharrow

From: Tharrow Pegues tpegues2@kennesaw.edu Date: Thursday, July 9, 2020 at 3:27 PM To: "Lin, Shane (ssl2ab)" shane@virginia.edu, scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi Shane,

It is not resolved. I sent a follow up email.

Thanks,

Tharrow

From: "Lin, Shane (ssl2ab)" shane@virginia.edu Date: Thursday, July 9, 2020 at 3:03 PM To: Tharrow Pegues tpegues2@kennesaw.edu, scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi there,

Hmm. I actually didn't get your email with your exhibit data until this morning for some reason, but I'm glad to hear that you figured out what the problem was.

Best, Shane

Shane Lin

Senior Developer

University of Virginia Library Scholars' Lab

From: Tharrow Pegues tpegues2@kennesaw.edu Sent: Thursday, July 9, 2020 7:57 AM To: scholarslab/Neatline reply@reply.github.com; scholarslab/Neatline Neatline@noreply.github.com; Lin, Shane (ssl2ab) shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

My apologies. I see it was a typo on my part. ☹

From: Tharrow Pegues tpegues2@kennesaw.edu Date: Thursday, July 9, 2020 at 7:54 AM To: scholarslab/Neatline reply@reply.github.com, scholarslab/Neatline Neatline@noreply.github.com, "shane@virginia.edu" shane@virginia.edu Cc: Author author@noreply.github.com Subject: Re: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Any updates?

Did you get the information I sent?

Tharrow

From: shane-et-al notifications@github.com Reply-To: scholarslab/Neatline reply@reply.github.com Date: Thursday, June 25, 2020 at 12:41 PM To: scholarslab/Neatline Neatline@noreply.github.com Cc: Tharrow Pegues tpegues2@kennesaw.edu, Author author@noreply.github.com Subject: [EXTERNAL] Re: [scholarslab/Neatline] Cannot get geometry object from data you send to the GEOMETRY field. (#490)

Hi Tharrow, I really thought that we figured out the migration issues with the null coverage, but I guess we missed something. If your records data isn't too sensitive, could I have you send a copy of your coverage column to me at shane@virginia.edumailto:shane@virginia.edu?

select astext(coverage) from omeka_neatline_records;

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-649681976, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHVBGWU5M6MSY6YC2CTRYN4ZRANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.

shane-et-al commented 3 years ago

Hi Tharrow,

Sorry about the delay. I figured out pretty quickly that there were eight specific lines out of the data you sent me that mysql did not like. However, I'm not actually very conversant in WKT, so it took me a little bit of staring to figure out what distinguished those lines from the others. Turns out, it was actually pretty obvious.

Valid WKT Polygons must have at least 4 points: three or more vertices and one additional point equal to the first that closes it off. The eight lines contain polygons that do not meet this criteria. These are not meaningful polygons and should be redefined as points or lines instead. If there's some reason that you really want them to be polygons, it is apparently valid (at least to mysql) to simply add one or two identical end points.

So: POLYGON(0 0, 0 0) and POLYGON(0 0, 10 10, 0 0) are not valid, but POLYGON(0 0, 10 10, 0 0, 0 0) is.

I hope that clears things up. I will send you the 8 offending entries through email and leave this here for posterity.

This is of course concerning for us because neatline shouldn't allow the creation of these invalid polygons. We'll discuss it internally and probably create a new issue to add some validation logic to prevent this.

eam5 commented 3 years ago

Hi @tpegues2, I'm not sure if this issue was resolved or not - but we now have a potential patch for the issues that have come up related to MySQL migrations. If you are interesting in testing out this plugin version, you can download it from this branch: https://github.com/scholarslab/Neatline/tree/bug/mysql-functions

If you continue to seeing errors with the Neatline plugin, please let us know.

shane-et-al commented 3 years ago

@eam5 Sorry, I just forgot to close this ticket. I sent the corrected SQL to @tpegues2 over email back in July and that seemed to have worked.

The most recent fix should have no impact on this issue. Since the MySQL docs indicate that "ST_GeomFromText(), ST_GeometryFromText(), GeomFromText(), and GeometryFromText() are synonyms", that update should not functionally change anything for older versions which still support GeomFromText.

That is, we still need to figure out how to mitigate the invalid polygon generation problem. Although knowing about the issue does allow us to guide users through migrations, it's certainly not an ideal place for this to be.

tpegues2 commented 3 years ago

Thanks so much!!

Sent from my iPhone

On Jan 20, 2021, at 1:03 PM, eam5 notifications@github.com wrote:



Hi @tpegues2https://github.com/tpegues2, I'm not sure if this issue was resolved or not - but we now have a potential patch for the issues that have come up related to MySQL migrations. If you are interesting in testing out this plugin version, you can download it from this branch: https://github.com/scholarslab/Neatline/tree/bug/mysql-functions

If you continue to seeing errors with the Neatline plugin, please let us know.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/scholarslab/Neatline/issues/490#issuecomment-763830089, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKCBRHTZUCI25PZZEI7LYSLS24LIFANCNFSM4N76M2QA.

CAUTION: This email originated from outside of KSU. Use caution replying or supplying information, clicking links or opening attachments. If you suspect the message is fraudulent, contact the UITS Service Desk at 470-578-6999 or service@kennesaw.edumailto:servicedesk@kennesaw.edu.