The Arachne CMS seals database

I learned from the Aegeanet bulletin board that the Arachne CMS databases are online.  This includes a very large engraved seals database.

I have very little interest in engraved seals but I did go to this site anyway and looked at it.  I learned that the Heidelberg University Seals Database numbers about 13000 entries.  They do not allow access to their database except through a browser.   To do serious research work you’d have to look at every one of the 13,000 entries and you’d have to painstakingly recreate your seals DB from theirs one entry at a time.  Let’s say you can do two per hour.  That amounts to 6500 man hours.  There are only 2250 man hours in a work year.  That means that anyone who wants to use the Arachne seals DB for serious research has to spend 2.9 man years or 34.67 months in analysis and db building.   Along with all the errors that hand copying entails.

Which is an  unnecessary time expenditure because anyone who wants the Arachne seals database can have the entire thing with no more time investment than a couple of days to write a piece of software.   

How is that possible?

Well, first of all, it’s the internet and the internet is notoriously open.  It is open especially for benign purposes which this is.  But, second, the Arachne people have a sloppiness in their website which is that they show all the parameters to a page call right on their url line.  The result is that that url can be programmatically manipulated to allow you to download one page after another until you’ve downloaded every one of their pages.  The whole download process (images and all) would take, at most, a few minutes.  Once downloaded each page can be parsed for its constituent info (let me emphasize that the information being dumped is exactly the information that the web site is designed to provide in the first place and analyzing this page is exactly what your favorite web browser already does).  After download a series of sql statements can be generated which can create a customized form of their database on the local machine.  All of this can be done with a single program so that the sequence is ‘web pages in; SQL statements out’.

It would require that the would-be downloader write a program that is, in essence, a tiny customized browser that is designed just to read Arachne web pages.  That web browser can then parse the string coming from Arachne and write out sql statements that encapsulate the info of interest.  Once the sql statements are executed the database will be magically recreated on the local machine.

But let’s get back to that part about the sloppiness.  What does that look like?

A typical Arachne seals page

In this picture we see a typical Arachne seals page.   The browser I’m using, just for the record, is Chrome (Version 58.0.3029.110 (64-bit)).  See that red box towards the bottom?  That’s the information that you want to copy and put in your own database.  And the maxim is that if something is visible on your computer then it’s ON your computer and, if you can get access to it, you can do anything you want with it.  And, if you look at that little red circle at the top, you’ll see the number 495 there followed by an ampersand (&).  If you could change that number to 496 and reload the page then Arachne would serve the next image to your machine.  If you put that in a loop in a program then Arachne would serve ALL the images to your machine.  And I call this a sloppiness because those parameters don’t have to be visible.  I think that if Arachne really wanted to force you to use an approved browser then they should have used the POST method instead of GET for transmitting parameters.  That would make the passed parameters invisible.

What about the information in the red box?  How do you get hold of that?  Let’s look at a piece of the page string that is served by the Arachne servers.  This is the exact same string served to your regular browser and to see this, just use your browser to go to one of the Arachne web pages and then press the right mouse button on your mouse.  Then select 'View Source'.

...border="0" galleryimg="no"/></a><div style="font-size: 14px; padding: 3px; font-weight:bold;">Informationen</div><div class="info_wrap"><h3 class="info">Aufbewahrung: </h3><div class="info">Paläpaphos, Zypern, römisches Kammergrab.</div><div class="info">- Ortsangabe ist Fundort -</div><div class="info">Nikosia, Zypern, Cyprus Museum.</div><div class="info">- Ortsangabe ist Aufbewahrungsort -</div></div><div class="info_wrap"><h3 class="info">Herkunft: </h3><div class="info">Fundland: Zypern</div><div class="info">Fundort: Paläpaphos, CY</div><div class="info">Flur/Grabungsstätte: römisches Kammergrab</div><div class="info">Fundstelle: nein</div><div class="info">Platztypus: Grab</div><div class="info">Kontextdatierung: römisch</div><div class="info">Bereich: Zypern</div></div><div class="info_wrap"><h3 class="info">Form: </h3><div class="info">Siegelform: Lentoid</div><div class="info">Zahl der Siegelflächen: 1</div><div class="info">Wölbung der Siegelfläche: konvex</div><div class="info">Umriss der Siegelfläche: rund</div><div class="info">Dekor der Nebenseite: nein</div><div class="info">Art der Durchbohrung: diametral</div></div><div class="info_wrap"><h3 class="info">Material und Technik: </h3><div class="info">Material: Chalcedon?</div><div class="info">Bearbeitungstechnik: Rad, Rundperl, Tubus</div><div class="info">Sonstige Merkmale: nein</div></div><div class="....

