Bulk import GPS Coordinates into Google Maps

[peterkuric]

Hi

I'm just setting up the Google Map function in Scrutinizer and I was wondering if there is a bulk import method for the latitude/longitude details for each object as it is quite time consuming and prone to human error when there 500+ hosts to configure manually.

I'd even be happy updating via the mysql command line however I can't seem to find the database table that this data lives in.

Any ideas?

[mkrygeri]

I have a script That I could modify to do this if you want.
Do you have the sysLocation set on all your routers set to values that could be looked up in Google? e.g. 1060 W. Addison, Chicago Il" or "Bangor, Maine"?

I could also easily modify it do pull in a CSV that contains Exporter IP addresses and physical addresses like the following:

Code: Select all

"10.1.1.1","1060 W. Addison, Chicago Il"
"10.1.1.2"."Bangor, me"


If you want to just try this yourself, the table you want to update is plixer.membership. It looks like this:

Code: Select all

mysql> select * from plixer.membership ;
+-----------+----------+-----------------+-----------------+------+------+---------+--------+
| object_id | group_id | x               | y               | lng  | lat  | address | hidden |
+-----------+----------+-----------------+-----------------+------+------+---------+--------+
|         1 |        1 | 50.000000000000 | 50.000000000000 | NULL | NULL | NULL    |      0 |
|         2 |        1 | 50.000000000000 | 50.000000000000 | NULL | NULL | NULL    |      0 |
+-----------+----------+-----------------+-----------------+------+------+---------+--------+


The object_id is the id assigned to the exporter in the plixer.exporters table.

You would update the table like this:

Code: Select all

update plixer.membership set lat='45.0963338',lng='-94.4101481' where object_id = '1'


Hope this helps! Let Me know if you would like to check out that script!

Mike Krygeris

[peterkuric]

Thanks Mike!

I've now successfully imported all my GPS coordinates using the update query and have a map of Australia with my sites scattered across it, looks great.

For those wondering how the table relationships work for this stuff this query should give you an idea:

Code: Select all

select s.sysname,m.lat,m.lng from snmpsystem s,membership m,exporters e where m.object_id = e.object_id and e.exporter_id = s.device_id;

The only problem is it takes 2 whole minutes for the map to load! So its just a grey screen whilst loading. The page appears to refresh every 5mins so I'm getting about 3mins of map time in any 5min block! =(

This sucks because it's on a big screen in our NOC that everyone can see.

Has anyone encountered this problem before? There's about 400 nodes on my map if that helps

[mkrygeri]

Hi peterkuric,
I've spoken with a developer about this and they are looking into it. I'll let you know what we find.
I'm curious, have you tried this on multiple browsers? Is there a difference in load time between Firefox, Chrome and IE? I'm wondering if this is more of a js issue or a back-end issue.

Thanks,
Mike Krygeris

[peterkuric]

Hi Mike

I've only tried with Firefox and IE and its pretty much the same.

I've also noticed the CPU load on the server is fairly high.

Cheers
Peter

[DMalt]

I have a script That I could modify to do this if you want.
Do you have the sysLocation set on all your routers set to values that could be looked up in Google? e.g. 1060 W. Addison, Chicago Il" or "Bangor, Maine"?

Hope this helps! Let Me know if you would like to check out that script!

Mike Krygeris

If the offer is still there, the script would be great! I have all the addresses but not the GPS coordinates.

If it is more specific or better to do GPS, does anyone have a suggestion on an easy way to convert a batch (over 50) of addresses to GPS coordinates?

Thanks for the assistance,
David Maltenfort

[BenjaminM]

Hi DMalt,

Indeed there is a way to convert addresses to coordinates. A good site to achieve is http://www.gpsvisualizer.com/geocoder/

You can input multiple addresses one line at a time and it will convert them all to GPS coordinates.

Thanks,
Ben

[DMalt]

Ok, I have the GPS coordiates. Now the probelm is that I am a complete MYSql newb. I'm not sure what to do next. I see that I should run:

update plixer.membership set lat='45.0963338',lng='-94.4101481' where object_id = '1'

Though I'm not sure on:
A) object_id
B) what format a file should be
C) how to execute the file once I have made it

Thanks for the assistance in advance.

David

[mkrygeri]

The object ID is assigned in the plixer.exporters table.
A simple query to to get that list would be

Code: Select all

select object_id,inet_b2a(exporter_id) from plixer.exporters;

The format of the file should be a .sql file that can be imported into mysql using the mysql, although these are queries I would just paste in at the mysql prompt.

mysql -u root -p plixer < data.sql

Make sure there is a semicolon at the end of each query

Code: Select all

update plixer.membership set lat='45.0963338',lng='-94.4101481' where object_id = '1';

If you are unsure about mysql queries, make sure you back up your plixer database since the changes are not reversible.