3

During a migration from 1x to 2x on an EE site, I needed to switch to current Google Maps add-ons and chose MX Google Maps (for now anyway). The site was using a geotagger that filled a Latitude field and Longitude field with data, so those are still in place.

Since MX Google Maps uses it's own fields, I need to find a way to migrate current db data to MX Maps. But I'm hella bad at this SQL query stuff.

Right now, the lat/lon are stored in exp_channel_data in field_id_41 (lat) and field_id_42 (lon). MX Maps stores Lat/Lon in field_id_46 as a pipe delimited set (lat|lon). It also stores data for each entry in it's own table at exp_mx_google_map.

In exp_mx_google_map it has

  • point_id (a sequential number for each post)
  • entry_id (tied to the entry itself)
  • latitude
  • longitude
  • address (not using)
  • city (not using)
  • zipcode (not using)
  • state (not using)
  • field_id (in this case 46)
  • icon

With about 2400 entries storing lat/lon, what's the best way to go about moving this data over? Besides drinking heavily and sleeping on it, which I've tried and doesn't work.

Improve this question

1 Answer 1

Reset to default
4

That should be as simple as this, using the correct channel_id:

Update exp_channel_data
set field_id_46 = concat(field_id_41, '|', field_id_42)
where channel_id = 1

For the Maps table, it's something like this:

Insert into exp_mx_google_map
(entry_id, lat, long, some_example_field)
Select entry_id, field_id_41, field_id_42, 'Some example value'
from exp_channel_data
where channel_id = 1

You can fill in the other fields from there.

Updated: Sorry, the values line caused an error in the Insert statement. I've updated that.

I only included a few sample fields in that. You'll want to be sure to map the rest. Note that you can pass your own values, they don't all have to be from fields.

As UltraBob suggested, you can use PHPMyAdmin, Sequel Pro, Navicat, etc to set the point_id field to AUTO_INCREMENT. You don't need to include it in your insert then. It will get a value as each record is created.

Improve this answer
8
  • Thanks Mark, the first one ran perfectly. Getting a syntax error on the second one. Wil the second one add the sequential point_id? Commented Feb 12, 2013 at 23:41
  • is auto_increment set on the point_id column? Commented Feb 13, 2013 at 4:30
  • I don't think you need two cancats in that first update query. That can be set field_id_46 = concat(field_id_41, '|', field_id_42) Commented Feb 13, 2013 at 4:35
  • @UltraBob - You're right about the concat. I must've had something else stuck in my head. Maybe Google Docs. Kelly Sims - Updated with some additional notes. Commented Feb 13, 2013 at 13:32
  • Perfect! Thanks for the help both of you. Now I'm running into an issue where even though the database is set right, posts aren't outputting the map unless I edit that entry and save it. Is this an EE issue, or something else? Commented Feb 13, 2013 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.