Portion of the page string for an Arachne Seals web page

This string is the portion that creates the info box which I outlined in red above.  I changed some of the fields to red (I think that there are about 40 fields of interest) to show where they are.  The first ‘Fundland’ shows that this seal was found in Cyprus.  The second ‘Fundort’ gives you the location of the find city, namely Palaipaphos on Cyprus.  And so forth.  (And somewhere in here is the link to the high-resolution image of the seal.  Arachne snottily tells you that if you're just a casual user then you can only get the low-res version of the image  - or so they think.)  I emphasize that this page string is exactly the string that your Chrome or Firefox browser actually is sent by Arachne and from which your browser creates the web page that you see.  We just need a more efficient way to extract the data in a usable form and then create SQL statements out of it.  That's what's nice about this.  It is NOT a hack.  It does NOT try to change their data.  It does NOT attempt to modify their code to do something they don't want it to do.  It's merely a different way of consuming the product that they already WANT to deliver.

Much of web programming is done in a language called PHP.  PHP supports a call (leaving cURL completely out of the picture because we don’t need anything so sophisticated) called file_get_contents().  The parameter to this call is the URL whose page you are interested in acquiring.  When you execute this call the server on the Arachne machine returns the string representation of the page.  Your own favorite browser does exactly this except with much more powerful utilities.  When you get the page string back you can then do what I was suggesting which was to parse the page and write out the information in the form of SQL statements.  Writing out the SQL statements is trivial.  The whole program looks like this:

establish first URL

MAIN LOOP

web_page = file_get_contents(URL);

web_array = explode('><div class="info">', Web_page);

    FOR (every element of web_array)

            {

    IF start of element is a key word like ‘Fundort’ or ‘Fundstelle’                 then extract the argument and store it in a unique variable,                1..n

    }

Generate the SQL statement: e.g. “insert in seals values (‘Unique var 1’, ‘Unique var 2’, … ‘Unique var n’);”;

       END FOR LOOP

       update the URL to point to the next page.

END MAIN LOOP;

It’s a little more sophisticated than that.  The dates have to be extracted and placed in their own table since there can be a variable number of dates for each seal.   Basically, though, that’s all there is to it.  Now you may not want to do this but don't forget that this is always possible on the internet and for many more scholarly databases than just Arachne.  These internet tools are far more powerful than the uninitiated suppose.  What I suggest to Arachne is, if they really want to restrict access to only approved browsers, that they protect their URL line better.

And don't write back to me and say that this can't be done.  I got my version of this program running along with the associated database in about two days.  

Just for the exercise.

The GIS and the Front End

What is a geographic information system (or GIS)?   In its simplest form a GIS is a software package meant to facilitate the creation and display of maps along with analysis and display of any data that includes lat/lon pairs. 

There are some critical components in a GIS.  A database, for example, along with a ‘front-end’.  O.k. so what is a ‘front end’?  A ‘front-end’ is a software package that interfaces to a database and makes it possible to retrieve data and convert it into a map.  The second thing a ‘front-end’ must do is allow the user to specify geographical points that then get stored in the database for later retrieval/mapping.  In other words a front-end acts to gather and store data in a DB and it also gets data from the DB and displays it as a map or as tables. That’s really all there is to it.

And a GIS can be surprisingly simple.  Google Earth makes a respectable ‘front-end’ but you need to use it in conjunction with a database in order to have anything like full ‘GIS’ functionality. So that although the two parts, Google Earth and The Database, are separate and not automatically interconnecting, you can still use them together even though you store what you retrieve from the database as an intermediate flat file before you then import it into Google Earth.  It looks like this:

I’ve numbered the arcs here for easy reference.  In step 1 we get a lat/lon pair from Google Earth and we attach it to a place name and id with some .sql query like this:

insert into site (

place_key,

name,

lat,

lon,

region)

values (

‘C9999’,

‘Tholos’,

36.7654,

22.4536,

‘Messenia’);

In step 2 we take that .sql statement and enter it into the database.

We repeat steps 1 and 2 as often as we need to in order to generate a database of place marks.  When that’s done we have a table (the ‘site’ table in this case) which holds our data and which was derived from Google Earth. 

Later we want to come back and display the site table on a map.  In step 3 we execute the following query in the database:

select * from site;

…And we save the result as a comma-separated file (or .csv).  A good database, such as MySQL, will allow you to do this. 

In step 4 we import the .csv into Google Earth using GE’s ‘import’ feature (not the ‘open’ feature).  Importing into Google Earth is described here.

And the result is that you now have your data displayed on a map.  Of course you can modify the site table in the meantime, amplify it from different sources, etc., etc.

Once the data is displayed in Google Earth you can also save it as a .kml (.kmz is a .kml in zip-file form).  The .kml is a very useful file type since most GIS products support it.  That deserves a modified picture:

Here, in Step 5, I show the Google Earth capability of exporting either .kml or .kmz files.

And although this method seems a little clunky (because of the hand derivation of .sql statements in step 1 or the intermediate .csv file in steps 3-4) it’s still a perfectly reasonable way to work and, in fact, all the Mycenaean Atlas Project so far, thousands of points, has been implemented in just this way.    Why does this work?  It works because the work of finding points can be very much greater than the relatively trivial operations of hand-entering them into a database.  As long as a project is small or the data entry is a small part of the total cost we don’t need anything more elaborate.

Another method of extracting information from the database (besides extracting flat files from it) is to write custom software for that purpose.  For example, in the Mycenaean Atlas Project the database contains lots of material that you wouldn’t ordinarily display on a map, like bibliographic information.  And yet the bibliographic material supports the rest of the database; it is the warrant, in a sense, for the data’s accuracy.  For that type of material you’d want to generate, not a map, but a report.  For that purpose you could just use sql:

select * from fnb where pk = ‘C237’;   // this query would return bibliographic citations for site ‘C237’.

And sql can be made very elaborate and, ordinarily, .sql queries can work for this purpose perfectly well.

But let’s say that you want to dump, in a nicely formatted form (not just a table), all the bibliographic material and show its connections to the rest of the DB.  For that purpose simple sql might not do so well.  

 For writing a fancy report you need to write a program.  I expand our diagram to show that possibility.

Here I show a software interface connecting to the database and generating (in arc 6) a text report of some kind.  There are several good software interpreters that make connecting to the DB simple.  The PHP language is a reasonable choice.  It is very widely used in Internet applications to serve a site’s online database.  In fact, if we were to put the Mycenaean Atlas Project online the Text Report attached to arc 6 would actually consist of .html pages.  To download your free, widely-used, standard version of PHP just click here. 

The Mycenaean Atlas Project actually does use a couple of self-developed programs in PHP in order to generate full reports of nearly the entire contents of the database.  (These reports are in .pdf form, and they're yours for the asking).  The first is a complete report on all the Bronze Age sites; the second is a complete dump of the Features table.  (The Features table consists of non-Bronze Age sites which are mentioned in the gazetteer and other literature and which you need to know about in order to make sense of that literature.  ‘Features’ include towns, signs, churches, monasteries, regions, chapels, streams, bridges, etc. etc.)  

By way of parenthesis there is a .php class that allows you to build .pdfs directly.  Find it described here.  

What I've shown so far is a little over-elaborate. 

To simplify things we can get ourselves a front-end that can interface directly to the database and by eliminating the hand written .sql statements as well as the intermediate .csv file.  After all, once we’ve finished with hand-created .sql files and db-generated .csv files we don’t want them hanging around.  The Truth Model is in the Database and in the Database only.  The .sql and .csv files must be trashed once they’re used so as not to lead to confusion.

What front-end interfaces directly to the DB?

A correspondent of mine from Cambridge, England, shares that he is working on a project to map Bronze Age burial sites using QGIS as his Geographic Information System.

What is QGIS?  QGIS is a genuine, full-featured GIS that replaces nearly all the complicated stuff I’ve presented so far.  I’ll discuss it soon in a separate blog post.

~~~~~~~~~~~~~~~~

Anyone who would like to have a copy of the MAP database can send an e-mail to bobconsoli 'at' gmail.com or leave a comment on any of my posts.  To run the MAP database requires a SQL server running on your desktop computer.   MySQL is such a server and it is powerful, industry-standard, and free.  

I can and will make .kml or .kmz files, which can be opened directly in Google Earth, available to those who would like them.  

I can also create .csv files for people who would like to import Mycenaean Atlas Project data into Google Earth but would like it in tabular form.

Those who do not have a SQL server but would like the full database in .pdf form can have that for the asking.

If you like these posts then please follow me on Twitter (Squinchpix) or on Google+   (Robert Consoli)

Facebook?  Sorry.I.just.can't.