Posted by adamg on Mon, Nov 11, 2024 - 9:57am

Apologies in advance if this is a dumb question, but ...

I've started getting ready to convert my D7 site to Backdrop. So far, going well, except I have several years of D7 Location lat/lon variables to port.  Does anybody know if the import process will handle this OK, or if I have to import the values via mysql at the end?

Just in case I have to do the latter, I've started looking at how the two dbs store lat/lon values. 

Looks like the Backdrop location module puts them into a table with a different name than in the D7 version, called field_data_field_location, which is fine, but the Backdrop table has two additional fields, one field_location_geohash and field_location_geom (which is a blob).  I tried simply deleting the field_location_geohash value from the db entry for one node I'd created in Backdrop and the map still showed up correctly on the individual node and its pointer on a view built with Leaflet.  But what about the blob field (I'm not currently doing anything fancier than just plotting points on a map)? Or am I worrying too much?

Thanks!

Accepted answer

Heya @adamg

First of all...no need to apologize.  I was just trying to clarify what module(s) you were talking about so help would (hopefully) be more forthcoming.

I had to do a migration a long time ago along the lines you are describing, but not for lat/long.  I was able to accomplish it using just SQL.  I was lucky.

Personally speaking, I do not know if the BCMS Leaflet module or the BCMS Geolocation module has an "import" feature that allows it to migrate data that was originally created via the Drupal Location module.  Have you checked?

I also do not know if importing LAT/LONG "behind the scenes" via SQL statements is a good idea or not over the long term.  Considering the fact that the data schema appears to be different between them, with complex columns involved that may also have a requirement that data populate them, importing data via mitigating programming code (i.e. PHP or similar) may be a necessity, especially if computations or manipulations are required.

SQL is not a General Purpose Language (GPL) like PHP.  

SQL is a Domain Specific Language (DSL), and may not be powerful enough.

Assuming the BCMS Geolocation module is whereby the lat/long data is stored in the system, are you technical enough to extend the BCMS Geolocation module with a little data import module to bring in data from the Drupal Location module?

I daresay that is what you are actually asking for; not a platform-wide import capability, but rather a module2module data migration capability.

(hope I got that right).

g.
----

 

Comments

Mon, Nov 11, 2024 - 6:42pm by herb

Leaflet on its does not provide fields, from what I understand. Which module do you have installed in Backdrop to provide fields? Is it geofield? And we're you using Location module in Drupal 7?

Mon, Nov 11, 2024 - 7:17pm by adamg

Apologies for not being clear, and getting the name of the Backdrop module wrong!

On D7, I'm using a combination of Location and GMaps (and Views) to store and display locations on maps.

On a test Backdrop site, I've installed Geolocation and Leaflet. I created a Geolocation field (called "location") on one content type to collect the latitude and longitude. In the default display mode for the content type, I have the format set to Leaflet. I then created a View to collect display pins for all the nodes of a particular content type, with the format set to Leaflet Maps. It works great, no problems!

My question, and maybe I should have waited to try actually importing my D7 site first, is if the existing lat/lon data from D7 will be able to flow into the relevant Backdrop table during import or if I will have to do something in mysql itself to transfer the data (and apologies if the answer is: Try the import first).

Mon, Nov 11, 2024 - 8:30pm by Graham Leach

Heya @adamg

First of all...no need to apologize.  I was just trying to clarify what module(s) you were talking about so help would (hopefully) be more forthcoming.

I had to do a migration a long time ago along the lines you are describing, but not for lat/long.  I was able to accomplish it using just SQL.  I was lucky.

Personally speaking, I do not know if the BCMS Leaflet module or the BCMS Geolocation module has an "import" feature that allows it to migrate data that was originally created via the Drupal Location module.  Have you checked?

I also do not know if importing LAT/LONG "behind the scenes" via SQL statements is a good idea or not over the long term.  Considering the fact that the data schema appears to be different between them, with complex columns involved that may also have a requirement that data populate them, importing data via mitigating programming code (i.e. PHP or similar) may be a necessity, especially if computations or manipulations are required.

SQL is not a General Purpose Language (GPL) like PHP.  

SQL is a Domain Specific Language (DSL), and may not be powerful enough.

Assuming the BCMS Geolocation module is whereby the lat/long data is stored in the system, are you technical enough to extend the BCMS Geolocation module with a little data import module to bring in data from the Drupal Location module?

I daresay that is what you are actually asking for; not a platform-wide import capability, but rather a module2module data migration capability.

(hope I got that right).

g.
----

 

Tue, Nov 12, 2024 - 6:51am by adamg

Thanks, that was helpful! Not at all technical, but it gives me some ideas on what to play around with.

Tue, Nov 12, 2024 - 3:03pm by Graham Leach

Hello @adamg,

I had a look at the BCMS Geolocation module .install file, which defines the schema of its database tables.

