Tuesday, February 14, 2017

Using the Mycenaean Atlas Project (MAP)



The Mycenaean Atlas Project is a database which accurately maps more than 1600 Mycenaean find spots.  Because there are lat/lon pairs for every geographical point the data in this database can be displayed with map products such as Google Earth or GIS products like Carto and others. 

Querying the MAP database:
Select pk, pn, lat,lon from site where region = 'Laconia';


This MAP database is a relational database.  The data is stored in several different tables which are linked or connected through a primary key.  Since the fundamental data concept in this database is Mycenaean find spots it makes sense that each Mycenaean find spot has its own unchanging primary key value.  Any table in the database that has information about a Mycenaean find spot is accessed through the find spot primary key. 

FIELDS in the 'Site' TABLE

The fundamental table in this database is the ‘site’ table.  There is one unique record in the ‘site’ table for each find spot.  The record contains the following fields:

pk: the unique primary key designating this site

pn: (‘place name’) An alpha-numeric string with a pronounceable name for the site, e.g., ‘Mycenae’, ‘Tiryns’, ‘Nestor’s Palace’, ‘Pallantion’.

Lat: the latitude field is a floating-point number with at least six digits in the fractional part.  In the DB it is defined as ‘DOUBLE’.

Lon: the longitude field is a floating-point number with at least six digits in the fractional part.  In the DB it is defined as ‘DOUBLE’.

Acc: ‘Accuracy’.  This is a value that expresses the degree of confidence in the lat/lon pair.  See this for a full explanation.

Alt: Altitude in meters.  These altitudes in meters were derived from Google Earth.

Type: A single word identifier that gives a general characterization for the site.  The most common types are: ‘Hab’, ‘Cem’, and ‘Ceramics’.

Comment:  A character field that can be used for information that clarifies the site record.  Ordinarily this field is null.

Region: This is a character field of up to 45 characters.  It is used to designate the general geographic area in which the site is located.  It holds such values as ‘Laconia’, ‘Attica’, ‘Argolid’, ‘Messenia’, etc.

SAMPLE QUERIES

These nine fields are all that is required to generally characterize a find-spot.  With just this table it is already possible to write queries such as:

1.select * from site;              // returns all info in the table

2. select pk, pn, lat, lon from site;            // returns a file or all sites and which is suitable for importing into Google Earth

3. select pk, pn, lat, lon from site where region = ‘Laconia’;       // returns a file, suitable for importing into Google Earth which shows all Mycenaean sites in Laconia.

4. select pk, pn, lat, lon from site where alt < 50;   // returns a file showing Mycenaean sites close to a sea shore. 

Habitations close to the shore.  Query is:
select pk, pn, lat, lon from site where alt < 50;



5. select avg(alt) from site where type = ‘Hab’;      // returns the average altitude of a Mycenaean habitation.
Is the average burial above or below the average habitation?  Use query 6  along with query 5:

6. select avg(alt) from site where type = ‘Cem’;

The following illustration shows those sites which range from 20 m. below to 20 m. above the average altitude of 191 m.

Mycenaean sites at about the average elevation of 191.4 m:
Query: select pk, pn, lat, lon from site where alt > 171.4 and alt < 211.4;

7. select count(*), region from site group by region;    // This query will return a table with the number of Mycenaean find spots in each region.  When you execute this query the first few rows will look like this:

318, Messenia
189, Argolid
137, Boeotia
105, Dodecanese
94, Achaea
81, Laconia
61, Attica
60, Ionian Islands

The other tables in the database support the 'site' table.  By adding those tables we can write much more sophisticated queries.  I will describe those tables and their potential interactions in follow-on posts.

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 to 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.

If you like these posts then please follow me on Twitter (Squinchpix) or on Google+   (Robert Consoli)
Facebook?  Sorry.I.just.can't.

No comments:

Post a Comment