function query_bm($ref)
{
$textRefs = array();
$db = sparql_connect("http://collection.britishmuseum.org/sparql");
if (!$db) {
print sparql_errno() . ": " . sparql_error() . "\n";
exit;
}
sparql_ns("bmo", "http://collection.britishmuseum.org/id/ontology/");
sparql_ns("ecrm", "http://erlangen-crm.org/current/");
sparql_ns("object", "http://collection.britishmuseum.org/id/object/");
$sparql = 'SELECT DISTINCT ?coin ?text WHERE {
?coin ecrm:P70i_is_documented_in <REF> . FILTER regex(str(?coin), "C[A-Z]{2}[0-9]+") .
?coin a ecrm:E22_Man-Made_Object ;
ecrm:P50_has_current_keeper <http://collection.britishmuseum.org/id/thesauri/department/C> ;
bmo:PX_display_wrap ?text . FILTER regex(?text, "Bibliograpic\\\\sreference")
} LIMIT 10';
$result = sparql_query(str_replace('REF', $ref, $sparql));
if (!$result) {
print sparql_errno() . ": " . sparql_error() . "\n";
exit;
}
$fields = sparql_field_array($result);
while ($row = sparql_fetch_array($result)) {
$textRefs[$row['coin']][] = $row['text'];
/*foreach( $fields as $field )
{
$textRefs
}*/
}
return $textRefs;
}
public static function update_airports()
{
global $tmp_dir, $globalTransaction;
require_once 'libs/sparqllib.php';
$db = sparql_connect('http://dbpedia.org/sparql');
$query = '
PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX dbp: <http://dbpedia.org/property/>
PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT ?name ?icao ?iata ?faa ?lid ?latitude ?longitude ?airport ?homepage ?type ?country ?country_bis ?altitude ?image
FROM <http://dbpedia.org>
WHERE {
?airport rdf:type <http://dbpedia.org/ontology/Airport> .
OPTIONAL {
?airport dbo:icaoLocationIdentifier ?icao .
FILTER regex(?icao, "^[A-Z0-9]{4}$")
}
OPTIONAL {
?airport dbo:iataLocationIdentifier ?iata .
FILTER regex(?iata, "^[A-Z0-9]{3}$")
}
OPTIONAL {
?airport dbo:locationIdentifier ?lid .
FILTER regex(?lid, "^[A-Z0-9]{4}$")
FILTER (!bound(?icao) || (bound(?icao) && (?icao != ?lid)))
OPTIONAL {
?airport_y rdf:type <http://dbpedia.org/ontology/Airport> .
?airport_y dbo:icaoLocationIdentifier ?other_icao .
FILTER (bound(?lid) && (?airport_y != ?airport && ?lid = ?other_icao))
}
FILTER (!bound(?other_icao))
}
OPTIONAL {
?airport dbo:faaLocationIdentifier ?faa .
FILTER regex(?faa, "^[A-Z0-9]{3}$")
FILTER (!bound(?iata) || (bound(?iata) && (?iata != ?faa)))
OPTIONAL {
?airport_x rdf:type <http://dbpedia.org/ontology/Airport> .
?airport_x dbo:iataLocationIdentifier ?other_iata .
FILTER (bound(?faa) && (?airport_x != ?airport && ?faa = ?other_iata))
}
FILTER (!bound(?other_iata))
}
FILTER (bound(?icao) || bound(?iata) || bound(?faa) || bound(?lid))
OPTIONAL {
?airport rdfs:label ?name
FILTER (lang(?name) = "en")
}
OPTIONAL {
?airport foaf:homepage ?homepage
}
OPTIONAL {
?airport dbp:coordinatesRegion ?country
}
OPTIONAL {
?airport dbp:type ?type
}
OPTIONAL {
?airport dbo:elevation ?altitude
}
OPTIONAL {
?airport dbp:image ?image
}
{
?airport geo:lat ?latitude .
?airport geo:long ?longitude .
FILTER (datatype(?latitude) = xsd:float)
FILTER (datatype(?longitude) = xsd:float)
} UNION {
?airport geo:lat ?latitude .
?airport geo:long ?longitude .
FILTER (datatype(?latitude) = xsd:double)
FILTER (datatype(?longitude) = xsd:double)
OPTIONAL {
?airport geo:lat ?lat_f .
?airport geo:long ?long_f .
FILTER (datatype(?lat_f) = xsd:float)
FILTER (datatype(?long_f) = xsd:float)
}
FILTER (!bound(?lat_f) && !bound(?long_f))
}
}
ORDER BY ?airport
';
$result = sparql_query($query);
$query = 'TRUNCATE TABLE airport';
try {
$Connection = new Connection();
$sth = Connection::$db->prepare($query);
$sth->execute();
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
$query = 'ALTER TABLE airport DROP INDEX icaoidx';
try {
$Connection = new Connection();
$sth = Connection::$db->prepare($query);
$sth->execute();
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
$query_dest = "INSERT INTO airport (`airport_id`,`name`,`city`,`country`,`iata`,`icao`,`latitude`,`longitude`,`altitude`,`type`,`home_link`,`wikipedia_link`,`image_thumb`,`image`)\n\t\t VALUES (:airport_id, :name, :city, :country, :iata, :icao, :latitude, :longitude, :altitude, :type, :home_link, :wikipedia_link, :image_thumb, :image)";
$Connection = new Connection();
$sth_dest = Connection::$db->prepare($query_dest);
if ($globalTransaction) {
Connection::$db->beginTransaction();
}
$i = 0;
while ($row = sparql_fetch_array($result)) {
if ($i >= 1) {
print_r($row);
if (!isset($row['iata'])) {
$row['iata'] = '';
}
if (!isset($row['icao'])) {
$row['icao'] = '';
}
if (!isset($row['type'])) {
$row['type'] = '';
}
if (!isset($row['altitude'])) {
$row['altitude'] = '';
}
if (isset($row['city_bis'])) {
$row['city'] = $row['city_bis'];
}
if (!isset($row['city'])) {
$row['city'] = '';
}
if (!isset($row['country'])) {
$row['country'] = '';
}
if (!isset($row['homepage'])) {
$row['homepage'] = '';
}
if (!isset($row['wikipedia_page'])) {
$row['wikipedia_page'] = '';
}
if (!isset($row['name'])) {
continue;
}
if (!isset($row['image'])) {
$row['image'] = '';
$row['image_thumb'] = '';
} else {
$image = str_replace(' ', '_', $row['image']);
$digest = md5($image);
$folder = $digest[0] . '/' . $digest[0] . $digest[1] . '/' . $image . '/220px-' . $image;
$row['image_thumb'] = 'http://upload.wikimedia.org/wikipedia/commons/thumb/' . $folder;
$folder = $digest[0] . '/' . $digest[0] . $digest[1] . '/' . $image;
$row['image'] = 'http://upload.wikimedia.org/wikipedia/commons/' . $folder;
}
$country = explode('-', $row['country']);
$row['country'] = $country[0];
$row['type'] = trim($row['type']);
if ($row['type'] == 'Military: Naval Auxiliary Air Station' || $row['type'] == 'http://dbpedia.org/resource/Naval_air_station' || $row['type'] == 'Military: Naval Air Station' || $row['type'] == 'Military Northern Fleet' || $row['type'] == 'Military and industrial' || $row['type'] == 'Military: Royal Air Force station' || $row['type'] == 'http://dbpedia.org/resource/Military_airbase' || $row['type'] == 'Military: Naval air station' || preg_match('/air base/i', $row['name'])) {
$row['type'] = 'Military';
} elseif ($row['type'] == 'http://dbpedia.org/resource/Airport' || $row['type'] == 'Civil' || $row['type'] == 'Public use' || $row['type'] == 'Public' || $row['type'] == 'http://dbpedia.org/resource/Civilian' || $row['type'] == 'Public, Civilian' || $row['type'] == 'Public / Military' || $row['type'] == 'Private & Civilian' || $row['type'] == 'Civilian and Military' || $row['type'] == 'Public/military' || $row['type'] == 'Active With Few Facilities' || $row['type'] == '?ivilian' || $row['type'] == 'Civil/Military' || $row['type'] == 'NA' || $row['type'] == 'Public/Military') {
$row['type'] = 'small_airport';
}
$row['city'] = urldecode(str_replace('_', ' ', str_replace('http://dbpedia.org/resource/', '', $row['city'])));
$query_dest_values = array(':airport_id' => $i, ':name' => $row['name'], ':iata' => $row['iata'], ':icao' => $row['icao'], ':latitude' => $row['latitude'], ':longitude' => $row['longitude'], ':altitude' => $row['altitude'], ':type' => $row['type'], ':city' => $row['city'], ':country' => $row['country'], ':home_link' => $row['homepage'], ':wikipedia_link' => $row['wikipedia_page'], ':image' => $row['image'], ':image_thumb' => $row['image_thumb']);
//print_r($query_dest_values);
try {
$sth_dest->execute($query_dest_values);
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
}
$i++;
}
if ($globalTransaction) {
Connection::$db->commit();
}
echo "Delete duplicate rows...\n";
$query = 'ALTER IGNORE TABLE airport ADD UNIQUE INDEX icaoidx (icao)';
try {
$Connection = new Connection();
$sth = Connection::$db->prepare($query);
$sth->execute();
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
if ($globalDebug) {
echo "Insert Not available Airport...\n";
}
$query = "INSERT INTO airport (`airport_id`,`name`,`city`,`country`,`iata`,`icao`,`latitude`,`longitude`,`altitude`,`type`,`home_link`,`wikipedia_link`,`image`,`image_thumb`)\n\t\t VALUES (:airport_id, :name, :city, :country, :iata, :icao, :latitude, :longitude, :altitude, :type, :home_link, :wikipedia_link, :image, :image_thumb)";
$query_values = array(':airport_id' => $i, ':name' => 'Not available', ':iata' => 'NA', ':icao' => 'NA', ':latitude' => '0', ':longitude' => '0', ':altitude' => '0', ':type' => 'NA', ':city' => 'N/A', ':country' => 'N/A', ':home_link' => '', ':wikipedia_link' => '', ':image' => '', ':image_thumb' => '');
try {
$Connection = new Connection();
$sth = Connection::$db->prepare($query);
$sth->execute($query_values);
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
$i++;
/*
$query = 'DELETE FROM airport WHERE airport_id IN (SELECT * FROM (SELECT min(a.airport_id) FROM airport a GROUP BY a.icao) x)';
try {
$Connection = new Connection();
$sth = Connection::$db->prepare($query);
$sth->execute();
} catch(PDOException $e) {
return "error : ".$e->getMessage();
}
*/
echo "Download data from ourairports.com...\n";
$delimiter = ',';
$out_file = $tmp_dir . 'airports.csv';
update_db::download('http://ourairports.com/data/airports.csv', $out_file);
if (!file_exists($out_file) || !is_readable($out_file)) {
return FALSE;
}
echo "Add data from ourairports.com...\n";
$header = NULL;
if (($handle = fopen($out_file, 'r')) !== FALSE) {
$Connection = new Connection();
//Connection::$db->beginTransaction();
while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
if (!$header) {
$header = $row;
} else {
$data = array();
$data = array_combine($header, $row);
try {
$sth = Connection::$db->prepare('SELECT COUNT(*) FROM airport WHERE `icao` = :icao');
$sth->execute(array(':icao' => $data['gps_code']));
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
if ($sth->fetchColumn() > 0) {
$query = 'UPDATE airport SET `type` = :type WHERE icao = :icao';
try {
$sth = Connection::$db->prepare($query);
$sth->execute(array(':icao' => $data['gps_code'], ':type' => $data['type']));
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
} else {
$query = "INSERT INTO airport (`airport_id`,`name`,`city`,`country`,`iata`,`icao`,`latitude`,`longitude`,`altitude`,`type`,`home_link`,`wikipedia_link`)\n\t\t\t\t\t\t VALUES (:airport_id, :name, :city, :country, :iata, :icao, :latitude, :longitude, :altitude, :type, :home_link, :wikipedia_link)";
$query_values = array(':airport_id' => $i, ':name' => $data['name'], ':iata' => $data['iata_code'], ':icao' => $data['gps_code'], ':latitude' => $data['latitude_deg'], ':longitude' => $data['longitude_deg'], ':altitude' => $data['elevation_ft'], ':type' => $data['type'], ':city' => $data['municipality'], ':country' => $data['iso_country'], ':home_link' => $data['home_link'], ':wikipedia_link' => $data['wikipedia_link']);
try {
$sth = Connection::$db->prepare($query);
$sth->execute($query_values);
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
$i++;
}
}
}
fclose($handle);
//Connection::$db->commit();
}
echo "Download data from another free database...\n";
$out_file = $tmp_dir . 'GlobalAirportDatabase.zip';
update_db::download('http://www.partow.net/downloads/GlobalAirportDatabase.zip', $out_file);
if (!file_exists($out_file) || !is_readable($out_file)) {
return FALSE;
}
update_db::unzip($out_file);
$header = NULL;
echo "Add data from another free database...\n";
$delimiter = ':';
$Connection = new Connection();
if (($handle = fopen($tmp_dir . 'GlobalAirportDatabase.txt', 'r')) !== FALSE) {
//Connection::$db->beginTransaction();
while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
if (!$header) {
$header = $row;
} else {
$data = $row;
$query = 'UPDATE airport SET `city` = :city, `country` = :country WHERE icao = :icao';
try {
$sth = Connection::$db->prepare($query);
$sth->execute(array(':icao' => $data[0], ':city' => ucwords(strtolower($data[3])), ':country' => ucwords(strtolower($data[4]))));
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
}
}
fclose($handle);
//Connection::$db->commit();
}
echo "Put type military for all air base";
$Connection = new Connection();
try {
$sth = Connection::$db->prepare("SELECT icao FROM airport WHERE `name` LIKE '%Air Base%'");
$sth->execute();
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$query2 = 'UPDATE airport SET `type` = :type WHERE icao = :icao';
try {
$sth2 = Connection::$db->prepare($query2);
$sth2->execute(array(':icao' => $row['icao'], ':type' => 'military'));
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
}
return "success";
}
function query_bm($writer, $uri)
{
$db = sparql_connect("http://collection.britishmuseum.org/sparql");
if (!$db) {
print sparql_errno() . ": " . sparql_error() . "\n";
exit;
}
sparql_ns("thesDimension", "http://collection.britishmuseum.org/id/thesauri/dimension/");
sparql_ns("bmo", "http://collection.britishmuseum.org/id/ontology/");
sparql_ns("ecrm", "http://erlangen-crm.org/current/");
sparql_ns("object", "http://collection.britishmuseum.org/id/object/");
$sparql = "SELECT ?image ?weight ?axis ?diameter ?objectId ?hoard WHERE {\n OPTIONAL {<OBJECT> bmo:PX_has_main_representation ?image }\n OPTIONAL { <OBJECT> ecrm:P43_has_dimension ?wDim .\n ?wDim ecrm:P2_has_type thesDimension:weight .\n ?wDim ecrm:P90_has_value ?weight}\n OPTIONAL {\n <OBJECT> ecrm:P43_has_dimension ?wAxis .\n ?wAxis ecrm:P2_has_type thesDimension:die-axis .\n ?wAxis ecrm:P90_has_value ?axis\n }\n OPTIONAL {\n <OBJECT> ecrm:P43_has_dimension ?wDiameter .\n ?wDiameter ecrm:P2_has_type thesDimension:diameter .\n ?wDiameter ecrm:P90_has_value ?diameter\n }\n OPTIONAL {\n <OBJECT> ecrm:P1_is_identified_by ?identifier.\n ?identifier ecrm:P2_has_type <http://collection.britishmuseum.org/id/thesauri/identifier/codexid> ;\n rdfs:label ?objectId\n }\n OPTIONAL {\n <OBJECT> bmo:PX_display_wrap ?hoard . FILTER regex(?hoard, 'IGCH')\n }\n }";
$result = sparql_query(str_replace('OBJECT', $uri, $sparql));
if (!$result) {
print sparql_errno() . ": " . sparql_error() . "\n";
exit;
}
$fields = sparql_field_array($result);
while ($row = sparql_fetch_array($result)) {
foreach ($fields as $field) {
if (strlen($row[$field]) > 0) {
if ($field == 'hoard') {
preg_match('/IGCH\\s([0-9]+)/', $row[$field], $matches);
if (isset($matches[1])) {
$num = str_pad($matches[1], 4, "0", STR_PAD_LEFT);
$hoardURI = 'http://coinhoards.org/id/igch' . $num;
echo "Found hoard {$hoardURI}\n";
$writer->startElement('dcterms:isPartOf');
$writer->writeAttribute('rdf:resource', $hoardURI);
$writer->endElement();
}
} else {
switch ($field) {
case 'image':
$writer->startElement('foaf:depiction');
$writer->writeAttribute('rdf:resource', $row[$field]);
$writer->endElement();
break;
case 'objectId':
$writer->startElement('foaf:homepage');
$writer->writeAttribute('rdf:resource', "http://www.britishmuseum.org/research/collection_online/collection_object_details.aspx?objectId={$row[$field]}&partId=1");
$writer->endElement();
break;
case 'axis':
$writer->startElement('nmo:hasAxis');
$writer->writeAttribute('rdf:datatype', 'http://www.w3.org/2001/XMLSchema#integer');
$writer->text($row[$field]);
$writer->endElement();
break;
case 'weight':
$writer->startElement('nmo:hasWeight');
$writer->writeAttribute('rdf:datatype', 'http://www.w3.org/2001/XMLSchema#decimal');
$writer->text($row[$field]);
$writer->endElement();
break;
case 'diameter':
$writer->startElement('nmo:hasDiameter');
$writer->writeAttribute('rdf:datatype', 'http://www.w3.org/2001/XMLSchema#decimal');
$writer->text($row[$field]);
$writer->endElement();
break;
}
}
}
}
}
}
$input = $_REQUEST["term"];
$data = array();
// query the triple store looking for a match to $input
$query = 'SELECT DISTINCT ?webid, ?name, ?img WHERE {
?webid foaf:name ?name .
?webid foaf:nick ?nick .
?webid foaf:img ?img .
FILTER (regex(?name, "' . $input . '", "i") || regex(?nick, "' . $input . '", "i") || regex(?webid, "' . $input . '", "i"))
MINUS { ?webid a foaf:Person .
FILTER (regex(?webid, "nodeID", "i")) }
} LIMIT 10';
$result = $sparql->query($query);
if (!$result) {
die(sparql_errno() . ": " . sparql_error());
}
while ($row = sparql_fetch_array($result)) {
$json = array();
$json['webid'] = $row['webid'];
$json['name'] = $row['name'];
$json['img'] = $row['img'];
$json['label'] = '<table><tr>';
$json['label'] .= '<td><img width="30" src="' . $row['img'] . '"/></td>';
$json['label'] .= '<td><strong>' . $row['name'] . '</strong><br/>' . $row['webid'] . '</td>';
$json['label'] .= '</tr></table>';
$json['value'] = '<' . strtolower($row['webid']) . '>';
$data[] = $json;
}
// return data
header("Content-type: application/json");
echo json_encode($data);
//echo $data;
public function getEnglishResourceFromEnglishWikipediaLink($englishWikipediaLink)
{
$result = null;
$db = sparql_connect('http://dbpedia.org/sparql');
$query = "select * where { ?resource foaf:isPrimaryTopicOf <{$englishWikipediaLink}> .}";
// print_r($query);
$resultQuery = sparql_query($query);
if ($resultQuery) {
$fields = sparql_field_array($resultQuery);
$row = sparql_fetch_array($resultQuery);
if ($row != null) {
$result = $row["resource"];
}
}
return $result;
}
