« Digitizing biological collections | Main | Google Analytics on Drupal »

March 11, 2010

Load latitude and longitude into Drupal via database

Here are my notes:

1) Paired the research and their NIDs with the Lat/long data from bob.

2) Loaded this information into the location table. (reference file: insert_into_location.sql). Note, before loading the lats/longs I changed those database field settings from Decimal(10,6) to Decimal(14,10) to avoid the truncation of the coordinates I input.


INSERT INTO `database`.`location` (
`name` ,
`country` ,
`latitude` ,
`longitude` ,
`source` ,
`is_primary`
)
VALUES (

'Sucker River' , 'us' , '46.665334' , '-85.890727' , '1' , '0'),
( 'Lake Erie' , 'us' , '41.846071885' , '-83.2438109141' , '1' , '0'),
( 'Lake St. Clair' , 'us' , '42.4906916199' , '-82.7712197327' , '1' , '0'
);

3) Following this site I created a temporary table and loaded the matched nid's and lid's.

(reference file: insert_into_tempTable.sql)
INSERT INTO database.tempTable(

nid, lid
)

VALUES (

'7958' , '4945' ) ,
('8003' , '5105' ) ,
('7899' , '5112' ) ;

I then transferred this data over to the proper table:
(reference file: update_researchSite_lids.sql)
UPDATE database.content_type_research_site , database.tempTable
SET content_type_research_site.field_research_location_lid = tempTable.lid
WHERE content_type_research_site.nid = tempTable.nid

4) After step 3, all of the sites mapped properly.

5) I also noticed that the LID's showed up in one additional table. I don't know what this table does, but I felt it prudent to insert the relevant information into that table as well. (reference file: insert_into_location_instance.sql)


INSERT INTO database.location_instance (
nid, vid, uid, genid, lid
)
VALUES(

'0' , '0' , '0' , 'cck:field_research_location:7958' , '4945' ) ,
('0' , '0' , '0' , 'cck:field_research_location:8003' , '5105' ) ,
('0' , '0' , '0' , 'cck:field_research_location:7899' , '5112' ) ,

Posted by kkwaiser at March 11, 2010 08:31 AM

Comments

Login to leave a comment. Create a new account.