Migrate from node location to cck location [#857000]

Until recently, the easiest way to show a google map in a node was to use a location map block together with the Location module. No CCK location fields if you do it this way.

Things changed with the flurry of releases in July 2010 – it's now easy to use a CCK location field to display a Google map in a node. I've tried it and it seems to work really well – thanks guys!

So now I have legacy sites showing Google maps in the 'old-school' non-cck way. I'd like to migrate my data to a cck-location field because it's more flexible and probably more future-proof. Simple-minded approaches like adding a cck-location field to an existing content type with location data and hoping it will pick-up the data don't seem to work.

As far as I can see the only difference in the database is in the location_instance table. So one approach might be to run a SQL query and mod this table. YesCT has suggested an alternative approach using a rule to copy the data across. I'll run some trials on both these methods.

Is there an easier/safer way that I'm missing?

I can't be the only person wanting to do this and the best answer is not obvious to me at least, so any tips would be really good.

-j (for jack)

Comments

Comment #1

-j CreditAttribution: -j commented 17 July 2010 at 12:43

Later that day...

When a new node with a location cck field is created it seems that three things happen in the database:
1. A new row is added to the location table to store the actual data
2. A new row is added to the location_instance table with an entry in the genid column in the form cck:field_myfieldname:nid
3. A new row is added to the content_type_mycontenttype table linking the nid and vid to the location id

When adding a cck-location field to an existing node with location data and saving the node, change number 1 is not needed since the location data is already there but changes 2 and 3 are needed and don't happen so the new field is not connected to any data.

I can edit the database to make changes 2 and 3 and it all seems to work but that feels awfully hacky and error-prone.

So can anyone suggest a civilized way to trigger all the changes needed to link a new cck-location field to an existing location id?

jack

Comment #2

lolmaus CreditAttribution: lolmaus commented 28 July 2010 at 08:04

Subscribing ^-^

Comment #3

Summit CreditAttribution: Summit commented 28 July 2010 at 08:08

Subscribing, I also have legacy location - nodes, interested in best approach to move to cck-location, but also visa versa. Greetings, Martijn

Comment #4

nemchenk CreditAttribution: nemchenk commented 12 October 2010 at 11:12

Any news on this? (Hi Martjin!)

Comment #5

-j CreditAttribution: -j commented 14 October 2010 at 08:08

I couldn't find a 'civilised' way to do this so I resorted to working directly on the database. I'm not a SQL guru and my case may be different from yours so I'll just give the outline of what I did.

My content type was called 'business'. I just had one location per business and I've never enabled revisions so my nid and vid values were the same.

I did an inner join to create a field_business_location_lid column in the content_type_business table. This column now contains the appropriate location id (lid) from the location_instance table.

I then updated the location_instance table by setting the genid column to concat('cck:field_business_location:', nid).

Obviously, I was backed-up all ways and I tested thoroughly on a development site first.

It's worked fine ever since.

I still wish there was a way to do it without poking around inside database tables.

jack

Comment #6

nemchenk CreditAttribution: nemchenk commented 26 October 2010 at 10:13

Thanks, jack. I had also come to the same conclusion and just hacked the DB.

There really ought to be an automated way of doing this, since Location Node is really now obsolete...

Comment #7

mstrelan CreditAttribution: mstrelan commented 8 November 2010 at 03:01

If anyone wants the SQL for #5 it is below, but remember to replace the names in square brackets. Also make sure you create the field first

UPDATE content_type_[CONTENT_TYPE] ct, location_instance li 
SET ct.[NEW_LOCATION_FIELD]_lid = li.lid
WHERE ct.nid = li.nid
AND ct.vid = li.vid;

UPDATE location_instance li
SET genid = concat('cck:[NEW_LOCATION_FIELD]:', nid)
WHERE nid IN (SELECT nid FROM content_type_[CONTENT_TYPE]);

Comment #8

rocbrook CreditAttribution: rocbrook commented 27 February 2012 at 17:37

This post is old, is this still the only way to do this?

I have a ton of Location (D7, L7.x-3) info in a "node location" but want to move them to a "field (cck) location" and don't want to get into fiddling with the db.

Comment #9

alanpeart CreditAttribution: alanpeart commented 7 February 2013 at 06:07

I would like to say a big thank you to mstrelan for sharing the SQL, I would have figured it out in the end but you probably saved me an hour or so.

Rocbrook - believe it or not, there isn't a way to do this without fiddling with the DB - not that I've been able to find anyway. The SQL posted above works, just back up your DB first.

Just spotted that you're using D7 - sorry, can't comment on that, have only tried this in D6!