/**
* Implements hook_field_schema().
*/
function geolocation_field_schema($field) {
 $columns = array(
   'lat' => array(
     'description' => 'Stores the latitude value',
     'type' => 'float',
     'size' => 'big',
     'not null' => TRUE,
     'default' => 0,
   ),
   'lng' => array(
     'description' => 'Stores the longitude value',
     'type' => 'float',
     'size' => 'big',
     'not null' => TRUE,
     'default' => 0,
   ),
   'lat_sin' => array(
     'description' => 'Stores the sine of latitude',
     'type' => 'float',
     'size' => 'big',
     'not null' => TRUE,
     'default' => 0,
   ),
   'lat_cos' => array(
     'description' => 'Stores the cosine of latitude',
     'type' => 'float',
     'size' => 'big',
     'not null' => TRUE,
     'default' => 0,
   ),
   'lng_rad' => array(
     'description' => 'Stores the radian longitude',
     'type' => 'float',
     'size' => 'big',
     'not null' => TRUE,
     'default' => 0,
   ),
 );
 $indexes = array(
   'lat' => array('lat'),
   'lng' => array('lng'),
 );
 return array(
   'columns' => $columns,
   'indexes' => $indexes,
 );
}

Looks like any data migration routine MUST insert a value into every one of the above-defined columns.

Also, the abbreviation for longitude is non-standard.

Abbreviations:  latitude and longitude

Here's the schema of the Drupal Location module:

/**
* Implements of hook_schema().
*/
function location_schema() {
 $schema['location'] = array(
   'description' => 'Locational data managed by location.module.',
   'fields' => array(
     'lid' => array(
       'description' => 'Primary Key: Unique location ID.',
       'type' => 'serial',
       'unsigned' => TRUE,
       'not null' => TRUE,
     ),
     'name' => array(
       'description' => 'Place Name.',
       'type' => 'varchar',
       'length' => 255,
       'default' => '',
       'not null' => TRUE,
     ),
     'street' => array(
       'description' => 'Street address, line 1.',
       'type' => 'varchar',
       'length' => 255,
       'default' => '',
       'not null' => TRUE,
     ),
     'additional' => array(
       'description' => 'Street address, line 2.',
       'type' => 'varchar',
       'length' => 255,
       'default' => '',
       'not null' => TRUE,
     ),
     'city' => array(
       'description' => 'City.',
       'type' => 'varchar',
       'length' => 255,
       'default' => '',
       'not null' => TRUE,
     ),
     'province' => array(
       'description' => 'State / Province code.',
       'type' => 'varchar',
       'length' => 16,
       'default' => '',
       'not null' => TRUE,
     ),
     'postal_code' => array(
       'description' => 'Postal / ZIP code.',
       'type' => 'varchar',
       'length' => 16,
       'default' => '',
       'not null' => TRUE,
     ),
     'country' => array(
       'description' => 'Two letter ISO country code.',
       'type' => 'char',
       'length' => 2,
       'not null' => TRUE,
       'default' => '',
     ),
     'latitude' => array(
       'description' => 'Location latitude (decimal degrees).',
       'type' => 'numeric',
       'precision' => 10,
       // @todo Shouldn't these all be 7?
       'scale' => 6,
       'not null' => TRUE,
       'default' => 0.0,
     ),
     'longitude' => array(
       'description' => 'Location longitude (decimal degrees).',
       'type' => 'numeric',
       'precision' => 10,
       'scale' => 6,
       'not null' => TRUE,
       'default' => 0.0,
     ),
     'source' => array(
       'description' => 'Source of the latitude and longitude data (Geocoder, user entered, invalid, etc.)',
       'type' => 'int',
       'size' => 'tiny',
       'default' => 0,
       'not null' => TRUE,
     ),
     // @@@ Historical civicrm field that isn't applicable to location, I think..
     'is_primary' => array(
       'description' => 'Is this the primary location of an object? (unused, civicrm legacy field?).',
       'type' => 'int',
       'size' => 'tiny',
       'default' => 0,
       'not null' => TRUE,
     ),
   ),
   'primary key' => array('lid'),
 )

Observations:

  • The shape of the data between the two modules is very different
  • While the source table contains much more information, the target table actually requires data that the source table does not contain.

Considering that there are NOT NULL restrictions being imposed, it looks to me like a custom program is the only way to go.  If you try to do a bulk move, the database is going to complain about NULL entries.

Here's how I would do it:

  • Use command-line tools to export the source table to a CSV
  • Write an import routine that reads the CSV, one line at a time
  • Calculate missing values during the import
  • Insert the complete records in the target database

N.B.:  The target database requires COS and RAD calculations be done on lat/lng prior to the INSERT operation.

Why COS matters in geolocation

Why RAD matters in geolocation

I have over 20 years of experience with GIS systems, including real-time asset monitoring, route calculation and traffic optimization.

Most of my experience was gathered doing work in the logistics, long-haul trucking, ULD management and airline industries.

Hope the above helps you in your journey.

g.
----