Tod’s Page
April 20, 2008
Entrance and the Whole Earth Chart
The MySQL Conference was really a hoot. This was the first time Entrance has had a booth in it, and we were often “stacked” with people watching the demos and asking questions. Lots of fun. Larry Cafiero, the President of the Cabrillo LUG brought his laptop loaded with an Ubuntu beta and Entrance, Shane had a laptop with Windows on it, and I had a MacBook, so we were truly cross platform.
We hit it off with several of the other exhibitors, and especially with Mike Pitarro from the SnapLogic booth. That led to same brain storming, and the next thing you know we had SnapLogicX, a proof-of-concept EntranceExtension demonstrating that we can connect to them.
Lenz Grimmer, the MySQL Community Relations Advisor, had sound advice for solving some of the documentation problems we face, and walked me over to some of the open source vendors who may have solutions. We’re starting with Drupal.
Mark Mattthews gave a great session talk about the MySQL JDBC driver, describing techniques for hooking into the driver, eg. to see events during the connection and statement lifecycles. I’m planning to try them out, let me know if you do.
My biggest disappointment was that I never hooked up with Roland Bouman, I missed him at the booth,
and my biggest blunder was failing to recognize a celebrity blogger who came by (I learned later)
I was impressed when Jonathan Schwartz showed plots of Solaris and MySQL downloads in his MySQL Conference Keynote Speech: I wondered if I could make something similar using Entrance, and the answer, it turns out, is “Yes!”. In the remainder of this note, I’ll show how you can do it too.
This plot shows the IP locations of about 800 distinct, recent visitors to the Entrance site:
... and this plot shows shows the same IP locations against a black background:
After drawing the two charts on tabs in Entrance you can switch between them using the Meta-J and Meta-K keys, to achieve an effect something like the dissolve in Jonathn Schwartz’s keynote. Of course, you can always export them as PNG files and then use a presentation package for a nicer one.
I made the first chart using this script:
plot bitmapchart
x, filled red circle, white circle
with
scale x -180 180 45
scale y -90 90 45
gray gridlines
bitmap "/Users/todlandis/proj/maps/PathfinderMap1024.jpg"
select lon, lat, lat
from ACCESS_IPS;
and the second using this one:
plot xychart
x, filled red circle, white circle
with
scale x -180 180 45
scale y -90 90 45
gray gridlines
background black
frame 0 0 1024 512
select lon, lat, lat
from ACCESS_IPS;
BitmapChart is a new chart that will appear in the Entrance version we ship next week. BitmapCharts are XYCharts with an associated bitmap and a rule for mapping (x,y) coordinates onto the bitmap. There is a little about how it was implemented here.
The new version of Entrance will support BitmapChart as a scriptable chart type. This simple extension ties in beautifully with something we have planned for MySQL WKT columns. It will be online this Monday or so at the Entrance home page.
Mapping IP Addresses to Lat, Long
Here are some of the details...
I used the “lite” data tables available from the GeoIP Free/Open Source area. I imported their CSV files into Entrance using the Tools | Import... dialog, selecting type “Comma Separated” and selecting “First line contains column names”. As I recall, one had a heading line, and I needed to check “Skip heading”, but you may have to experiment.
Here is the script I used to parse IP addresses and map them to ‘locid’ values:
-- drop table if exists ACCESS_IPS;
create table ACCESS_IPS (
ip varchar(255),
w int default null,
x int default null,
y int default null,
z int default null,
ipnum long,
locid long
);
-- parse ip addresses and calculate 'ipnum'
-- from a comment by "name withheld" at:
insert into ACCESS_IPS(ip)
SELECT distinct ip from ACCESS;
update ACCESS_IPS
set w = SUBSTRING_INDEX( `ip` , '.', 1 );
update ACCESS_IPS
set x = SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) ;
update ACCESS_IPS
set y = SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) ;
update ACCESS_IPS
set z = SUBSTRING_INDEX( `ip` , '.', -1 );
-- formula from http://www.maxmind.com/app/csv
update ACCESS_IPS
set ipnum = 16777216*w + 65536*x + 256*y + z ;
update ACCESS_IPS
set locid = (select locid from maxmind.GEOLITECITY_BLOCKS
where ipnum >= startipNum and ipnum <= endipNum limit 1);
update ACCESS_IPS
set lat = (select latitude from maxmind.geolitecity_location as a
where a.locid = ACCESS_IPS.locid limit 1),
lon = (select longitude from maxmind.geolitecity_location as b
where b.locid = ACCESS_IPS.locid limit 1)
;
You’ll want an index on startipNum and one on locid. Maybe the SQL experts will tell us what else will help to make them faster (especially that first script). Enjoy!
Earlier posts:
White Paper: Entrance: A Data Analysis Tool Written In Java
Copyright (c) 2008 Tod Landis, All Rights Reserved
MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.
Java is a trademark or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.