Labs

eLifeLog.org Labs is a collection of on-going experiments by members. Feel free to create your own one to show off your ideas and get some feedbacks or help from experts in eLifeLog.

Categories

e-Log Project

The E-log project is an acronym of our main lifelog research works composed of various sub laboratories (See the below) aiming to develop a system for life logging that may cover personal diary, healthcare, fitness, surveillance, security and/or enterprise management: e-Log Project

Lifelog Mining

Lifelog data is diverse in all aspects. This category is a collection of lifelog data analysis using our own data, contributed data or public data. Labs include preliminary data analysis or technical development for lifelog mining: Lifelog Mining

Sensor Development

Lifelogging starts from objective monitoring of a user digitizing human actions using various sensors. This category includes our hands-on experiments testing or developing various lifelogging sensors: Sensor

Student projects

Implementing Event summarization on LifeLog Data

We will try to build a system that is capable to summarize the images taken in near similar location along with GPS data. Image grouping will be done using different clustering techniques. Image clustering is a process of grouping images based on their similarity. The image clustering usually uses the color component, texture, edge, shape, or mixture of two components, etc. The clustered image will help in exact event summarization in the particular location.

Summarization of lifelog data

We want to address the problem of summarising frequent event that happens during a user–specified period of time. The key idea that drives our solution is to proceed with the summarisation by steps, as it follows: (1) GPS Points: first of all we need to extract all the paths that the subject has done in the given period of time. With this data we can recognize the most frequent path; (2) People: from the paths it is possible to extract the people that the subject have encounter more times, in order to get the frequent person seen; and (3) Images: with the new information we can now get the most interesting pictures that can describe the place and the people seen in this period of time. The photos will be selected from the ones captured on the starting and arrival place and we use some criteria to choose which one is better.
* For students, to have your project listed in this page, please create a child book page with your experiment name of this Labs. Describe your experiment and plans. Please actively update your project status to keep us informed on what works or not. It would be the best way to get advised from many research members in this community :)

List of all labs

e-Log Project

The E-log project is an acronym of our main lifelog research works composed of various sub laboratories (See the below) aiming to develop a system for life logging that may cover personal diary, healthcare, fitness, surveillance, security and/or enterprise management.

Introduction on e-Log


The origin of the E-log stems from the MEMEX (a compound word of "memory" and "index") system proposed by Vannevar Bush in 1945. MEMEX refers the theoretical proto-hypertext computer system in which an individual compresses and stores all of their books, records, and communications which is then mechanized so that it may be consulted with exceeding speed and flexibility. Its necessity in the modern era is still growing over wide application domains and the rapid progress of computer systems and mobile devices makes it more realistic to implement. Technically we can now record when and where we are, what we do and what we see in real-time and we will see in this report in which application domains market demands mostly dominate.

The concept of the MEMEX system can be broadly applied on the personal digital collection like MyLifeBit project of Microsoft, to the military application like ASSIST project of USA DARPA, to the public security that needs to monitor and record suspicious activities and report illegal events, and to health-care services that need to monitor patients, infants or the aged. Also, smart-phones are getting very popular and their embedded sensors such as cameras, GPS sensors or microphones can record user status in real-time and send it to anywhere by wireless network. They are becoming the strong and solid foundation for the MEMEX-like system development.

The E-log project is a sort of life logging project while the research focus is more set on the analysis of life events pouring efforts on designing the information management system easy to archive the life data and supports the query of real human who are interested in retrieving high-level events. This needs the mathematical formulation of real-life events with the foundation of the way that can treat digitized data objects in a unified way to permit the traversal of complex relations to retrieve all events of user's interests.

Documentation

A collection of documents:

How do we collect and process lifelog data?

Technical backgrounds on the way we collect and process data are briefly summarized at the e-Log project from which this community orginates. Please visit when interested in the technical data processing parts.

Data collection and status

This page records the current status of e-log archives with technical analysis.

Data collection

ViconRevue

ViconRevue is the wearable digital camera designed to take photos passively without intervention whilst being worn by the user. Based on Microsoft SenseCam technology, Revue is a research tool aimed at researchers as an aid for people with memory loss.

Specification

Vicon Revue contains a 1230mAh lithium-ion battery that lasts enough for one-day continuous shot. It is built around a PIC microcontroller. It has:

  • 1GB of flash memory
  • 4KB RAM
  • Many general purpose I/O (GPIO) lines
  • On-chip peripherals. 

Vicon Revue stores VGA resolution (640x480) images as compressed .JPG files on the 1GB flash memory card. The typical image size (around 30KB) allows over 30,000 photos to be stored in the camera.

ViconRevue has the wide-angle lens with indicator lights and buttons for manual shot and privacy pause. It embeds multiple sensors that tell much details on the environment surrounding the user:

  • ACC = 3 axis accelerometer data, the accelerometer triggers the camera but doesn’t get used by the software at this time.
  • TMP = Temperature in degrees C.
  • CLR = Light level, big number (3,000+) = lots of light.
  • BAT = Battery level in millivolts, max is around 41000.
  • MAG = Magnetometer readings in 3 axis. This can be turned into a 'compass' reading but I don’t have any details on this at the moment.
  • PIR = Passive infrared sensor. 0 or 1 where 1 means that there is some moving IR source (typically a person) in view.

Three letter acronyms above are the ID of the sensing records stored in the ViconRevue memory. Records consist of image files and SENSOR.CSV text file. SENSOR.CSV include the timestamped sensing data of image and above sensors.

To get some sample videos composed of pictures captured with Revue, visit ViconRevue YouTube channel.

iPhone 3GS

I use iPhone to capture the location information using MotionX GPS app. See the example below.

GMail

Programmed using PHP for GMail IMAP acccess to monitor all email activities.

Phone records

Collecting from the phone bill.

People identification

Currently using PICASA to identify people face and classification from ViconRevue images. I am considering using Face.com open API for automated face recognition.

Status

As of October 29th, 2010 since July 9th, 2010.  

  • ViconRevue Sensing records:1,820,779
  • Images: 327,542
  • GPS points: 32,473
  • Human faces: 7,658
  • Idenfitied people: 126
  • E-mails: 8,337
  • Phone records: 243

Developer System Configuration

This lab studies the eLifeLog API development system for both developers and administrators. It will describe current development environments and suggest guidelines for similar projects.

 

 

Linux remote mounting for Mac

Use https://github.com/osxfuse/osxfuse/wiki/SSHFS and see https://github.com/osxfuse/osxfuse/wiki/FAQ to figure out mounting remote SSH directories at developer's Mac. See the example that I am using. uid and gid is of the user "_www", which is the default Apache user name. If you want to give a permission for other user, then replace those two numbers (See the example below)

sshfs your_id@your_ssh_server:/remove_server_path/ /localpath -o"volname=your_mapped_volume_name,uid=70,gid=20,allow_other,sync_read,sshfs_sync,follow_symlinks"

How to check user's id:

id otherusername

How to check user's group id:

dscl . -read /Groups/otherusergroupname | awk '($1 == "PrimaryGroupID:") { print $2 }' 20

 

E-model database (First version, kept for records)

The E-model database or called EFIM is a prototype of E-model theory developed purely based on SQL implementing hybrid graph data model and adding supports to existing relational database systems.

Download

Visit http://www.elifelog.org/book/source-code-distribution and select EFIM git to check out the SQL codes. EFIM is purely written in SQL completely database server side script. 

API Documentation

This is a working draft -- June 26th, 2011.

 

The EFIM API is the set of SQL source codes that constitute the EFIM back-end database on top of the relational database.

The whole EFIM API codes are written in SQL following the SQL-99 standard. The current implementation is tested on the MySQL database version 5.1.23-rc-community version. We expect to get user's contribution on the problems for any other relational database products.

This document will explain extensive procedures and functions. As of August 2008, the EFIM API has 110 stored procedures and 95 functions. It supports 8 views that describe the content of data stored in the EFIM database. After installation, the EFIM will use 15 tables for operation.

Let us start by the list of stored procedures and functions.

== Naming convention ==
Stored procedures in the EFIM has a special naming convention. They are numbered by its associated role:
* 100: C-data
* 200: e-node
* 300: Tag
* 400: Category and path search
* 410: Import relational tables
* 411: Export relational tables
* 420: Import XML
* 421: Export XML
* 500: Debug view
* 600: Application extension
* 700: Search related e-nodes
* 900: System status
* 990: Utility procedures
* No number: WordNet interface

Regarding functions, since they can be called in any routines, no special naming convention is applied. However, one such rule is that their name start with ''EFIM_(Get/Put/Is/Query/Util/Translate)'' depending on their role.

== EFIM stored prcedures ==
* [[EFIM:Procedure:100_sp_find_data|100_sp_find_data]]
** IN iRawDataType INTEGER(11)
** IN sValue VARCHAR(255)
** IN iOption INTEGER(11)
** IN iLimit BIGINT
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:100_sp_get_data|100_sp_get_data]]
** IN sData BLOB
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:100_sp_get_data_untagged|100_sp_get_data_untagged]]
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:100_sp_get_data_with_data_id|100_sp_get_data_with_data_id]]
** IN iDataID BIGINT(20) UNSIGNED
** OUT iResult BIGINT(20) UNSIGNED
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:100_sp_show_data_all_type_limit|100_sp_show_data_all_type_limit]]
** IN iLimit BIGINT(20) UNSIGNED
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:121_sp_get_rawdata_type|121_sp_get_rawdata_type]]
* [[EFIM:Procedure:200_import_event_relationship_table|200_import_event_relationship_table]]
** IN sTable TEXT
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:200_sp_delete_event|200_sp_delete_event]]
** IN iParentEvent BIGINT(20) UNSIGNED
* [[EFIM:Procedure:200_sp_get_event|200_sp_get_event]]
** IN iSymbol BIGINT(20) UNSIGNED
** IN iData BIGINT(20) UNSIGNED
** IN iLimit BIGINT(20) UNSIGNED
** OUT iResult BIGINT(20) UNSIGNED
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:200_sp_get_event_with_event_id|200_sp_get_event_with_event_id]]
** IN iEventID BIGINT(20) UNSIGNED
** OUT iResult BIGINT(20) UNSIGNED
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:200_sp_get_generic_subtree|200_sp_get_generic_subtree]]
** IN iParentEvent INTEGER(11) UNSIGNED
* [[EFIM:Procedure:200_sp_get_list_adjacency_paths|200_sp_get_list_adjacency_paths]]
** IN direction char(5)
* [[EFIM:Procedure:200_sp_get_list_descendants|200_sp_get_list_descendants]]
** IN iParentEvent INTEGER(11)
* [[EFIM:Procedure:200_sp_get_list_descendants_in_table|200_sp_get_list_descendants_in_table]]
** IN iParentEvent INTEGER(11)
** IN sEFIMPathTable VARCHAR(255)
* [[EFIM:Procedure:200_sp_get_list_descendants_in_xml|200_sp_get_list_descendants_in_xml]]
** IN iParentEvent BIGINT(20) unsigned
** OUT sXML LONGTEXT
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:200_sp_get_list_descendants_in_xml_no_group_node|200_sp_get_list_descendants_in_xml_no_group_node]]
** IN iParentEvent BIGINT(20) unsigned
** OUT sXML LONGTEXT
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:200_sp_get_list_descendants_in_xml_with_relationships|200_sp_get_list_descendants_in_xml_with_relationships]]
** IN iParentEvent BIGINT(20) UNSIGNED
** IN iType ENUM('all','category','group')
** OUT sXML LONGTEXT
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:200_sp_get_list_descendants_option_in_table|200_sp_get_list_descendants_option_in_table]]
** IN iParentEvent BIGINT(20) UNSIGNED
** IN iRelationship BIGINT(20) UNSIGNED
** IN iDepthLimit BIGINT(20) UNSIGNED
** IN sEFIMPathTable VARCHAR(255)
* [[EFIM:Procedure:200_sp_get_list_descendants_option_in_xml|200_sp_get_list_descendants_option_in_xml]]
** IN iParentEvent BIGINT(20) UNSIGNED
** IN iRelationship BIGINT(20) UNSIGNED
** IN iDepthLimit BIGINT(20) UNSIGNED
** OUT sXML LONGTEXT
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:200_sp_get_xml_from_efim_nextparents|200_sp_get_xml_from_efim_nextparents]]
** INOUT sXML LONGTEXT
** IN iType ENUM('all', 'category', 'group')
** OUT iRows BIGINT(20) UNSIGNED
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:200_sp_query_getresultbytemptable|200_sp_query_getresultbytemptable]]
** IN sTempTable varchar(255)
** IN sSelectQuery TEXT
* [[EFIM:Procedure:200_sp_query_get_related_events|200_sp_query_get_related_events]]
** IN iSourceEvent INTEGER(11)
** IN iRecursive INTEGER(11)
* [[EFIM:Procedure:200_sp_search_event_relationship|200_sp_search_event_relationship]]
** IN iSourceEvent BIGINT(20) UNSIGNED
** IN iTargetEvent BIGINT(20) UNSIGNED
** IN iRelationship BIGINT(20) UNSIGNED
** IN iSearchOption BIGINT(20) UNSIGNED
** OUT iResult BIGINT(20) UNSIGNED
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:210_sp_get_event_relationship|210_sp_get_event_relationship]]
** OUT iResult BIGINT(20) UNSIGNED
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:300_sp_create_tag_international_view|300_sp_create_tag_international_view]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:300_sp_create_tag_view|300_sp_create_tag_view]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:300_sp_get_one_level_path_with_options|300_sp_get_one_level_path_with_options]]
** IN sDirection CHAR(5)
** IN sEFIMPathTable VARCHAR(255)
** IN iInputType ENUM('event','symbol','data')
** IN iInput BIGINT(20) UNSIGNED
* [[EFIM:Procedure:300_sp_get_tag|300_sp_get_tag]]
** IN iSynsetID VARCHAR(255)
** IN iWordID INTEGER(11)
** IN iLexID INTEGER(11)
** IN iDataID BIGINT(20) UNSIGNED
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:300_sp_get_tag_text|300_sp_get_tag_text]]
** sText VARCHAR(255)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:300_sp_get_tag_with_tag_id|300_sp_get_tag_with_tag_id]]
** IN iTagID BIGINT(20) UNSIGNED
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:300_sp_tag_115_data_text|300_sp_tag_115_data_text]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:300_sp_tag_116_data_varchar|300_sp_tag_116_data_varchar]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:300_sp_tag_texts|300_sp_tag_texts]]
** IN iDataID BIGINT(20) UNSIGNED
** IN sData LONGTEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:300_sp_tag_word|300_sp_tag_word]]
** IN iDataID BIGINT(20) UNSIGNED
** IN sWord VARCHAR(255)
* [[EFIM:Procedure:400_sp_create_category_sdtype_view|400_sp_create_category_sdtype_view]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:400_sp_create_category_view|400_sp_create_category_view]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:400_sp_create_event_dump_table|400_sp_create_event_dump_table]]
* [[EFIM:Procedure:400_sp_find_category_childs|400_sp_find_category_childs]]
** IN sSourceSymbol VARCHAR(255)
** IN sSourceData VARCHAR(255)
** IN sSourceDataType VARCHAR(255)
** IN sTargetSymbols TEXT
** IN sTargetKeywords TEXT
** IN sResultTable VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_category|400_sp_get_category]]
** IN sCategoryName VARCHAR(255)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_category_child_events|400_sp_get_category_child_events]]
** IN sParentTable VARCHAR(255)
** IN sChildTable VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_category_list|400_sp_get_category_list]]
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_category_model_in_xml|400_sp_get_category_model_in_xml]]
** IN iCategoryID BIGINT(20) UNSIGNED
** OUT sXML LONGTEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:400_sp_get_group_child_events|400_sp_get_group_child_events]]
** IN sParentTable VARCHAR(255)
** IN sRelationship TEXT
** IN sChildTable VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_group_event|400_sp_get_group_event]]
** IN sCategoryName VARCHAR(255)
** IN sGroupTable VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_parent_category_events|400_sp_get_parent_category_events]]
** IN sSourceSymbol VARCHAR(255)
** IN sSourceData LONGTEXT
** IN sSourceDataType VARCHAR(255)
** IN sResultTable VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_parent_category_events_from_input_table|400_sp_get_parent_category_events_from_input_table]]
** IN sSourceEventTable VARCHAR(255)
** IN sResultTable VARCHAR(255)
* [[EFIM:Procedure:400_sp_get_path|400_sp_get_path]]
** IN iSourceEvent BIGINT(20) UNSIGNED
** IN sRelationship TEXT
** IN iTargetEvent BIGINT(20) UNSIGNED
** IN sOutputTable VARCHAR(255)
** IN iDepthLimit INTEGER(11)
** IN iMatchOption ENUM('complete','all','shortest','count')
** IN iMatchCount INTEGER(11)
** IN iSearchDirection ENUM('both','forward','backward')
* [[EFIM:Procedure:400_sp_get_path_bidrectional_table|400_sp_get_path_bidrectional_table]]
** IN sSourceTable VARCHAR(255)
** IN sRelationship TEXT
** IN sTargetTable VARCHAR(255)
** IN sOutputTable VARCHAR(255)
** IN iDepthLimit INTEGER(11)
** IN iMatchOption ENUM('complete','all','shortest', 'count')
** IN iMatchCount INTEGER(11)
* [[EFIM:Procedure:400_sp_get_path_from_source_to_target_table|400_sp_get_path_from_source_to_target_table]]
** IN sSourceTable VARCHAR(255)
** IN sRelationship TEXT
** IN sTargetTable VARCHAR(255)
** IN sOutputTable VARCHAR(255)
** IN iDepthLimit INTEGER(11)
** IN iMatchOption ENUM('complete','all','shortest', 'count')
** IN iMatchCount INTEGER(11)
* [[EFIM:Procedure:400_sp_get_path_from_table|400_sp_get_path_from_table]]
** IN sSourceTable VARCHAR(255)
** IN sRelationship TEXT
** IN sTargetTable VARCHAR(255)
** IN sOutputTable VARCHAR(255)
** IN iDepthLimit INTEGER(11)
** IN iMatchOption ENUM('complete','all','shortest','count')
** IN iMatchCount INTEGER(11)
** IN iSearchDirection ENUM('both','forward','backward')
* [[EFIM:Procedure:400_sp_get_path_from_target_to_source_table|400_sp_get_path_from_target_to_source_table]]
** IN sSourceTable VARCHAR(255)
** IN sRelationship TEXT
** IN sTargetTable VARCHAR(255)
** IN sOutputTable VARCHAR(255)
** IN iDepthLimit INTEGER(11)
** IN iMatchOption ENUM('complete','all','shortest', 'count')
** IN iMatchCount INTEGER(11)
* [[EFIM:Procedure:400_sp_get_recursive_target_events|400_sp_get_recursive_target_events]]
** IN sParentTable VARCHAR(255)
** IN sRelationship TEXT
** IN sCategories TEXT
** IN sChildTable VARCHAR(255)
** IN iDepthLimit INTEGER(11)
* [[EFIM:Procedure:400_sp_put_category_model_in_xml|400_sp_put_category_model_in_xml]]
** IN sXML LONGTEXT
** IN sRootNode VARCHAR(255)
** INOUT iCategoryID BIGINT(20) UNSIGNED
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:410_sp_dump_with_select_query|410_sp_dump_with_select_query]]
** IN sSelectQuery VARCHAR(255)
** IN bUseMemory INTEGER(11)
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:410_sp_import_rawdata_from_dump_column|410_sp_import_rawdata_from_dump_column]]
** IN sColumn VARCHAR(255)
** IN sMySQLType VARCHAR(255)
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:410_sp_import_relational_database|410_sp_import_relational_database]]
** IN sCategory VARCHAR(255)
** IN sOperationMode VARCHAR(255)
** IN sArgument TEXT
** IN sFunctionModel TEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:410_sp_import_select_query|410_sp_import_select_query]]
** IN sSelectQuery VARCHAR(255)
* [[EFIM:Procedure:410_sp_register_rdbms_events|410_sp_register_rdbms_events]]
** IN sCategoryName VARCHAR(255)
** IN bStatusReport INTEGER(11)
** OUT iCategoryID BIGINT(20) UNSIGNED
* [[EFIM:Procedure:410_sp_register_rdbms_event_relationships|410_sp_register_rdbms_event_relationships]]
** IN iCategoryID BIGINT(20) UNSIGNED
** IN sXML TEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:410_sp_register_rdbms_raw_data|410_sp_register_rdbms_raw_data]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:411_sp_export_create_export_table|411_sp_export_create_export_table]]
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:411_sp_export_data_from_path_table|411_sp_export_data_from_path_table]]
** IN iCategoryID BIGINT(20) UNSIGNED
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:411_sp_export_relational_database|411_sp_export_relational_database]]
** IN sCategory TEXT
** IN sTable VARCHAR(255)
** IN iTransactionLimit INTEGER(11)
** IN bUseMemory INTEGER(11)
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:420_sp_import_xml|420_sp_import_xml]]
** IN iCategoryID BIGINT(20) UNSIGNED
** IN iParentID BIGINT(20) UNSIGNED
** IN sInputData LONGTEXT
** INOUT sModel LONGTEXT
** IN sInputDataRootNode LONGTEXT
** IN sModelRootNode LONGTEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:420_sp_import_xml_with_xml|420_sp_import_xml_with_xml]]
** IN iCategoryID BIGINT(20) UNSIGNED
** IN iParentID BIGINT(20) UNSIGNED
** IN sInputData LONGTEXT
** INOUT sModel LONGTEXT
** IN sInputDataRootNode LONGTEXT
** IN sModelRootNode LONGTEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:500_sp_debug_event_view|500_sp_debug_event_view]]
** IN iEventID BIGINT(20) UNSIGNED
* [[EFIM:Procedure:600_sp_get_efim_player_current_scene_id|600_sp_get_efim_player_current_scene_id]]
** OUT iSceneID INTEGER(11)
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_get_efim_player_current_similar_scene|600_sp_get_efim_player_current_similar_scene]]
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_get_efim_player_current_similar_scene_order|600_sp_get_efim_player_current_similar_scene_order]]
** IN iOrder INTEGER(11)
** OUT iChildScript TEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_get_efim_player_current_similar_scene_script|600_sp_get_efim_player_current_similar_scene_script]]
** IN iOrder INTEGER(11)
** OUT sScript TEXT
** IN bStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_get_efim_player_event|600_sp_get_efim_player_event]]
** IN sData TEXT
** IN iRawDataType INTEGER(11) UNSIGNED
** IN iIsSymbol INTEGER(11) UNSIGNED
** IN iIsData INTEGER(11) UNSIGNED
** IN iLimit BIGINT(20) UNSIGNED
** IN iOffset BIGINT(20) UNSIGNED
** IN iStatusReport INTEGER(11) UNSIGNED
* [[EFIM:Procedure:600_sp_get_efim_player_related_event|600_sp_get_efim_player_related_event]]
** IN iSourceEventID BIGINT(20) UNSIGNED
** IN iLimit BIGINT(20) UNSIGNED
** IN iOffset BIGINT(20) UNSIGNED
** IN iStatusReport INTEGER(11) UNSIGNED
* [[EFIM:Procedure:600_sp_get_efim_player_scene|600_sp_get_efim_player_scene]]
** IN iEpisodeID INTEGER(11)
** IN sEFIMPathTable VARCHAR(255)
** IN sPathToSave TEXT
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_get_efim_player_scene_id|600_sp_get_efim_player_scene_id]]
** IN iEpisodeID INTEGER(11)
** IN iMilliSecond INTEGER(11)
** OUT iSceneID INTEGER(11)
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_get_efim_player_scene_similarity|600_sp_get_efim_player_scene_similarity]]
** IN iSceneID INTEGER(11)
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_get_efim_player_subtitle|600_sp_get_efim_player_subtitle]]
** IN iEpisodeID INTEGER(11)
** IN sEFIMPathTable VARCHAR(255)
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:600_sp_put_efim_player_status_message|600_sp_put_efim_player_status_message]]
** IN sEFIMPlayerEpisodeID VARCHAR(255)
** IN sEFIMPlayerStatusClass VARCHAR(255)
** IN sEFIMPlayerStatusType VARCHAR(255)
** IN sEFIMPlayerStatusValue VARCHAR(255)
* [[EFIM:Procedure:700_sp_get_one_related_event_data|700_sp_get_one_related_event_data]]
** IN sSourceSymbol VARCHAR(255)
** IN iSourceDataType INTEGER(11)
** IN sSourceData LONGBLOB
** IN sTargetSymbol VARCHAR(255)
** IN iTargetDataType INTEGER(11)
** OUT sTargetData LONGBLOB
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:700_sp_get_related_event_by_group_in_table|700_sp_get_related_event_by_group_in_table]]
** IN sSourceSymbol VARCHAR(255)
** IN sTargetSymbol VARCHAR(255)
** IN sEFIMPathTable VARCHAR(255)
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:700_sp_get_related_event_by_group_in_xml|700_sp_get_related_event_by_group_in_xml]]
** IN sSourceSymbol VARCHAR(255)
** IN sTargetSymbol VARCHAR(255)
** IN sRootNodeName VARCHAR(255)
** OUT sXML LONGTEXT
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:700_sp_get_related_event_by_value_in_table|700_sp_get_related_event_by_value_in_table]]
** IN sSourceTable VARCHAR(255)
** IN sSourceSymbol VARCHAR(255)
** IN iSourceDataType INTEGER(11)
** IN sTargetSymbol VARCHAR(255)
** IN sEFIMPathTable VARCHAR(255)
** IN iStatusReport INTEGER(11)
* [[EFIM:Procedure:900_get_efim_system_log|900_get_efim_system_log]]
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:990_sp_create_efim_tables|990_sp_create_efim_tables]]
* [[EFIM:Procedure:990_sp_create_efim_views|990_sp_create_efim_views]]
* [[EFIM:Procedure:990_sp_initialize|990_sp_initialize]]
** IN iOption ENUM('all', 'event')
* [[EFIM:Procedure:990_sp_initialize_event_parts|990_sp_initialize_event_parts]]
* [[EFIM:Procedure:990_sp_util_list_api|990_sp_util_list_api]]
* [[EFIM:Procedure:990_util_find_procedures|990_util_find_procedures]]
** IN sKeyword VARCHAR(255)
* [[EFIM:Procedure:990_util_find_related_word|990_util_find_related_word]]
** iSynset INTEGER(11), iWord INTEGER(11)
** INOUT sPos CHAR(1)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:990_util_find_word|990_util_find_word]]
** sWord VARCHAR(255), sPos CHAR(1), iOption INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:990_util_install_trigger|990_util_install_trigger]]
** IN sDatabase INTEGER(11)
* [[EFIM:Procedure:990_util_process_sentence_xml|990_util_process_sentence_xml]]
** IN sFile varchar(255)
* [[EFIM:Procedure:990_util_remove_all_events_with_tasks|990_util_remove_all_events_with_tasks]]
* [[EFIM:Procedure:990_util_string_split|990_util_string_split]]
** IN strResponse LONGTEXT
** IN strSplitter CHAR(1)
** IN sOutputTable VARCHAR(255)
* [[EFIM:Procedure:1000_sp_find_events|1000_sp_find_events]]
** IN sRawDataType varchar(255)
** IN sKeywords TEXT
* [[EFIM:Procedure:sp_get_linkdef|sp_get_linkdef]]
* [[EFIM:Procedure:sp_get_linkdef_count_by_lemma|sp_get_linkdef_count_by_lemma]]
** sWord VARCHAR(255)
* [[EFIM:Procedure:sp_get_linkdef_count_by_word_synset_id|sp_get_linkdef_count_by_word_synset_id]]
** iWord INTEGER(11), iSynset INTEGER(11)
* [[EFIM:Procedure:sp_get_related_lexlink_word|sp_get_related_lexlink_word]]
** iWordID INTEGER(11), iSynsetID INTEGER(11), iLinkType INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:sp_get_related_semlink_word|sp_get_related_semlink_word]]
** iWordID INTEGER(11), iSynsetID INTEGER(11), iLinkType INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:sp_get_related_word|sp_get_related_word]]
** iWordID INTEGER(11), iSynsetID INTEGER(11), iLinkType INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:sp_get_related_word_from_lemma|sp_get_related_word_from_lemma]]
** sWord VARCHAR(255), iLinkType INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:sp_get_related_word_from_lemma_linktype|sp_get_related_word_from_lemma_linktype]]
** sWord VARCHAR(255), sLinkType VARCHAR(255)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:sp_get_symbol_relations|sp_get_symbol_relations]]
* [[EFIM:Procedure:sp_get_word|sp_get_word]]
** sWord VARCHAR(255), sPos CHAR(1), iOption INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:sp_get_word_user_data_extension|sp_get_word_user_data_extension]]
** sWord VARCHAR(255), iOption INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:sp_get_word_user_data_extension_with_option|sp_get_word_user_data_extension_with_option]]
** IN sWord VARCHAR(255)
** IN sPos CHAR(1)
** IN iOption INTEGER(11)
** OUT iResult INTEGER(11)
** OUT sResult VARCHAR(255)
* [[EFIM:Procedure:test|test]]
** IN sKeyword TEXT
* [[EFIM:Procedure:test_search_conversation|test_search_conversation]]
** IN sSpeakerA VARCHAR(255)
** IN sSpeakerB VARCHAR(255)
** IN sKeywords VARCHAR(255)
* [[EFIM:Procedure:test_search_speaker|test_search_speaker]]
** IN sSourceSymbol varchar(255)
** IN sSourceData varchar(255)
** IN sTargetSymbols VARCHAR(255)
** IN sTargetKeywords VARCHAR(255)
* [[EFIM:Procedure:traverse_category_down|traverse_category_down]]
** IN start_with INTEGER(11) UNSIGNED

== EFIM Functions ==
* [[EFIM:Function:BASE64_DECODE|BASE64_DECODE]]
** IN input LONGBLOB
** RETURN TYPE: longblob
* [[EFIM:Function:BASE64_ENCODE|BASE64_ENCODE]]
** IN input LONGBLOB
** RETURN TYPE: longblob
* [[EFIM:Function:EFIM_GetCategoryName|EFIM_GetCategoryName]]
** IN iCategoryID BIGINT UNSIGNED
** RETURN TYPE: char(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetDataIDbyRawDataTypeID|EFIM_GetDataIDbyRawDataTypeID]]
** IN iRawDataType BIGINT(20) UNSIGNED
** IN  iRawDataID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetDataIDofEvent|EFIM_GetDataIDofEvent]]
** IN iEventID BIGINT UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetDatainBlob|EFIM_GetDatainBlob]]
** IN iDataID BIGINT UNSIGNED
** RETURN TYPE: longblob
* [[EFIM:Function:EFIM_GetDatainText|EFIM_GetDatainText]]
** IN iDataID BIGINT UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_GetDatainTextLimit|EFIM_GetDatainTextLimit]]
** IN iDataID BIGINT UNSIGNED
** IN  iLimit INTEGER(11)
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_GetDataofEventinBlob|EFIM_GetDataofEventinBlob]]
** IN iEventID BIGINT UNSIGNED
** RETURN TYPE: longblob
* [[EFIM:Function:EFIM_GetDataofEventinText|EFIM_GetDataofEventinText]]
** IN iEventID BIGINT UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_GetDataTypebyRawDataTypeID|EFIM_GetDataTypebyRawDataTypeID]]
** IN iRawDataTypeID INTEGER(11)
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetDataTypeofEvent|EFIM_GetDataTypeofEvent]]
** IN iEventID BIGINT UNSIGNED
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetEFIMDumpFieldID|EFIM_GetEFIMDumpFieldID]]
** IN sFieldName TEXT
** RETURN TYPE: int(11)
* [[EFIM:Function:EFIM_GetEventCountbySymbol|EFIM_GetEventCountbySymbol]]
** IN sSymbol VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetEventRelationshipID|EFIM_GetEventRelationshipID]]
** IN sRelationship VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetEventTimeStamp|EFIM_GetEventTimeStamp]]
** IN iEventID BIGINT UNSIGNED
** RETURN TYPE: timestamp
* [[EFIM:Function:EFIM_GetFunctionName|EFIM_GetFunctionName]]
** IN iFunctionID BIGINT UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_GetMySQLTypebyRawDataTypeID|EFIM_GetMySQLTypebyRawDataTypeID]]
** IN iRawDataTypeID INTEGER(11)
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetOneCategoryID|EFIM_GetOneCategoryID]]
** IN sCategory VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneChildEventwithOptions|EFIM_GetOneChildEventwithOptions]]
** IN iSourceEvent BIGINT(20) UNSIGNED
** IN  iRelationshipEvent BIGINT(20) UNSIGNED
** IN  iSymbolID BIGINT(20) UNSIGNED
** IN  iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneDataID|EFIM_GetOneDataID]]
** IN iRawDataType INTEGER(11)
** IN  sData LONGTEXT
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneDataIDforAllType|EFIM_GetOneDataIDforAllType]]
** IN sData LONGBLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneEventID|EFIM_GetOneEventID]]
** IN iSymbolID BIGINT(20) UNSIGNED
** IN  iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneEventIDbySymbolNameRawDataTypeRawData|EFIM_GetOneEventIDbySymbolNameRawDataTypeRawData]]
** IN sSymbolName VARCHAR(255)
** IN  sRawDataType VARCHAR(255)
** IN  sData LONGBLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneFunctionID|EFIM_GetOneFunctionID]]
** IN sFunction VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneIntegerDataID|EFIM_GetOneIntegerDataID]]
** IN sData VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneRawDataID|EFIM_GetOneRawDataID]]
** IN iRawDataType BIGINT(20) UNSIGNED
** IN  sData LONGBLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneSDTypeID|EFIM_GetOneSDTypeID]]
** IN iSymbolID VARCHAR(255)
** IN  iRawDataTypeID VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneSDTypeIDbySymbolNameRawDataTypeName|EFIM_GetOneSDTypeIDbySymbolNameRawDataTypeName]]
** IN sSymbol VARCHAR(255)
** IN  sRawDataType VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneSourceEventID|EFIM_GetOneSourceEventID]]
** IN iRelationshipEventID BIGINT(20) UNSIGNED
** IN  iTargetEventID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneSourceEventIDbySymbolNameSourceData|EFIM_GetOneSourceEventIDbySymbolNameSourceData]]
** IN sSourceSymbol VARCHAR(255)
** IN  sSourceDataType VARCHAR(255)
** IN  sSourceData LONGTEXT
** IN  sRelationship VARCHAR(255)
** RETURN TYPE: bigint(20)
* [[EFIM:Function:EFIM_GetOneTagID|EFIM_GetOneTagID]]
** IN iSynset INTEGER(11)
** IN  iWordno INTEGER(11)
** IN  iLexno INTEGER(11)
** IN  iData BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneTagIDbyName|EFIM_GetOneTagIDbyName]]
** IN sSymbolName VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetOneTextData|EFIM_GetOneTextData]]
** IN iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_GetOneTextDataID|EFIM_GetOneTextDataID]]
** IN sData VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetRawDataName|EFIM_GetRawDataName]]
** IN iRawDataType BIGINT(20) UNSIGNED
** RETURN TYPE: char(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetRawDataTableName|EFIM_GetRawDataTableName]]
** IN iRawDataType BIGINT(20) UNSIGNED
** RETURN TYPE: char(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetRawDataTableNamebyMySQLType|EFIM_GetRawDataTableNamebyMySQLType]]
** IN sMySQLType varchar(255)
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetRawDataTableNamebyRawDataName|EFIM_GetRawDataTableNamebyRawDataName]]
** IN sRawDataType VARCHAR(255)
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetRawDataTypebyMySQLType|EFIM_GetRawDataTypebyMySQLType]]
** IN sMySQLType varchar(255)
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetRawDataTypeIDbyMySQLType|EFIM_GetRawDataTypeIDbyMySQLType]]
** IN sMySQLType varchar(255)
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetRawDataTypeofData|EFIM_GetRawDataTypeofData]]
** IN iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: varchar(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetSymbolIDofEvent|EFIM_GetSymbolIDofEvent]]
** IN iEventID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_GetSymbolofEventinText|EFIM_GetSymbolofEventinText]]
** IN iEventID BIGINT(20) UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_GetSynsetofTag|EFIM_GetSynsetofTag]]
** IN iTagID BIGINT(20) UNSIGNED
** RETURN TYPE: int(11)
* [[EFIM:Function:EFIM_GetTagName|EFIM_GetTagName]]
** IN iTagID BIGINT(20) UNSIGNED
** RETURN TYPE: char(255) CHARSET utf8
* [[EFIM:Function:EFIM_GetXMLSafeString|EFIM_GetXMLSafeString]]
** IN sXML LONGTEXT
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_IsBlobEvent|EFIM_IsBlobEvent]]
** IN iEventID BIGINT(20) UNSIGNED
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_IsDateTime|EFIM_IsDateTime]]
** IN sData BLOB
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_IsGeometry|EFIM_IsGeometry]]
** IN sData BLOB
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_IsGroupEvent|EFIM_IsGroupEvent]]
** IN iEventID BIGINT(20)
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_IsMultiByteChar|EFIM_IsMultiByteChar]]
** IN sText VARCHAR(255)
** RETURN TYPE: int(11)
* [[EFIM:Function:EFIM_IsNumeric|EFIM_IsNumeric]]
** IN sData BLOB
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_IsSymboltoTag|EFIM_IsSymboltoTag]]
** IN iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_IsUUID|EFIM_IsUUID]]
** IN sUUID VARCHAR(255)
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_IsValidDataID|EFIM_IsValidDataID]]
** IN iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_IsValidEventID|EFIM_IsValidEventID]]
** IN iEventID BIGINT(20) UNSIGNED
** RETURN TYPE: int(11)
* [[EFIM:Function:EFIM_PutCategory|EFIM_PutCategory]]
** IN iSymbol INTEGER(11)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutCategorybyName|EFIM_PutCategorybyName]]
** IN sCategoryName VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutCategorybySymbolName|EFIM_PutCategorybySymbolName]]
** IN sSymbol varchar(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutCategoryElement|EFIM_PutCategoryElement]]
** IN iCategoryEventID BIGINT(20) UNSIGNED
** IN  iElementEventID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutCategoryElementbyCategoryNameElementID|EFIM_PutCategoryElementbyCategoryNameElementID]]
** IN sCategory VARCHAR(255)
** IN  iElementEventID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutCategoryEvent|EFIM_PutCategoryEvent]]
** IN iCategoryEvent BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutData|EFIM_PutData]]
** IN iRawDataType BIGINT(20) UNSIGNED
** IN  sData LONGBLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutDatabyMimeIDDataSize|EFIM_PutDatabyMimeIDDataSize]]
** IN iMimeType INTEGER(11)
** IN  iDataSize INTEGER(11)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEvent|EFIM_PutEvent]]
** IN iSymbol BIGINT(20) UNSIGNED
** IN  iData BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEventbySymbolIDRawDataTypeIDData|EFIM_PutEventbySymbolIDRawDataTypeIDData]]
** IN iSymbolID BIGINT(20) UNSIGNED
** IN  iRawDataTypeID BIGINT(20) UNSIGNED
** IN  sData BLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEventbySymbolNameDataID|EFIM_PutEventbySymbolNameDataID]]
** IN sSymbol VARCHAR(255)
** IN  iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEventbySymbolNameRawDataTypeData|EFIM_PutEventbySymbolNameRawDataTypeData]]
** IN sSymbol VARCHAR(255)
** IN  sRawDataType VARCHAR(255)
** IN  sData BLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEventbySymbolNameRawDataTypeIDData|EFIM_PutEventbySymbolNameRawDataTypeIDData]]
** IN sSymbol VARCHAR(255)
** IN  iRawDataTypeID INTEGER(11) UNSIGNED
** IN  sData BLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEventRelationship|EFIM_PutEventRelationship]]
** IN iSourceEvent BIGINT(20) UNSIGNED
** IN  iRelationship BIGINT(20) UNSIGNED
** IN  iTargetEvent BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEventRelationshipText|EFIM_PutEventRelationshipText]]
** IN iSourceEvent BIGINT(20) UNSIGNED
** IN  iTargetEvent BIGINT(20) UNSIGNED
** IN  sRelaionship VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutEventRelationshipType|EFIM_PutEventRelationshipType]]
** IN sRelationship VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutFunctionbyName|EFIM_PutFunctionbyName]]
** IN sFunctionName VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutFunctionElement|EFIM_PutFunctionElement]]
** IN iFunctionEventID BIGINT(20) UNSIGNED
** IN  iIsInput BIGINT(20) UNSIGNED
** IN  iSDTypeID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutFunctionElementbyFunctionNameElementID|EFIM_PutFunctionElementbyFunctionNameElementID]]
** IN sFunction VARCHAR(255)
** IN  iIsInput INTEGER(11)
** IN  iSDTypeID BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutRawData|EFIM_PutRawData]]
** IN iRawDataType BIGINT(20) UNSIGNED
** IN  sData LONGBLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutSDType|EFIM_PutSDType]]
** IN iSymbolID BIGINT(20) UNSIGNED
** IN  iRawDataTypeID INTEGER UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutSDTypebySymbolNameRawDataType|EFIM_PutSDTypebySymbolNameRawDataType]]
** IN sSymbol VARCHAR(255)
** IN  sRawDataType VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutSingleDatabyRawDataTypeID|EFIM_PutSingleDatabyRawDataTypeID]]
** IN iRawDataTypeID BIGINT(20) UNSIGNED
** IN  sData BLOB
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutSystemLog|EFIM_PutSystemLog]]
** IN iType ENUM('normal'
** IN 'warning'
** IN 'result')
** IN  sMessage varchar(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutTag|EFIM_PutTag]]
** IN iSynset INTEGER(11)
** IN  iWordno INTEGER(11)
** IN  iLexno INTEGER(11)
** IN  iData BIGINT(20) UNSIGNED
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_PutTagbyTextData|EFIM_PutTagbyTextData]]
** IN iSynset INTEGER(11)
** IN  iWordno INTEGER(11)
** IN  iLexno INTEGER(11)
** IN  sData VARCHAR(255)
** RETURN TYPE: bigint(20) unsigned
* [[EFIM:Function:EFIM_Query_SelectEventRelationship|EFIM_Query_SelectEventRelationship]]
** IN iIsUp INTEGER
** IN  sEFIMPathTable VARCHAR(255)
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_Query_SelectRawData|EFIM_Query_SelectRawData]]
** IN iRawDataType BIGINT(20) UNSIGNED
** IN  sValue LONGBLOB
** IN  iOption BIGINT(20) UNSIGNED
** IN  iLimit BIGINT(20) UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_Query_SelectRawDatabyDataID|EFIM_Query_SelectRawDatabyDataID]]
** IN iDataID BIGINT(20) UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_Query_SelectRawDataFromAssignedDataTable|EFIM_Query_SelectRawDataFromAssignedDataTable]]
** IN sDataTableName VARCHAR(255)
** IN  iRawDataType BIGINT(20) UNSIGNED
** IN  iOption BIGINT(20) UNSIGNED
** IN  iLimit BIGINT(20) UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_Query_SelectRelatedEvents|EFIM_Query_SelectRelatedEvents]]
** IN iSourceEvent BIGINT(20) UNSIGNED
** IN  iRelationshipEvent BIGINT(20) UNSIGNED
** IN  iTargetEvent BIGINT(20) UNSIGNED
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_TranslateKoreanKeywordsToEnglish|EFIM_TranslateKoreanKeywordsToEnglish]]
** IN sKeywords TEXT
** RETURN TYPE: text CHARSET utf8
* [[EFIM:Function:EFIM_TranslateKoreanWordToEnglish|EFIM_TranslateKoreanWordToEnglish]]
** IN sKeyword TEXT
** RETURN TYPE: text CHARSET utf8
* [[EFIM:Function:EFIM_Util_GetRawCastData|EFIM_Util_GetRawCastData]]
** IN iRawDataTypeID INTEGER UNSIGNED
** IN  sColumn VARCHAR(255)
** RETURN TYPE: longtext CHARSET utf8
* [[EFIM:Function:EFIM_Util_GetRawCastValue|EFIM_Util_GetRawCastValue]]
** IN iRawDataTypeID BIGINT(20) UNSIGNED
** IN  sColumn VARCHAR(255)
** RETURN TYPE: text CHARSET utf8
* [[EFIM:Function:EFIM_Util_IsTableExist|EFIM_Util_IsTableExist]]
** IN sDatabase varchar(255)
** IN  sTable varchar(255)
** RETURN TYPE: int(11)
* [[EFIM:Function:EFIM_Util_PutEFIMReservedKeywords|EFIM_Util_PutEFIMReservedKeywords]]
** RETURN TYPE: tinyint(4)
* [[EFIM:Function:EFIM_Util_RowID|EFIM_Util_RowID]]
** RETURN TYPE: bigint(20) unsigned

== References ==
* Codes to generate the stored procedure list

BEGIN
/*
  Developed by Pilho Kim, 08/03/2008

Object: Procedure to list up all stored procedures and funcstion.

Input:
Output:
  The list of functions will be stored at the specified directory and file.
  Be aware that you have to delete the local file before running this procedure. 
  If already exists, then this procedure will fail.
  
Comment:

Revision History:
  08/03/2008 # First codes
*/

SELECT CONCAT(
		CONCAT('* [[EFIM:Procedure:', mproc.name, '|', mproc.name, ']]'),
        # IF param_list exists
        IF (LENGTH(mproc.param_list) > 1, 
        	CONCAT('\n** ', REPLACE(REPLACE(mproc.param_list, ', IN', '\n** IN'), ', OUT', '\n** OUT')),
            ''))
        AS 'EFIM API list'
        INTO OUTFILE 'c:/EFIM_Stored_procedure_list.txt' 
        LINES TERMINATED BY '\n'
FROM mysql.proc AS mproc 
WHERE db = 'efim_release' AND type = 'procedure'
ORDER BY mproc.name ASC;

SELECT CONCAT(
		CONCAT('* [[EFIM:Function:', mproc.name, '|', mproc.name, ']]'),
        # Return type
        # IF param_list exists
        IF (LENGTH(mproc.param_list) > 1, 
        	CONCAT('\n** IN ', REPLACE(mproc.param_list, ',', '\n** IN ')),
            ''),
        CONCAT('\n** RETURN TYPE: ', mproc.returns))
        AS 'EFIM API list'
        INTO OUTFILE 'c:/EFIM_Function_list.txt' 
        LINES TERMINATED BY '\n'
FROM mysql.proc AS mproc 
WHERE db = 'efim_release' AND type = 'function'
ORDER BY mproc.name ASC;

END

 

 

 

 

 

 

 

 

 

Practice in importing and exporting database tables that include "TIMESTAMP" field

To keep a long story short, I made this mistake a number of times even when handling billions of records. So here is a record for prevention :)

When exporting or importing table data that include TIMESTAMP field in there, MySQL by defaults exports the data based on UTC. It internally executes "SET time_zone='UTC'" command before exporting any data. This is a default behavior (See https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_tz-utc) and so after using mysqldump to export data, you MUST set "SET time_zone='UTC'" before importing (using LOAD DATA) the data to any database.

ex) exporting data:
mysqldump --opt -Q -h 127.0.0.1 --user=youruseid --password=yourpassword --no-create-info --where="unixtimestamp < 1373925599" --tab="/wheretosave/tmp" --fields-terminated-by="\t" --fields-optionally-enclosed-by="\"" --lines-terminated-by="\n" yourdb yourtable

Then when importing data within MySQL console:

mysql> SET time_zone="UTC";
mysql> USE yourdb;
mysql> LOAD DATA LOCAL INFILE '/wheretosave/tmp/yourtable.txt' INTO TABLE yourtable FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

EML (First version -- Kept for records)

EML stands for the E-model language designed for the general purpose event query. It is based on the triple-type e-node graph and its syntax will be the extension of SPARQL and SQL. EML is based on the hyper graph data structure (See the E-model database structure.) Currently it supports MySQL database and this labs defines the BNF specification of EML and its parser. 

This is a working draft -- June 26th, 2011

EML Namespaces

Name Notation Comments
All sets _* Search all objects in the E-model database
Parent object _parent, _parent[i] [i] means the ith parent object
Child object _child, _child[i] [i] means ith child object
Name _name Object name
Value _value Object value
Raw data type _type Object raw data type
Timestamp _timestamp Object transaction time
Temporal constraint WHEN Temporal search of associated time predicates
Relation _R(,,) Query object relation
Graph path (%d, _EPATH, *) _EPATH specifies edges to walk. %d denotes the dth slected field.
Populatiry _Popularity() Used to order results by popularity

Documents

EML BNF Specification

! -----------------------------------------------------------------------------------
! E-model language (EML)
!
! An E-model prototype is built on top of relational database. 
! Its full functions are implemented using hundreds of SQL procedures
! and functions. The idea is when a database system adds the E-model 
! database to existing databases, then a user can query the database 
! in the mixture of structured queries in addition to E-model queries. 
! EML implements such ideas by extending the SQL language to support 
! both structured and unstructured queries with various feature additions. 
!
! Update:
!   03/29/2009 Preliminary EML design
!   06/05/2009 Add chain expression in Id list
!
! Note: This is a preliminary version of the EML based on the SQL 89 grammar 
! at http://www.devincook.com/goldparser/grammars/index.htm.
!
! Note, 05/02/2010: Add supports to _parent, _child, _parent[%d], _child[%d] namespaces
! Note, 06/02/2010: Add supports to Id without child specification in WHEN clause
! Note, 10/16/2010: Add supports functions like second, year as the type specifier
!                   ex) WHEN ABS(t3 - t4) <= 30 SECOND;
! -----------------------------------------------------------------------------------

"Name"         = 'E-model language (EML)'
"Author"       = 'Pilho Kim'
"Version"      = '03/26/2011'
"About"        = 'EML is an extended SQL language developed on top of E-model.'

"Start Symbol" = 

! =============================================================================
! Comments
! =============================================================================

Comment Start = '/*'
Comment End   = '*/'
Comment Line  = '--'

! =============================================================================
! Terminals
! =============================================================================

{String Ch 1}      = {Printable} - ["]
{String Ch 2}      = {Printable} - ['']
{Id Ch Standard}   = {Alphanumeric} + [_] + ['*']
{Id Ch Extended}   = {Printable} - ['['] - [']']

StringLiteral   = '"'{String Ch 1}*'"' | ''{String Ch 2}*''
IntegerLiteral  = {Digit}+
RealLiteral     = {Digit}+'.'{Digit}+

! =============================================================================
! EML namespace
! =============================================================================

! Extension for EML, allow * to specify whole database
{EML Id Ch}   = {Letter} + ['_'] + ['*']

!----- Identifiers in SQL are very complex. 
! Id             = ({Letter}{Id Ch Standard}* | '['{Id Ch Extended}+']')  ('.'({Letter}{Id Ch Standard}* | '['{Id Ch Extended}+']'))?
! Support _* expression that specify the whole databases and tables
! Allow chain expression
! Id  = ({EML Id Ch}{Id Ch Standard}* | '['{Id Ch Extended}+']') ('.'({EML Id Ch}{Id Ch Standard}* | '['{Id Ch Extended}+']'))?
Id  = ({EML Id Ch}{Id Ch Standard}* | '['{Id Ch Extended}+']') ('.'({EML Id Ch}{Id Ch Standard}* | '['{Id Ch Extended}+']'))*


! =============================================================================
! Rules
! =============================================================================

! Support EndofStatement ';'
       ::=  
                |  
                |  
                |  
                |  
                | 
                  | INSERT INTO Id '('  ')' VALUES '('  ')'

    ::= UPDATE Id SET   

   ::= Id '='  ',' 
                  | Id '='  

    ::= DELETE FROM Id  

! =============================================================================
! Select Statement
! =============================================================================

! Add LIMIT
 ')'
                | '('  ')'

  ::=  ',' 
               | 

     ::=  ','  
                | 

!    ::= Id
!                | Id Id

! Support 'AS' and sub query
   ::= Id
                | Id Id
                | Id AS Id
                | '('  ')' Id
                | '(' 

Using the network storage to build up personal lifelogs

In this experiment, a linux-embedded network storage is used to extend its function to manage one's personal lifelogs. For the first experiment, 4TB My Book Live Duo was chosen due to its cheap price, SSH supports and good enough size to start lifelogging. This page will list up how a user can set up her personal cloud installing eLifeLog services on Live Duo for private lifelogging. Other network storages, if they support Linux, should be similarly supported using information in this lab.

Installation

First you have to buy it whether from Amazon or anywhere else, find a good deal and have it next to your home router. Follow the given setup instruction for initial installation. 

Technical Specifications to Know

Live Duo has a linux and things to know for system management are:

  • By default WD authorize to install packages from "debian lenny" and "squeeze" version
  • Package installation

Problems

Now you have a personal cloud but you will soon find an issue questioning whether this is a real private cloud or not because if you want to access your cloud, then you have to give all information to WD's service server. For instance if you want to browse photos at your home NAS server, then all data should be first uploaded to WD servers and then you access WD server (not your home cloud) to see the data which is probabily not what you pay for.

Modification

So people already have started to keep thier things under their hood. One problem of Live Duo's linux kernel is that any update of the WD firmware wipes out your own installed upgrades and new packages. WD perfomrs this update by swaping a file partition thus the delivered firmware (i.e. any update means reverting system parts, not user data, to the factory default) is not compatible with all upgrades you can make.

For eLifeLog services, a server should support (as of Augst 2013) APM (Apache+PHP+MySQL) and optionally Java for real-time CEP and additional packages for various media analysis. They are not completely embedded into the eLifeLog data due to its open architecture (and limited supports) to utilize existing technologies.

Start Installing What Nedded

Apache

Apache HTTP server is already there but to have more control and higher security follow this instruction to set up http web server. eLifeLog APIs work well both for HTTP and HTTPS.

PHP

PHP installation should follow this forum post. Don't go more than PHP installation.

MySQL

Install from Optware

 

OpenVPN

Now you want to access your private cloud from anywhere at anytime and do not resort to other free (but have to give away your personal data) gateway services. For this, install OpenVPN and register free dns service (ex. no-ip.org) . Also accordinly install client Apps or software to acccess your home cloud (For Mac, we used Tunnelblick). 

eLifeLog stuffs

Now you have a safe and a pretty secure private cloud (VPN + http). Visit eLifeLog source codes to install your lifelog managing software. No data will be transferred out of your private network from now on :)

Lifelog mining

Lifelog data is diverse in all aspects. This category is a collection of lifelog data analysis using our own data, contributed data or public data. Labs include preliminary data analysis or technical development for lifelog mining.

Microsoft GeoLife GPS Trajectories

GeoLife GPS Trajectories is a dataset collected in (Microsoft Research Asia) Geolife project by 182 users in a period of over three years (from April 2007 to August 2012). Recently they updated the data and the below is accordingly updated.

Data Description

This dataset is available at their download page. If you want to use 'wget' like tool, then check the source of this page to find the direct link to the file. File download size is 298.66 MB and it includes many PLT gps files and Labels.txt (Ground truth user transportation mode). More details on their data can be found at the included user guide in their download file.

Data Parsing and Database Preparation

We imported all into our MySQL database. Below is the sequence of commands to import and process all data. One difference with the old data is now that they have Labels.txt which is the ground-truth data tagged by users. So we redesigned tables like below.

Prepare MySQL Tables

We have two PLT tables: one for fast importing without indexing and the other for distinct row importing with the complete index. We may create a complete table at the first momemt but since it involves shell scripts and background process, let move the field to the MySQL, which is much easier and safer.

CREATE TABLE `plt` (
  `directory` varchar(10) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `flag` int(11) DEFAULT NULL,
  `altitude` double DEFAULT NULL,
  `passeddate` varchar(255) DEFAULT NULL,
  `gpsdate` date DEFAULT NULL,
  `gpstime` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `plt_distinct` (
  `directory` varchar(10) NOT NULL DEFAULT '',
  `latitude` double NOT NULL DEFAULT '0',
  `longitude` double NOT NULL DEFAULT '0',
  `flag` int(11) DEFAULT NULL,
  `altitude` double NOT NULL DEFAULT '0',
  `passeddate` varchar(255) DEFAULT NULL,
  `gpsdate` date NOT NULL DEFAULT '0000-00-00',
  `gpstime` time NOT NULL DEFAULT '00:00:00',
  `gpsdatetime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`directory`,`latitude`,`longitude`,`gpsdate`,`gpstime`,`altitude`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `labels` (
  `directory` varchar(10) NOT NULL DEFAULT '',
  `starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `endtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `transportationmode` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`directory`,`starttime`,`endtime`,`transportationmode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Importing data

Unlike old one below, let's do everything using shell scripts not PHP. Belows are BASH schell scripts. If you are using Windows, please find similar tools or use cygwin.

For both scripts below, first open the shell and move to the unzipped data directory (ex. GeolifeTrajectories1.3/Data) and then create script files using below script (Don't forget to set the execution attribute!)

#!/bin/bash                                                                     

shopt -s globstar

for file in **/*.plt
do
    if [[ ! -f "$file" ]]
    then
        continue
    fi  
    echo "$file"
    # Do something else.                                                          
    
    rm plt.txt
    ln -s "$file" "plt.txt"
    cols="latitude, longitude, @flag, altitude, passeddate, gpsdate, gpstime) SET directory = '${file:0:3}', flag=(@flag"
    mysqlimport -i -s --local --user=yourdbuserid --password=yourdbuserpassword --fields-terminated-by="," --lines-terminated-by="\r\n" --ignore-lines=6 --columns="$cols" userdbname plt.txt 
done

Below scripts import Labels.txt data from the GeoLife data set

 

#!/bin/bash                                                                     

shopt -s globstar

for file in **/labels.txt
do
    if [[ ! -f "$file" ]]
    then
        continue
    fi  
    echo "$file"
    # Do something else.                                                          
    
    rm labels.txt
    ln -s "$file" "labels.txt"
    cols="starttime, endtime, @transportationmode) SET directory = '${file:0:3}', transportationmode=(@transportationmode"
    
    mysqlimport --local --user=youruserid --password=yourpassword --fields-terminated-by="\t" --lines-terminated-by="\r\n" --ignore-lines=1 --columns="$cols" yourdbname labels.txt 
done

Once all steps are done (~< 1 hour), then execute MySQL client and check the data. If everything looks OK, then run below SQL commands.

 

INSERT IGNORE INTO plt_distinct (directory, latitude, longitude, flag, altitude, passeddate, gpsdate, gpstime, gpsdatetime)  SELECT directory, latitude, longitude, flag, altitude, passeddate, gpsdate, gpstime, CONCAT(gpsdate, ' ', gpstime) FROM plt;

As a statistics, there were total: 14718 labels and 5,405,681 GPS samples.

References

 

Obsolete

Note! Microsoft GeoLife GPS Trajectories data is changed and the below information is only compatible with their old (first version) data set.

This lab is to import Microsoft GeoLife GPS Trajectories data into the database.

Extract GPS trajectories

The dataset is composed of over two years (from April 2007 to August 2009) 165 users' GPS trajectories.

Data conversion for database import

Source GPS trajectories are in OziExplorer format (.plt extension). Its format is available at http://www.rus-roads.ru/gps/help_ozi/fileformats.html.

Table structure to store the plt format:

CREATE TABLE `geolife` (
  `gps_userid` int(11) DEFAULT NULL,
  `gps_latitude` double DEFAULT NULL,
  `gps_longitude` double DEFAULT NULL,
  `gps_code` int(11) DEFAULT NULL,
  `gps_altitude` double DEFAULT NULL,
  `gps_UTC_timestamp` timestamp NULL DEFAULT NULL,
  `gps_UTC_unix_timestamp` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

PHP scripts to import data into the MySQL table:

query($query))) {
	printf("Table truncation error: %s\n", $mysqli->error);
	exit();
}

// Recursively read all files in the selected directory 
$ite=new RecursiveDirectoryIterator($path);
$bytestotal=0;
$nbfiles=0;
$gpspath = $path;
$gpssubpath = '';

$ite_gps = new RecursiveDirectoryIterator($gpspath);
foreach (new RecursiveIteratorIterator($ite_gps) as $gpsfilename=>$gpscur) {
	// Check sub path
	$newgpspath = $gpscur->getPathname();
	$newgpsfile = $gpscur->getFileName();
	$newgpsfullpath = substr($newgpspath, 0, strlen($newgpspath) - strlen($newgpsfile));
	$newgpssubpath = substr($newgpsfullpath, strlen($gpspath) - strlen($newgpsfullpath));

	if ($gpssubpath != $newgpssubpath && is_dir($gpspath.$newgpssubpath)) {  
		$gpssubpath = $newgpssubpath;
		echo "Entering the subpath: $gpssubpath\n";
	}

	if (strstr($gpscur, ".plt") != "") {
		$filesize=$gpscur->getSize();
		$modified_time = $gpscur->getMTime();
		$bytestotal+=$filesize;
		$nbfiles++;
		echo "$gpsfilename => $filesize\n";

		// Load data into the table
		$user_id = str_replace("/trajectory/", "", $gpssubpath);
		$query = " LOAD DATA LOCAL INFILE '".$gpscur->getPathname()."' ";
	  	$query .= " IGNORE INTO TABLE geolife ";
		$query .= " FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 6 LINES ";
		$query .= " (gps_latitude, gps_longitude, gps_code, gps_altitude, @gps_dateformat, @gps_date, @gps_time) ";
		$query .= " SET gps_userid = '".$user_id."', ";
		$query .= " gps_UTC_timestamp = CONCAT(@gps_date, ' ', @gps_time), ";
		$query .= " gps_UTC_unix_timestamp = UNIX_TIMESTAMP(CONCAT(@gps_date, ' ', @gps_time))";
		
		if (!($result = $mysqli->query($query))) {
			echo $query."\n";
			printf("GPS data insertion error: %s\n", $mysqli->error);
			return 0;
		}
	}
}	

unset($gpsfilename, $query);

/* Update the photo timestamp by the location */

$mysqli->close();

$bytestotal=number_format($bytestotal);
echo "Total: $nbfiles files, $bytestotal bytes\n";

?>

As a statistics, there were total: 8809 files in 1,554,806,869 bytes.

 

 

Copyrights

Any members using this data should conform to the following agreement:

 

Microsoft Research License Agreement

Non-Commercial Use Only << GeoLife GPS Trajectories>>
This Microsoft Research License Agreement, including all exhibits ("MSR-LA") is a legal agreement between you and Microsoft Corporation (Microsoft or we) for the software or data identified above, which may include source code, and any associated materials, text or speech files, associated media and "online" or electronic documentation and any updates we provide in our discretion (together, the "Software").
By installing, copying, or otherwise using this Software, you agree to be bound by the terms of this MSR-LA. If you do not agree, do not install copy or use the Software. The Software is protected by copyright and other intellectual property laws and is licensed, not sold.
SCOPE OF RIGHTS: You may use this Software for any non-commercial purpose, subject to the restrictions in this MSR-LA. Some
purposes which can be non-commercial are teaching, academic research, public demonstrations and personal experimentation. You may not distribute this Software or any derivative works in any form. In return, we simply require that you agree: 1. That you will not remove any copyright or other notices from the Software.
2. That if any of the Software is in binary format, you will not attempt to modify such portions of the Software, or to reverse engineer or decompile them, except and only to the extent authorized by applicable law. 3. That Microsoft is granted back, without any restrictions or limitations, a non-exclusive, perpetual, irrevocable, royalty-free, assignable and sub-licensable license, to reproduce, publicly perform or display, install, use, modify, post, distribute, make and have made, sell and transfer your modifications to and/or derivative works of the Software source code or data, for any purpose.
4. That any feedback about the Software provided by you to us is voluntarily given, and Microsoft shall be free to use the feedback as it sees fit without obligation or restriction of any kind, even if the feedback is designated by you as confidential. 5. THAT THE SOFTWARE COMES "AS IS", WITH NO WARRANTIES. THIS MEANS NO EXPRESS, IMPLIED OR STATUTORY WARRANTY, INCLUDING WITHOUT LIMITATION, WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE, ANY WARRANTY AGAINST INTERFERENCE WITH YOUR ENJOYMENT OF THE SOFTWARE OR ANY WARRANTY OF TITLE OR NON-INFRINGEMENT. THERE IS NO WARRANTY THAT THIS SOFTWARE WILL FULFILL ANY OF YOUR PARTICULAR PURPOSES OR NEEDS.
6. THAT NEITHER MICROSOFT NOR ANY CONTRIBUTOR TO THE SOFTWARE WILL BE LIABLE FOR ANY DAMAGES RELATED TO THE SOFTWARE OR THIS MSR-LA, INCLUDING DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL OR INCIDENTAL DAMAGES, TO THE MAXIMUM EXTENT THE LAW PERMITS, NO MATTER WHAT LEGAL THEORY IT IS BASED ON.
7. That we have no duty of reasonable care or lack of negligence, and we are not obligated to (and will not) provide technical support for the Software. 8. That if you breach this MSR-LA or if you sue anyone over patents that you think may apply to or read on the Software or anyone's use of the Software, this MSR-LA (and your license and rights obtained herein) terminate automatically. Upon any such termination, you shall destroy all of your copies of the Software immediately. Sections 3, 4, 5, 6, 7, 8, 11 and 12 of this MSR-LA shall survive any termination of this MSR-LA.
9. That the patent rights, if any, granted to you in this MSR-LA only apply to the Software, not to any derivative works you make. 10. That the Software may be subject to U.S. export jurisdiction at the time it is licensed to you, and it may be subject to additional export or import laws in other places. You agree to comply with all such laws and regulations that may apply to the Software after delivery of the software to you.
11. That all rights not expressly granted to you in this MSR-LA are reserved. 12. That this MSR-LA shall be construed and controlled by the laws of the State of Washington, USA, without regard to conflicts of law. If any provision of this MSR-LA shall be deemed unenforceable or contrary to law, the rest of this MSR-LA shall remain in full effect and interpreted in an enforceable manner that most nearly captures the intent of the original language.
Copyright (c) Microsoft Corporation. All rights reserved.

Multimedia retrieval case studies

This lab is the collection of multimedia retrieval experiments on various lifelog cases. We mean by Multimedia as a combination of different content forms temporally aligned sensor streams.

Outdoor activities -- Motorbike rider's favorites are different

Bike riders often attach one or two GoPro cameras each on the bike and their helmet to record their driving life on the road. They also commonly carry GPS units to track their paths and to record the speed. We asked them whether they have ever reviewed their videos and the answers were same, "No." Simply the reason is the fact driving on the road is rather monotonous mostly the scene is filled up with roads and cars.

The next question is then what they would ask to the computer, assuming very intelligent computer, to show them what moments? Interestingly the moment they want to ask is most dangerous moments like when (1) reaching the highest speed, (2) turning the sharp corner almost nearly touching the ground, (3) passing over slow cars or (4) breaking sharply to avoid collision.

So now we understand that they really enjoy thrills but what multimedia retrieval experts should do to create a summary of videos for bike riders? 

Nokia Mobile Data Challenge 2012

We have applied to the Nokia Mobile Data Challenge 2012 Open Challenge and this lab will maintain the progress to share and improve the results. By the policy of contribution share specified by the Nokia MDC team, contents shared here are limited to efficient data processing, not related with our research contribution. Also the MDC data will not be available to the community member til we get the grant from Nokia. The access is limited to members who particiate in the MDC Open Challenge with Pil Ho Kim.

Process the Nokia MDC Data

Prepare the database

The SQL script to prepare the data tables. 

Below SQL statements are for the Nokia MDC data processing. We did not actually analyze their data since their data are too much anonymized and intentionally degraded (ex. GPS coordinates) to track one's activities. It has no multimedia data . Also the data set assigned to us includes only a few members lifelog (not hundreds as advertised) living far away with each other.
-- MySQL dump 10.13  Distrib 5.5.11, for Win64 (x86)
--
-- Host: localhost    Database: nokiamdc
-- ------------------------------------------------------
-- Server version	5.5.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `accel`
--

DROP TABLE IF EXISTS `accel`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `accel` (
  `userid` int(11) NOT NULL,
  `tz` int(11) NOT NULL,
  `start` int(11) NOT NULL,
  `stop` int(11) NOT NULL,
  `avdelt` double(15,3) DEFAULT NULL,
  `data` longtext,
  PRIMARY KEY (`userid`,`tz`,`start`,`stop`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `application`
--

DROP TABLE IF EXISTS `application`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `application` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) NOT NULL DEFAULT '0',
  `event` varchar(255) DEFAULT NULL,
  `uid` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`,`tz`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `bluetooth`
--

DROP TABLE IF EXISTS `bluetooth`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bluetooth` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) NOT NULL DEFAULT '0',
  `mac_prefix` varchar(50) NOT NULL DEFAULT '',
  `mac_address` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`,`tz`,`mac_prefix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `calendar`
--

DROP TABLE IF EXISTS `calendar`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `calendar` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) NOT NULL DEFAULT '0',
  `uid` int(10) NOT NULL DEFAULT '0',
  `status` varchar(50) DEFAULT NULL,
  `begin` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  `last_mod` int(11) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`,`tz`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `calllog`
--

DROP TABLE IF EXISTS `calllog`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `calllog` (
  `userid` int(10) DEFAULT NULL,
  `tz` int(10) DEFAULT NULL,
  `call_time` int(10) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `direction` varchar(50) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL,
  `number_prefix` varchar(50) DEFAULT NULL,
  `number` varchar(255) DEFAULT NULL,
  `in_phonebook` int(11) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `contacts`
--

DROP TABLE IF EXISTS `contacts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contacts` (
  `userid` int(10) DEFAULT NULL,
  `time` int(10) DEFAULT NULL,
  `tz` int(10) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `mobile_1_prefix` varchar(50) DEFAULT NULL,
  `mobile_1` varchar(255) DEFAULT NULL,
  `mobile_2_prefix` varchar(50) DEFAULT NULL,
  `mobile_2` varchar(255) DEFAULT NULL,
  `mobile_3_prefix` varchar(50) DEFAULT NULL,
  `mobile_3` varchar(255) DEFAULT NULL,
  `tel_1_prefix` varchar(50) DEFAULT NULL,
  `tel_1` varchar(255) DEFAULT NULL,
  `tel_2_prefix` varchar(50) DEFAULT NULL,
  `tel_2` varchar(255) DEFAULT NULL,
  `tel_3_prefix` varchar(50) DEFAULT NULL,
  `tel_3` varchar(255) DEFAULT NULL,
  `last_mod` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `devices`
--

DROP TABLE IF EXISTS `devices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `devices` (
  `mac_prefix` varchar(50) NOT NULL DEFAULT '',
  `mac_address` varchar(255) NOT NULL DEFAULT '',
  `model` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`mac_prefix`,`mac_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `gps`
--

DROP TABLE IF EXISTS `gps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gps` (
  `userid` int(10) NOT NULL,
  `time` int(10) NOT NULL,
  `tz` int(10) NOT NULL,
  `gps_time` int(10) NOT NULL,
  `altitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `speed` double DEFAULT NULL,
  `heading` double DEFAULT NULL,
  `horizontal_accuracy` double DEFAULT NULL,
  `horizontal_dop` double DEFAULT NULL,
  `vertical_accuracy` double DEFAULT NULL,
  `vertical_dop` double DEFAULT NULL,
  `speed_accuracy` double DEFAULT NULL,
  `heading_accuracy` double DEFAULT NULL,
  `time_since_gps_boot` double DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `gsm`
--

DROP TABLE IF EXISTS `gsm`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gsm` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) DEFAULT NULL,
  `country_code` int(10) DEFAULT NULL,
  `network_code` int(10) DEFAULT NULL,
  `cell_id` int(10) DEFAULT NULL,
  `area_code` int(10) DEFAULT NULL,
  `signal` double DEFAULT NULL,
  `signaldbm` double DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `media`
--

DROP TABLE IF EXISTS `media`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `media` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) DEFAULT NULL,
  `media_time` int(10) DEFAULT NULL,
  `media_tz` int(10) DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `mediaplay`
--

DROP TABLE IF EXISTS `mediaplay`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mediaplay` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) DEFAULT NULL,
  `album` varchar(255) DEFAULT NULL,
  `artist` varchar(255) DEFAULT NULL,
  `track` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `uri` varchar(255) DEFAULT NULL,
  `state` int(11) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `process`
--

DROP TABLE IF EXISTS `process`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `process` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) NOT NULL DEFAULT '0',
  `path` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`,`time`,`tz`,`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `questionnaire1`
--

DROP TABLE IF EXISTS `questionnaire1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `questionnaire1` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `q1` int(10) DEFAULT NULL,
  `q3` int(10) DEFAULT NULL,
  `q5` int(10) DEFAULT NULL,
  `q7a` int(10) DEFAULT NULL,
  `q7b` int(10) DEFAULT NULL,
  `q7c` int(10) DEFAULT NULL,
  `q7d` int(10) DEFAULT NULL,
  `q7e` int(10) DEFAULT NULL,
  `q7f` int(10) DEFAULT NULL,
  `q7g` int(10) DEFAULT NULL,
  `q10a` int(10) DEFAULT NULL,
  `q10b` int(10) DEFAULT NULL,
  `q10c` int(10) DEFAULT NULL,
  `q10d` int(10) DEFAULT NULL,
  `q10e` int(10) DEFAULT NULL,
  `q10f` int(10) DEFAULT NULL,
  `q10g` int(10) DEFAULT NULL,
  `q10h` int(10) DEFAULT NULL,
  `q11ar1` int(10) DEFAULT NULL,
  `q11ar2` int(10) DEFAULT NULL,
  `q11ar3` int(10) DEFAULT NULL,
  `q11ar4` int(10) DEFAULT NULL,
  `q11ar5` int(10) DEFAULT NULL,
  `q11ar6` int(10) DEFAULT NULL,
  `q11ar7` int(10) DEFAULT NULL,
  `q11ar8` int(10) DEFAULT NULL,
  `q11br1` int(10) DEFAULT NULL,
  `q11br2` int(10) DEFAULT NULL,
  `q11br3` int(10) DEFAULT NULL,
  `q11br4` int(10) DEFAULT NULL,
  `q11br5` int(10) DEFAULT NULL,
  `q11br6` int(10) DEFAULT NULL,
  `q11br7` int(10) DEFAULT NULL,
  `q11br8` int(10) DEFAULT NULL,
  `q11cr1` int(10) DEFAULT NULL,
  `q11cr2` int(10) DEFAULT NULL,
  `q11cr3` int(10) DEFAULT NULL,
  `q11cr4` int(10) DEFAULT NULL,
  `q11cr5` int(10) DEFAULT NULL,
  `q11cr6` int(10) DEFAULT NULL,
  `q11cr7` int(10) DEFAULT NULL,
  `q11cr8` int(10) DEFAULT NULL,
  `q11dr1` int(10) DEFAULT NULL,
  `q11dr2` int(10) DEFAULT NULL,
  `q11dr3` int(10) DEFAULT NULL,
  `q11dr4` int(10) DEFAULT NULL,
  `q11dr5` int(10) DEFAULT NULL,
  `q11dr6` int(10) DEFAULT NULL,
  `q11dr7` int(10) DEFAULT NULL,
  `q11dr8` int(10) DEFAULT NULL,
  `q11er1` int(10) DEFAULT NULL,
  `q11er2` int(10) DEFAULT NULL,
  `q11er3` int(10) DEFAULT NULL,
  `q11er4` int(10) DEFAULT NULL,
  `q11er5` int(10) DEFAULT NULL,
  `q11er6` int(10) DEFAULT NULL,
  `q11er7` int(10) DEFAULT NULL,
  `q11er8` int(10) DEFAULT NULL,
  `q11fr1` int(10) DEFAULT NULL,
  `q11fr2` int(10) DEFAULT NULL,
  `q11fr3` int(10) DEFAULT NULL,
  `q11fr4` int(10) DEFAULT NULL,
  `q11fr5` int(10) DEFAULT NULL,
  `q11fr6` int(10) DEFAULT NULL,
  `q11fr7` int(10) DEFAULT NULL,
  `q11fr8` int(10) DEFAULT NULL,
  `q11gr1` int(10) DEFAULT NULL,
  `q11gr2` int(10) DEFAULT NULL,
  `q11gr3` int(10) DEFAULT NULL,
  `q11gr4` int(10) DEFAULT NULL,
  `q11gr5` int(10) DEFAULT NULL,
  `q11gr6` int(10) DEFAULT NULL,
  `q11gr7` int(10) DEFAULT NULL,
  `q11gr8` int(10) DEFAULT NULL,
  `q11hr1` int(10) DEFAULT NULL,
  `q11hr2` int(10) DEFAULT NULL,
  `q11hr3` int(10) DEFAULT NULL,
  `q11hr4` int(10) DEFAULT NULL,
  `q11hr5` int(10) DEFAULT NULL,
  `q11hr6` int(10) DEFAULT NULL,
  `q11hr7` int(10) DEFAULT NULL,
  `q11hr8` int(10) DEFAULT NULL,
  `q12ar1` int(10) DEFAULT NULL,
  `q12ar2` int(10) DEFAULT NULL,
  `q12ar3` int(10) DEFAULT NULL,
  `q12br1` int(10) DEFAULT NULL,
  `q12br2` int(10) DEFAULT NULL,
  `q12br3` int(10) DEFAULT NULL,
  `q12cr1` int(10) DEFAULT NULL,
  `q12cr2` int(10) DEFAULT NULL,
  `q12cr3` int(10) DEFAULT NULL,
  `q12dr1` int(10) DEFAULT NULL,
  `q12dr2` int(10) DEFAULT NULL,
  `q12dr3` int(10) DEFAULT NULL,
  `q12er1` int(10) DEFAULT NULL,
  `q12er2` int(10) DEFAULT NULL,
  `q12er3` int(10) DEFAULT NULL,
  `q12fr1` int(10) DEFAULT NULL,
  `q12fr2` int(10) DEFAULT NULL,
  `q12fr3` int(10) DEFAULT NULL,
  `q12gr1` int(10) DEFAULT NULL,
  `q12gr2` int(10) DEFAULT NULL,
  `q12gr3` int(10) DEFAULT NULL,
  `q12hr1` int(10) DEFAULT NULL,
  `q12hr2` int(10) DEFAULT NULL,
  `q12hr3` int(10) DEFAULT NULL,
  `q12ir1` int(10) DEFAULT NULL,
  `q12ir2` int(10) DEFAULT NULL,
  `q12ir3` int(10) DEFAULT NULL,
  `q12jr1` int(10) DEFAULT NULL,
  `q12jr2` int(10) DEFAULT NULL,
  `q12jr3` int(10) DEFAULT NULL,
  `q12kr1` int(10) DEFAULT NULL,
  `q12kr2` int(10) DEFAULT NULL,
  `q12kr3` int(10) DEFAULT NULL,
  `q13` int(10) DEFAULT NULL,
  `q14a` int(10) DEFAULT NULL,
  `q14b` int(10) DEFAULT NULL,
  `q15ar1` int(10) DEFAULT NULL,
  `q15ar2` int(10) DEFAULT NULL,
  `q15ar3` int(10) DEFAULT NULL,
  `q15ar4` int(10) DEFAULT NULL,
  `q15ar5` int(10) DEFAULT NULL,
  `q15ar6` int(10) DEFAULT NULL,
  `q15ar7` int(10) DEFAULT NULL,
  `q15ar8` int(10) DEFAULT NULL,
  `q15br1` int(10) DEFAULT NULL,
  `q15br2` int(10) DEFAULT NULL,
  `q15br3` int(10) DEFAULT NULL,
  `q15br4` int(10) DEFAULT NULL,
  `q15br5` int(10) DEFAULT NULL,
  `q15br6` int(10) DEFAULT NULL,
  `q15br7` int(10) DEFAULT NULL,
  `q15br8` int(10) DEFAULT NULL,
  `q15cr1` int(10) DEFAULT NULL,
  `q15cr2` int(10) DEFAULT NULL,
  `q15cr3` int(10) DEFAULT NULL,
  `q15cr4` int(10) DEFAULT NULL,
  `q15cr5` int(10) DEFAULT NULL,
  `q15cr6` int(10) DEFAULT NULL,
  `q15cr7` int(10) DEFAULT NULL,
  `q15cr8` int(10) DEFAULT NULL,
  `q15dr1` int(10) DEFAULT NULL,
  `q15dr2` int(10) DEFAULT NULL,
  `q15dr3` int(10) DEFAULT NULL,
  `q15dr4` int(10) DEFAULT NULL,
  `q15dr5` int(10) DEFAULT NULL,
  `q15dr6` int(10) DEFAULT NULL,
  `q15dr7` int(10) DEFAULT NULL,
  `q15dr8` int(10) DEFAULT NULL,
  `q15er1` int(10) DEFAULT NULL,
  `q15er2` int(10) DEFAULT NULL,
  `q15er3` int(10) DEFAULT NULL,
  `q15er4` int(10) DEFAULT NULL,
  `q15er5` int(10) DEFAULT NULL,
  `q15er6` int(10) DEFAULT NULL,
  `q15er7` int(10) DEFAULT NULL,
  `q15er8` int(10) DEFAULT NULL,
  `q15fr1` int(10) DEFAULT NULL,
  `q15fr2` int(10) DEFAULT NULL,
  `q15fr3` int(10) DEFAULT NULL,
  `q15fr4` int(10) DEFAULT NULL,
  `q15fr5` int(10) DEFAULT NULL,
  `q15fr6` int(10) DEFAULT NULL,
  `q15fr7` int(10) DEFAULT NULL,
  `q15fr8` int(10) DEFAULT NULL,
  `q15gr1` int(10) DEFAULT NULL,
  `q15gr2` int(10) DEFAULT NULL,
  `q15gr3` int(10) DEFAULT NULL,
  `q15gr4` int(10) DEFAULT NULL,
  `q15gr5` int(10) DEFAULT NULL,
  `q15gr6` int(10) DEFAULT NULL,
  `q15gr7` int(10) DEFAULT NULL,
  `q15gr8` int(10) DEFAULT NULL,
  `q15hr1` int(10) DEFAULT NULL,
  `q15hr2` int(10) DEFAULT NULL,
  `q15hr3` int(10) DEFAULT NULL,
  `q15hr4` int(10) DEFAULT NULL,
  `q15hr5` int(10) DEFAULT NULL,
  `q15hr6` int(10) DEFAULT NULL,
  `q15hr7` int(10) DEFAULT NULL,
  `q15hr8` int(10) DEFAULT NULL,
  `q15ir1` int(10) DEFAULT NULL,
  `q15ir2` int(10) DEFAULT NULL,
  `q15ir3` int(10) DEFAULT NULL,
  `q15ir4` int(10) DEFAULT NULL,
  `q15ir5` int(10) DEFAULT NULL,
  `q15ir6` int(10) DEFAULT NULL,
  `q15ir7` int(10) DEFAULT NULL,
  `q15ir8` int(10) DEFAULT NULL,
  `q16a` int(10) DEFAULT NULL,
  `q16b` int(10) DEFAULT NULL,
  `q16c` int(10) DEFAULT NULL,
  `q16d` int(10) DEFAULT NULL,
  `q16f` int(10) DEFAULT NULL,
  `q16g` int(10) DEFAULT NULL,
  `q16h` int(10) DEFAULT NULL,
  `q16i` int(10) DEFAULT NULL,
  `q16j` int(10) DEFAULT NULL,
  `q16k` int(10) DEFAULT NULL,
  `q16l` int(10) DEFAULT NULL,
  `q16m` int(10) DEFAULT NULL,
  `q16n` int(10) DEFAULT NULL,
  `q16o` int(10) DEFAULT NULL,
  `q16p` int(10) DEFAULT NULL,
  `q16q` int(10) DEFAULT NULL,
  `q16r` int(10) DEFAULT NULL,
  `q16s` int(10) DEFAULT NULL,
  `q16t` int(10) DEFAULT NULL,
  `q16u` int(10) DEFAULT NULL,
  `q16v` int(10) DEFAULT NULL,
  `q16w` int(10) DEFAULT NULL,
  `q16x` int(10) DEFAULT NULL,
  `q16y` int(10) DEFAULT NULL,
  `q16z` int(10) DEFAULT NULL,
  `q17a_part` int(10) DEFAULT NULL,
  `q17a_freq` int(10) DEFAULT NULL,
  `q17b_part` int(10) DEFAULT NULL,
  `q17b_freq` int(10) DEFAULT NULL,
  `q17c_part` int(10) DEFAULT NULL,
  `q17c_freq` int(10) DEFAULT NULL,
  `q17d_part` int(10) DEFAULT NULL,
  `q17d_freq` int(10) DEFAULT NULL,
  `q17e_part` int(10) DEFAULT NULL,
  `q17e_freq` int(10) DEFAULT NULL,
  `q17f_part` int(10) DEFAULT NULL,
  `q17f_freq` int(10) DEFAULT NULL,
  `q17g_part` int(10) DEFAULT NULL,
  `q17g_freq` int(10) DEFAULT NULL,
  `q17h_part` int(10) DEFAULT NULL,
  `q17h_freq` int(10) DEFAULT NULL,
  `q17i_part` int(10) DEFAULT NULL,
  `q17i_freq` int(10) DEFAULT NULL,
  `q17j_part` int(10) DEFAULT NULL,
  `q17j_freq` int(10) DEFAULT NULL,
  `q17k_part` int(10) DEFAULT NULL,
  `q17k_freq` int(10) DEFAULT NULL,
  `q17l_part` int(10) DEFAULT NULL,
  `q17l_freq` int(10) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `sys`
--

DROP TABLE IF EXISTS `sys`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sys` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) NOT NULL DEFAULT '0',
  `profile` varchar(50) DEFAULT NULL,
  `battery` int(11) DEFAULT NULL,
  `charging` int(11) DEFAULT NULL,
  `freespace_c` int(11) DEFAULT NULL,
  `freespace_d` int(11) DEFAULT NULL,
  `freespace_e` int(11) DEFAULT NULL,
  `freespace_y` int(11) DEFAULT NULL,
  `freespace_z` int(11) DEFAULT NULL,
  `inactive` int(11) DEFAULT NULL,
  `ring` varchar(50) DEFAULT NULL,
  `freeram` int(11) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`,`tz`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_presence`
--

DROP TABLE IF EXISTS `user_presence`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_presence` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `day` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`,`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `phone_prefix` varchar(50) NOT NULL DEFAULT '',
  `phonenumber` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`,`phone_prefix`,`phonenumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wlan`
--

DROP TABLE IF EXISTS `wlan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wlan` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) NOT NULL DEFAULT '0',
  `mac_prefix` varchar(50) NOT NULL DEFAULT '',
  `mac_address` varchar(255) NOT NULL DEFAULT '',
  `ssid` varchar(255) DEFAULT NULL,
  `rx` int(10) DEFAULT NULL,
  `channel` int(10) DEFAULT NULL,
  `security` varchar(50) DEFAULT NULL,
  `opmode` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`,`tz`,`mac_prefix`,`mac_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wlan_loc`
--

DROP TABLE IF EXISTS `wlan_loc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wlan_loc` (
  `userid` int(10) NOT NULL DEFAULT '0',
  `time` int(10) NOT NULL DEFAULT '0',
  `tz` int(10) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `mac_prefix` varchar(50) DEFAULT NULL,
  `mac_address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-09 15:29:57

Process the MDC data

The PHP script to import the data set. 

query($query))) {
		printf("Table truncation error: %s\n", $mysqli->error);
		exit();
	}
}

// Recursively read all files in the selected directory 
$ite = new RecursiveDirectoryIterator($path);
foreach (new RecursiveIteratorIterator($ite) as $filename=>$cur) {
	if (strstr($cur, ".csv") != "") {
		echo "Processing: ".$cur->getPathName().PHP_EOL;

		$basename = substr($cur->getFilename(), 0, strlen($cur->getFilename()) - 4);
		$filepath = str_ireplace("\\", "/", $cur->getPathname());

		$query = "LOAD DATA LOW_PRIORITY LOCAL INFILE '".$filepath."' IGNORE INTO TABLE `".$basename."` FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' IGNORE 1 LINES";
		// $query = mysql_real_escape_string($query);

		if (!($result = $mysqli->query($query))) {
			echo $query.PHP_EOL;
			printf("Data insertion error: %s".PHP_EOL."Errors: %s".PHP_EOL, $query, $mysqli->error);
		}
	}
}	

/* Update the photo timestamp by the location */

$mysqli->close();

?>

 

OpenStreetMap Database & Nominatim

OpenStreetMap (OSM) is built by a community of mappers that contribute and maintain data about roads, trails, cafés, railway stations, and much more, all over the world. Its database is open to the public and hackers have contributed various tools to utilize this open data. Nominatim is one of OSM tools to search OSM data by name and address and to generate synthetic addresses of OSM points (reverse geocoding). It can be found at nominatim.openstreetmap.org too. 

This lab records our testing with nominatim to use it for reverse geocoding of our GPS logs. The practical needs of this lab comes from the limited public reverse geocoding service most of which limits the access to their API services including Google. 

Database

Instructions to download database and install nominatim is available at http://wiki.openstreetmap.org/wiki/Nominatim/Installation http://wiki.openstreetmap.org/wiki/Nominatim/Installation. Tips to modify to work with your systems are:

Experiments

We followed the instruction and the code at https://github.com/twain47/Nominatim at our Ubuntu machine. Note that their code and instruction did not work at our OS X environments. This limits the use of Nominatim service limited to its Web-based service which we do not like due to the speed and waste of computing power. So we looked into their code for reverse geocoding part (which we need at first) and revese engineered :) the Postgre SQL statements using a test data. The below code is also available at https://gist.github.com/pilhokim/bc7b4f66cdb8117d6edc

PgSQL Function For Direct Reverse Geocoding 

To perform the reverse coding purely based on the database, classtype data should be imported into the table. It is extracted from the PHP code for SQL table. So please create this table first and run following functions. 

Prepare Input Data

To perform the reverse coding purely based on the database, classtype data should be imported into the table. It is extracted from the PHP code for SQ

-- Table: my_gpsdata

-- DROP TABLE my_gpsdata;

CREATE TABLE my_gpsdata
(
  latitude double precision NOT NULL,
  longitude double precision NOT NULL,
  elevation double precision NOT NULL,
  unixtimestamp integer NOT NULL,
  eml_event_timestamp timestamp(6) without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
  CONSTRAINT my_gpsdata_key PRIMARY KEY (latitude, longitude, unixtimestamp, eml_event_timestamp)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE my_gpsdata
  OWNER TO postgres;

 

Perform Reverse Geocoding

Then run gps_get_placeids() and gps_reverse_geocode_from_placeids() in sequence.

--
-- PostgreSQL database dump
-- SQL version of getClassTypes array defined in Nominatim/lib.php
-- Excerpted and transformed for PQSQL by Pil Ho Kim, 2014
-- This creates a classtype table and put class type data extracted from Nominatim source codes

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: classtype; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE classtype (
    id integer,
    classtype character varying(255) NOT NULL,
    properties text,
    label character varying(255),
    frequency integer,
    icon character varying(255),
    defzoom integer,
    defdiameter double precision,
    simplelabel character varying(255)
);


ALTER TABLE public.classtype OWNER TO postgres;

--
-- Data for Name: classtype; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (1, 'boundary:administrative:1', '"label"=>"Continent","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'Continent', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (2, 'boundary:administrative:2', '"label"=>"Country","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'Country', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (3, 'place:country', '"label"=>"Country","frequency"=>0,"icon"=>"poi_boundary_administrative","defzoom"=>6,"defdiameter" => 15', 'Country', 0, 'poi_boundary_administrative', 6, 15, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (4, 'boundary:administrative:3', '"label"=>"State","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'State', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (5, 'boundary:administrative:4', '"label"=>"State","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'State', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (6, 'place:state', '"label"=>"State","frequency"=>0,"icon"=>"poi_boundary_administrative","defzoom"=>8,"defdiameter" => 5.12', 'State', 0, 'poi_boundary_administrative', 8, 5.12000000000000011, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (7, 'boundary:administrative:5', '"label"=>"State District","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'State District', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (8, 'boundary:administrative:6', '"label"=>"County","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'County', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (9, 'boundary:administrative:7', '"label"=>"County","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'County', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (10, 'place:county', '"label"=>"County","frequency"=>108,"icon"=>"poi_boundary_administrative","defzoom"=>10,"defdiameter" => 1.28', 'County', 108, 'poi_boundary_administrative', 10, 1.28000000000000003, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (11, 'boundary:administrative:8', '"label"=>"City","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'City', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (12, 'place:city', '"label"=>"City","frequency"=>66,"icon"=>"poi_place_city","defzoom"=>12,"defdiameter" => 0.32', 'City', 66, 'poi_place_city', 12, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (13, 'boundary:administrative:9', '"label"=>"City District","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'City District', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (14, 'boundary:administrative:10', '"label"=>"Suburb","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'Suburb', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (15, 'boundary:administrative:11', '"label"=>"Neighbourhood","frequency"=>0,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'Neighbourhood', 0, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (16, 'place:region', '"label"=>"Region","frequency"=>0,"icon"=>"poi_boundary_administrative","defzoom"=>8,"defdiameter" => 0.04', 'Region', 0, 'poi_boundary_administrative', 8, 0.0400000000000000008, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (17, 'place:island', '"label"=>"Island","frequency"=>288,"icon"=>"","defzoom"=>11,"defdiameter" => 0.64', 'Island', 288, NULL, 11, 0.640000000000000013, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (18, 'boundary:administrative', '"label"=>"Administrative","frequency"=>413,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'Administrative', 413, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (19, 'boundary:postal_code', '"label"=>"Postcode","frequency"=>413,"icon"=>"poi_boundary_administrative","defdiameter" => 0.32', 'Postcode', 413, 'poi_boundary_administrative', NULL, 0.320000000000000007, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (20, 'place:town', '"label"=>"Town","frequency"=>1497,"icon"=>"poi_place_town","defzoom"=>14,"defdiameter" => 0.08', 'Town', 1497, 'poi_place_town', 14, 0.0800000000000000017, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (21, 'place:village', '"label"=>"Village","frequency"=>11230,"icon"=>"poi_place_village","defzoom"=>15,"defdiameter" => 0.04', 'Village', 11230, 'poi_place_village', 15, 0.0400000000000000008, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (22, 'place:hamlet', '"label"=>"Hamlet","frequency"=>7075,"icon"=>"poi_place_village","defzoom"=>15,"defdiameter" => 0.04', 'Hamlet', 7075, 'poi_place_village', 15, 0.0400000000000000008, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (23, 'place:suburb', '"label"=>"Suburb","frequency"=>2528,"icon"=>"poi_place_village","defdiameter" => 0.04', 'Suburb', 2528, 'poi_place_village', NULL, 0.0400000000000000008, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (24, 'place:locality', '"label"=>"Locality","frequency"=>4113,"icon"=>"poi_place_village","defdiameter" => 0.02', 'Locality', 4113, 'poi_place_village', NULL, 0.0200000000000000004, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (25, 'landuse:farm', '"label"=>"Farm","frequency"=>1201,"icon"=>"","defdiameter" => 0.02', 'Farm', 1201, NULL, NULL, 0.0200000000000000004, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (26, 'place:farm', '"label"=>"Farm","frequency"=>1162,"icon"=>"","defdiameter" => 0.02', 'Farm', 1162, NULL, NULL, 0.0200000000000000004, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (27, 'highway:motorway_junction', '"label"=>"Motorway Junction","frequency"=>1126,"icon"=>"","simplelabel"=>"Road"', 'Motorway Junction', 1126, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (28, 'highway:motorway', '"label"=>"Motorway","frequency"=>4627,"icon"=>"","simplelabel"=>"Road"', 'Motorway', 4627, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (29, 'highway:trunk', '"label"=>"Trunk","frequency"=>23084,"icon"=>"","simplelabel"=>"Road"', 'Trunk', 23084, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (30, 'highway:primary', '"label"=>"Primary","frequency"=>32138,"icon"=>"","simplelabel"=>"Road"', 'Primary', 32138, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (31, 'highway:secondary', '"label"=>"Secondary","frequency"=>25807,"icon"=>"","simplelabel"=>"Road"', 'Secondary', 25807, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (32, 'highway:tertiary', '"label"=>"Tertiary","frequency"=>29829,"icon"=>"","simplelabel"=>"Road"', 'Tertiary', 29829, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (33, 'highway:residential', '"label"=>"Residential","frequency"=>361498,"icon"=>"","simplelabel"=>"Road"', 'Residential', 361498, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (34, 'highway:unclassified', '"label"=>"Unclassified","frequency"=>66441,"icon"=>"","simplelabel"=>"Road"', 'Unclassified', 66441, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (35, 'highway:living_street', '"label"=>"Living Street","frequency"=>710,"icon"=>"","simplelabel"=>"Road"', 'Living Street', 710, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (36, 'highway:service', '"label"=>"Service","frequency"=>9963,"icon"=>"","simplelabel"=>"Road"', 'Service', 9963, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (37, 'highway:track', '"label"=>"Track","frequency"=>2565,"icon"=>"","simplelabel"=>"Road"', 'Track', 2565, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (38, 'highway:road', '"label"=>"Road","frequency"=>591,"icon"=>"","simplelabel"=>"Road"', 'Road', 591, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (39, 'highway:byway', '"label"=>"Byway","frequency"=>346,"icon"=>"","simplelabel"=>"Road"', 'Byway', 346, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (40, 'highway:bridleway', '"label"=>"Bridleway","frequency"=>1556,"icon"=>""', 'Bridleway', 1556, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (41, 'highway:cycleway', '"label"=>"Cycleway","frequency"=>2419,"icon"=>""', 'Cycleway', 2419, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (42, 'highway:pedestrian', '"label"=>"Pedestrian","frequency"=>2757,"icon"=>""', 'Pedestrian', 2757, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (43, 'highway:footway', '"label"=>"Footway","frequency"=>15008,"icon"=>""', 'Footway', 15008, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (44, 'highway:steps', '"label"=>"Steps","frequency"=>444,"icon"=>"","simplelabel"=>"Footway"', 'Steps', 444, NULL, NULL, NULL, 'Footway');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (45, 'highway:motorway_link', '"label"=>"Motorway Link","frequency"=>795,"icon"=>"","simplelabel"=>"Road"', 'Motorway Link', 795, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (46, 'highway:trunk_link', '"label"=>"Trunk Link","frequency"=>1258,"icon"=>"","simplelabel"=>"Road"', 'Trunk Link', 1258, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (47, 'highway:primary_link', '"label"=>"Primary Link","frequency"=>313,"icon"=>"","simplelabel"=>"Road"', 'Primary Link', 313, NULL, NULL, NULL, 'Road');
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (48, 'landuse:industrial', '"label"=>"Industrial","frequency"=>1062,"icon"=>""', 'Industrial', 1062, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (49, 'landuse:residential', '"label"=>"Residential","frequency"=>886,"icon"=>""', 'Residential', 886, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (50, 'landuse:retail', '"label"=>"Retail","frequency"=>754,"icon"=>""', 'Retail', 754, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (51, 'landuse:commercial', '"label"=>"Commercial","frequency"=>657,"icon"=>""', 'Commercial', 657, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (52, 'place:airport', '"label"=>"Airport","frequency"=>36,"icon"=>"transport_airport2","defdiameter" => 0.03', 'Airport', 36, 'transport_airport2', NULL, 0.0299999999999999989, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (53, 'aeroway:aerodrome', '"label"=>"Aerodrome","frequency"=>36,"icon"=>"transport_airport2","defdiameter" => 0.03', 'Aerodrome', 36, 'transport_airport2', NULL, 0.0299999999999999989, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (54, 'aeroway', '"label"=>"Aeroway","frequency"=>36,"icon"=>"transport_airport2","defdiameter" => 0.03', 'Aeroway', 36, 'transport_airport2', NULL, 0.0299999999999999989, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (55, 'railway:station', '"label"=>"Station","frequency"=>3431,"icon"=>"transport_train_station2","defdiameter" => 0.01', 'Station', 3431, 'transport_train_station2', NULL, 0.0100000000000000002, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (56, 'amenity:place_of_worship', '"label"=>"Place Of Worship","frequency"=>9049,"icon"=>"place_of_worship_unknown3"', 'Place Of Worship', 9049, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (57, 'amenity:pub', '"label"=>"Pub","frequency"=>18969,"icon"=>"food_pub"', 'Pub', 18969, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (58, 'amenity:bar', '"label"=>"Bar","frequency"=>164,"icon"=>"food_bar"', 'Bar', 164, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (59, 'amenity:university', '"label"=>"University","frequency"=>607,"icon"=>"education_university"', 'University', 607, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (60, 'tourism:museum', '"label"=>"Museum","frequency"=>543,"icon"=>"tourist_museum"', 'Museum', 543, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (61, 'amenity:arts_centre', '"label"=>"Arts Centre","frequency"=>136,"icon"=>"tourist_art_gallery2"', 'Arts Centre', 136, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (62, 'tourism:zoo', '"label"=>"Zoo","frequency"=>47,"icon"=>"tourist_zoo"', 'Zoo', 47, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (63, 'tourism:theme_park', '"label"=>"Theme Park","frequency"=>24,"icon"=>"poi_point_of_interest"', 'Theme Park', 24, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (64, 'tourism:attraction', '"label"=>"Attraction","frequency"=>1463,"icon"=>"poi_point_of_interest"', 'Attraction', 1463, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (65, 'leisure:golf_course', '"label"=>"Golf Course","frequency"=>712,"icon"=>"sport_golf"', 'Golf Course', 712, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (66, 'historic:castle', '"label"=>"Castle","frequency"=>316,"icon"=>"tourist_castle"', 'Castle', 316, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (67, 'amenity:hospital', '"label"=>"Hospital","frequency"=>879,"icon"=>"health_hospital"', 'Hospital', 879, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (68, 'amenity:school', '"label"=>"School","frequency"=>8192,"icon"=>"education_school"', 'School', 8192, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (69, 'amenity:theatre', '"label"=>"Theatre","frequency"=>371,"icon"=>"tourist_theatre"', 'Theatre', 371, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (70, 'amenity:public_building', '"label"=>"Public Building","frequency"=>985,"icon"=>""', 'Public Building', 985, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (71, 'amenity:library', '"label"=>"Library","frequency"=>794,"icon"=>"amenity_library"', 'Library', 794, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (72, 'amenity:townhall', '"label"=>"Townhall","frequency"=>242,"icon"=>""', 'Townhall', 242, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (73, 'amenity:community_centre', '"label"=>"Community Centre","frequency"=>157,"icon"=>""', 'Community Centre', 157, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (74, 'amenity:fire_station', '"label"=>"Fire Station","frequency"=>221,"icon"=>"amenity_firestation3"', 'Fire Station', 221, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (75, 'amenity:police', '"label"=>"Police","frequency"=>334,"icon"=>"amenity_police2"', 'Police', 334, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (76, 'amenity:bank', '"label"=>"Bank","frequency"=>1248,"icon"=>"money_bank2"', 'Bank', 1248, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (77, 'amenity:post_office', '"label"=>"Post Office","frequency"=>859,"icon"=>"amenity_post_office"', 'Post Office', 859, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (78, 'leisure:park', '"label"=>"Park","frequency"=>2378,"icon"=>""', 'Park', 2378, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (79, 'amenity:park', '"label"=>"Park","frequency"=>53,"icon"=>""', 'Park', 53, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (80, 'landuse:park', '"label"=>"Park","frequency"=>50,"icon"=>""', 'Park', 50, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (81, 'landuse:recreation_ground', '"label"=>"Recreation Ground","frequency"=>517,"icon"=>""', 'Recreation Ground', 517, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (82, 'tourism:hotel', '"label"=>"Hotel","frequency"=>2150,"icon"=>"accommodation_hotel2"', 'Hotel', 2150, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (83, 'tourism:motel', '"label"=>"Motel","frequency"=>43,"icon"=>""', 'Motel', 43, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (84, 'amenity:cinema', '"label"=>"Cinema","frequency"=>277,"icon"=>"tourist_cinema"', 'Cinema', 277, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (85, 'tourism:information', '"label"=>"Information","frequency"=>224,"icon"=>"amenity_information"', 'Information', 224, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (86, 'tourism:artwork', '"label"=>"Artwork","frequency"=>171,"icon"=>"tourist_art_gallery2"', 'Artwork', 171, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (87, 'historic:archaeological_site', '"label"=>"Archaeological Site","frequency"=>407,"icon"=>"tourist_archaeological2"', 'Archaeological Site', 407, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (88, 'amenity:doctors', '"label"=>"Doctors","frequency"=>581,"icon"=>"health_doctors"', 'Doctors', 581, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (89, 'leisure:sports_centre', '"label"=>"Sports Centre","frequency"=>767,"icon"=>"sport_leisure_centre"', 'Sports Centre', 767, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (90, 'leisure:swimming_pool', '"label"=>"Swimming Pool","frequency"=>24,"icon"=>"sport_swimming_outdoor"', 'Swimming Pool', 24, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (91, 'shop:supermarket', '"label"=>"Supermarket","frequency"=>2673,"icon"=>"shopping_supermarket"', 'Supermarket', 2673, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (92, 'shop:convenience', '"label"=>"Convenience","frequency"=>1469,"icon"=>"shopping_convenience"', 'Convenience', 1469, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (93, 'amenity:restaurant', '"label"=>"Restaurant","frequency"=>3179,"icon"=>"food_restaurant"', 'Restaurant', 3179, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (94, 'amenity:fast_food', '"label"=>"Fast Food","frequency"=>2289,"icon"=>"food_fastfood"', 'Fast Food', 2289, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (95, 'amenity:cafe', '"label"=>"Cafe","frequency"=>1780,"icon"=>"food_cafe"', 'Cafe', 1780, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (96, 'tourism:guest_house', '"label"=>"Guest House","frequency"=>223,"icon"=>"accommodation_bed_and_breakfast"', 'Guest House', 223, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (97, 'amenity:pharmacy', '"label"=>"Pharmacy","frequency"=>733,"icon"=>"health_pharmacy_dispensing"', 'Pharmacy', 733, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (98, 'amenity:fuel', '"label"=>"Fuel","frequency"=>1308,"icon"=>"transport_fuel"', 'Fuel', 1308, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (99, 'natural:peak', '"label"=>"Peak","frequency"=>3212,"icon"=>"poi_peak"', 'Peak', 3212, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (100, 'waterway:waterfall', '"label"=>"Waterfall","frequency"=>24,"icon"=>""', 'Waterfall', 24, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (101, 'natural:wood', '"label"=>"Wood","frequency"=>1845,"icon"=>"landuse_coniferous_and_deciduous"', 'Wood', 1845, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (102, 'natural:water', '"label"=>"Water","frequency"=>1790,"icon"=>""', 'Water', 1790, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (103, 'landuse:forest', '"label"=>"Forest","frequency"=>467,"icon"=>""', 'Forest', 467, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (104, 'landuse:cemetery', '"label"=>"Cemetery","frequency"=>463,"icon"=>""', 'Cemetery', 463, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (105, 'landuse:allotments', '"label"=>"Allotments","frequency"=>408,"icon"=>""', 'Allotments', 408, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (106, 'landuse:farmyard', '"label"=>"Farmyard","frequency"=>397,"icon"=>""', 'Farmyard', 397, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (107, 'railway:rail', '"label"=>"Rail","frequency"=>4894,"icon"=>""', 'Rail', 4894, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (108, 'waterway:canal', '"label"=>"Canal","frequency"=>1723,"icon"=>""', 'Canal', 1723, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (109, 'waterway:river', '"label"=>"River","frequency"=>4089,"icon"=>""', 'River', 4089, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (110, 'waterway:stream', '"label"=>"Stream","frequency"=>2684,"icon"=>""', 'Stream', 2684, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (111, 'shop:bicycle', '"label"=>"Bicycle","frequency"=>349,"icon"=>"shopping_bicycle"', 'Bicycle', 349, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (112, 'shop:clothes', '"label"=>"Clothes","frequency"=>315,"icon"=>"shopping_clothes"', 'Clothes', 315, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (113, 'shop:hairdresser', '"label"=>"Hairdresser","frequency"=>312,"icon"=>"shopping_hairdresser"', 'Hairdresser', 312, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (114, 'shop:doityourself', '"label"=>"Doityourself","frequency"=>247,"icon"=>"shopping_diy"', 'Doityourself', 247, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (115, 'shop:estate_agent', '"label"=>"Estate Agent","frequency"=>162,"icon"=>"shopping_estateagent2"', 'Estate Agent', 162, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (116, 'shop:car', '"label"=>"Car","frequency"=>159,"icon"=>"shopping_car"', 'Car', 159, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (117, 'shop:garden_centre', '"label"=>"Garden Centre","frequency"=>143,"icon"=>"shopping_garden_centre"', 'Garden Centre', 143, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (118, 'shop:car_repair', '"label"=>"Car Repair","frequency"=>141,"icon"=>"shopping_car_repair"', 'Car Repair', 141, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (119, 'shop:newsagent', '"label"=>"Newsagent","frequency"=>132,"icon"=>""', 'Newsagent', 132, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (120, 'shop:bakery', '"label"=>"Bakery","frequency"=>129,"icon"=>"shopping_bakery"', 'Bakery', 129, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (121, 'shop:furniture', '"label"=>"Furniture","frequency"=>124,"icon"=>""', 'Furniture', 124, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (122, 'shop:butcher', '"label"=>"Butcher","frequency"=>105,"icon"=>"shopping_butcher"', 'Butcher', 105, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (123, 'shop:apparel', '"label"=>"Apparel","frequency"=>98,"icon"=>"shopping_clothes"', 'Apparel', 98, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (124, 'shop:electronics', '"label"=>"Electronics","frequency"=>96,"icon"=>""', 'Electronics', 96, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (125, 'shop:department_store', '"label"=>"Department Store","frequency"=>86,"icon"=>""', 'Department Store', 86, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (126, 'shop:books', '"label"=>"Books","frequency"=>85,"icon"=>""', 'Books', 85, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (127, 'shop:yes', '"label"=>"Yes","frequency"=>68,"icon"=>""', 'Yes', 68, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (128, 'shop:outdoor', '"label"=>"Outdoor","frequency"=>67,"icon"=>""', 'Outdoor', 67, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (129, 'shop:mall', '"label"=>"Mall","frequency"=>63,"icon"=>""', 'Mall', 63, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (130, 'shop:florist', '"label"=>"Florist","frequency"=>61,"icon"=>""', 'Florist', 61, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (131, 'shop:charity', '"label"=>"Charity","frequency"=>60,"icon"=>""', 'Charity', 60, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (132, 'shop:hardware', '"label"=>"Hardware","frequency"=>59,"icon"=>""', 'Hardware', 59, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (133, 'shop:laundry', '"label"=>"Laundry","frequency"=>51,"icon"=>"shopping_laundrette"', 'Laundry', 51, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (134, 'shop:shoes', '"label"=>"Shoes","frequency"=>49,"icon"=>""', 'Shoes', 49, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (135, 'shop:beverages', '"label"=>"Beverages","frequency"=>48,"icon"=>"shopping_alcohol"', 'Beverages', 48, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (136, 'shop:dry_cleaning', '"label"=>"Dry Cleaning","frequency"=>46,"icon"=>""', 'Dry Cleaning', 46, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (137, 'shop:carpet', '"label"=>"Carpet","frequency"=>45,"icon"=>""', 'Carpet', 45, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (138, 'shop:computer', '"label"=>"Computer","frequency"=>44,"icon"=>""', 'Computer', 44, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (139, 'shop:alcohol', '"label"=>"Alcohol","frequency"=>44,"icon"=>"shopping_alcohol"', 'Alcohol', 44, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (140, 'shop:optician', '"label"=>"Optician","frequency"=>55,"icon"=>"health_opticians"', 'Optician', 55, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (141, 'shop:chemist', '"label"=>"Chemist","frequency"=>42,"icon"=>"health_pharmacy"', 'Chemist', 42, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (142, 'shop:gallery', '"label"=>"Gallery","frequency"=>38,"icon"=>"tourist_art_gallery2"', 'Gallery', 38, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (143, 'shop:mobile_phone', '"label"=>"Mobile Phone","frequency"=>37,"icon"=>""', 'Mobile Phone', 37, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (144, 'shop:sports', '"label"=>"Sports","frequency"=>37,"icon"=>""', 'Sports', 37, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (145, 'shop:jewelry', '"label"=>"Jewelry","frequency"=>32,"icon"=>"shopping_jewelry"', 'Jewelry', 32, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (146, 'shop:pet', '"label"=>"Pet","frequency"=>29,"icon"=>""', 'Pet', 29, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (147, 'shop:beauty', '"label"=>"Beauty","frequency"=>28,"icon"=>""', 'Beauty', 28, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (148, 'shop:stationery', '"label"=>"Stationery","frequency"=>25,"icon"=>""', 'Stationery', 25, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (149, 'shop:shopping_centre', '"label"=>"Shopping Centre","frequency"=>25,"icon"=>""', 'Shopping Centre', 25, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (150, 'shop:general', '"label"=>"General","frequency"=>25,"icon"=>""', 'General', 25, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (151, 'shop:electrical', '"label"=>"Electrical","frequency"=>25,"icon"=>""', 'Electrical', 25, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (152, 'shop:toys', '"label"=>"Toys","frequency"=>23,"icon"=>""', 'Toys', 23, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (153, 'shop:jeweller', '"label"=>"Jeweller","frequency"=>23,"icon"=>""', 'Jeweller', 23, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (154, 'shop:betting', '"label"=>"Betting","frequency"=>23,"icon"=>""', 'Betting', 23, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (155, 'shop:household', '"label"=>"Household","frequency"=>21,"icon"=>""', 'Household', 21, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (156, 'shop:travel_agency', '"label"=>"Travel Agency","frequency"=>21,"icon"=>""', 'Travel Agency', 21, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (157, 'shop:hifi', '"label"=>"Hifi","frequency"=>21,"icon"=>""', 'Hifi', 21, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (158, 'amenity:shop', '"label"=>"Shop","frequency"=>61,"icon"=>""', 'Shop', 61, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (159, 'place:house', '"label"=>"House","frequency"=>2086,"icon"=>"","defzoom"=>18', 'House', 2086, NULL, 18, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (160, 'place:house_name', '"label"=>"House","frequency"=>2086,"icon"=>"","defzoom"=>18', 'House', 2086, NULL, 18, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (161, 'place:house_number', '"label"=>"House Number","frequency"=>2086,"icon"=>"","defzoom"=>18', 'House Number', 2086, NULL, 18, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (162, 'place:country_code', '"label"=>"Country Code","frequency"=>2086,"icon"=>"","defzoom"=>18', 'Country Code', 2086, NULL, 18, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (163, 'leisure:pitch', '"label"=>"Pitch","frequency"=>762,"icon"=>""', 'Pitch', 762, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (164, 'highway:unsurfaced', '"label"=>"Unsurfaced","frequency"=>492,"icon"=>""', 'Unsurfaced', 492, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (165, 'historic:ruins', '"label"=>"Ruins","frequency"=>483,"icon"=>"tourist_ruin"', 'Ruins', 483, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (166, 'amenity:college', '"label"=>"College","frequency"=>473,"icon"=>"education_school"', 'College', 473, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (167, 'historic:monument', '"label"=>"Monument","frequency"=>470,"icon"=>"tourist_monument"', 'Monument', 470, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (168, 'railway:subway', '"label"=>"Subway","frequency"=>385,"icon"=>""', 'Subway', 385, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (169, 'historic:memorial', '"label"=>"Memorial","frequency"=>382,"icon"=>"tourist_monument"', 'Memorial', 382, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (170, 'leisure:nature_reserve', '"label"=>"Nature Reserve","frequency"=>342,"icon"=>""', 'Nature Reserve', 342, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (171, 'leisure:common', '"label"=>"Common","frequency"=>322,"icon"=>""', 'Common', 322, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (172, 'waterway:lock_gate', '"label"=>"Lock Gate","frequency"=>321,"icon"=>""', 'Lock Gate', 321, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (173, 'natural:fell', '"label"=>"Fell","frequency"=>308,"icon"=>""', 'Fell', 308, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (174, 'amenity:nightclub', '"label"=>"Nightclub","frequency"=>292,"icon"=>""', 'Nightclub', 292, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (175, 'highway:path', '"label"=>"Path","frequency"=>287,"icon"=>""', 'Path', 287, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (176, 'leisure:garden', '"label"=>"Garden","frequency"=>285,"icon"=>""', 'Garden', 285, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (177, 'landuse:reservoir', '"label"=>"Reservoir","frequency"=>276,"icon"=>""', 'Reservoir', 276, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (178, 'leisure:playground', '"label"=>"Playground","frequency"=>264,"icon"=>""', 'Playground', 264, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (179, 'leisure:stadium', '"label"=>"Stadium","frequency"=>212,"icon"=>""', 'Stadium', 212, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (180, 'historic:mine', '"label"=>"Mine","frequency"=>193,"icon"=>"poi_mine"', 'Mine', 193, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (181, 'natural:cliff', '"label"=>"Cliff","frequency"=>193,"icon"=>""', 'Cliff', 193, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (182, 'tourism:caravan_site', '"label"=>"Caravan Site","frequency"=>183,"icon"=>"accommodation_caravan_park"', 'Caravan Site', 183, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (183, 'amenity:bus_station', '"label"=>"Bus Station","frequency"=>181,"icon"=>"transport_bus_station"', 'Bus Station', 181, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (184, 'amenity:kindergarten', '"label"=>"Kindergarten","frequency"=>179,"icon"=>""', 'Kindergarten', 179, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (185, 'highway:construction', '"label"=>"Construction","frequency"=>176,"icon"=>""', 'Construction', 176, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (186, 'amenity:atm', '"label"=>"Atm","frequency"=>172,"icon"=>"money_atm2"', 'Atm', 172, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (187, 'amenity:emergency_phone', '"label"=>"Emergency Phone","frequency"=>164,"icon"=>""', 'Emergency Phone', 164, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (188, 'waterway:lock', '"label"=>"Lock","frequency"=>146,"icon"=>""', 'Lock', 146, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (189, 'waterway:riverbank', '"label"=>"Riverbank","frequency"=>143,"icon"=>""', 'Riverbank', 143, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (190, 'natural:coastline', '"label"=>"Coastline","frequency"=>142,"icon"=>""', 'Coastline', 142, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (191, 'tourism:viewpoint', '"label"=>"Viewpoint","frequency"=>140,"icon"=>"tourist_view_point"', 'Viewpoint', 140, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (192, 'tourism:hostel', '"label"=>"Hostel","frequency"=>140,"icon"=>""', 'Hostel', 140, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (193, 'tourism:bed_and_breakfast', '"label"=>"Bed And Breakfast","frequency"=>140,"icon"=>"accommodation_bed_and_breakfast"', 'Bed And Breakfast', 140, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (194, 'railway:halt', '"label"=>"Halt","frequency"=>135,"icon"=>""', 'Halt', 135, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (195, 'railway:platform', '"label"=>"Platform","frequency"=>134,"icon"=>""', 'Platform', 134, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (196, 'railway:tram', '"label"=>"Tram","frequency"=>130,"icon"=>"transport_tram_stop"', 'Tram', 130, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (197, 'amenity:courthouse', '"label"=>"Courthouse","frequency"=>129,"icon"=>"amenity_court"', 'Courthouse', 129, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (198, 'amenity:recycling', '"label"=>"Recycling","frequency"=>126,"icon"=>"amenity_recycling"', 'Recycling', 126, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (199, 'amenity:dentist', '"label"=>"Dentist","frequency"=>124,"icon"=>"health_dentist"', 'Dentist', 124, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (200, 'natural:beach', '"label"=>"Beach","frequency"=>121,"icon"=>"tourist_beach"', 'Beach', 121, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (201, 'place:moor', '"label"=>"Moor","frequency"=>118,"icon"=>""', 'Moor', 118, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (202, 'amenity:grave_yard', '"label"=>"Grave Yard","frequency"=>110,"icon"=>""', 'Grave Yard', 110, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (203, 'waterway:derelict_canal', '"label"=>"Derelict Canal","frequency"=>109,"icon"=>""', 'Derelict Canal', 109, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (204, 'waterway:drain', '"label"=>"Drain","frequency"=>108,"icon"=>""', 'Drain', 108, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (205, 'landuse:grass', '"label"=>"Grass","frequency"=>106,"icon"=>""', 'Grass', 106, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (206, 'landuse:village_green', '"label"=>"Village Green","frequency"=>106,"icon"=>""', 'Village Green', 106, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (207, 'natural:bay', '"label"=>"Bay","frequency"=>102,"icon"=>""', 'Bay', 102, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (208, 'railway:tram_stop', '"label"=>"Tram Stop","frequency"=>101,"icon"=>"transport_tram_stop"', 'Tram Stop', 101, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (209, 'leisure:marina', '"label"=>"Marina","frequency"=>98,"icon"=>""', 'Marina', 98, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (210, 'highway:stile', '"label"=>"Stile","frequency"=>97,"icon"=>""', 'Stile', 97, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (211, 'natural:moor', '"label"=>"Moor","frequency"=>95,"icon"=>""', 'Moor', 95, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (212, 'railway:light_rail', '"label"=>"Light Rail","frequency"=>91,"icon"=>""', 'Light Rail', 91, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (213, 'railway:narrow_gauge', '"label"=>"Narrow Gauge","frequency"=>90,"icon"=>""', 'Narrow Gauge', 90, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (214, 'natural:land', '"label"=>"Land","frequency"=>86,"icon"=>""', 'Land', 86, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (215, 'amenity:village_hall', '"label"=>"Village Hall","frequency"=>82,"icon"=>""', 'Village Hall', 82, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (216, 'waterway:dock', '"label"=>"Dock","frequency"=>80,"icon"=>""', 'Dock', 80, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (217, 'amenity:veterinary', '"label"=>"Veterinary","frequency"=>79,"icon"=>""', 'Veterinary', 79, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (218, 'landuse:brownfield', '"label"=>"Brownfield","frequency"=>77,"icon"=>""', 'Brownfield', 77, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (219, 'leisure:track', '"label"=>"Track","frequency"=>76,"icon"=>""', 'Track', 76, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (220, 'railway:historic_station', '"label"=>"Historic Station","frequency"=>74,"icon"=>""', 'Historic Station', 74, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (221, 'landuse:construction', '"label"=>"Construction","frequency"=>72,"icon"=>""', 'Construction', 72, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (222, 'amenity:prison', '"label"=>"Prison","frequency"=>71,"icon"=>"amenity_prison"', 'Prison', 71, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (223, 'landuse:quarry', '"label"=>"Quarry","frequency"=>71,"icon"=>""', 'Quarry', 71, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (224, 'amenity:telephone', '"label"=>"Telephone","frequency"=>70,"icon"=>""', 'Telephone', 70, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (225, 'highway:traffic_signals', '"label"=>"Traffic Signals","frequency"=>66,"icon"=>""', 'Traffic Signals', 66, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (226, 'natural:heath', '"label"=>"Heath","frequency"=>62,"icon"=>""', 'Heath', 62, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (227, 'historic:house', '"label"=>"House","frequency"=>61,"icon"=>""', 'House', 61, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (228, 'amenity:social_club', '"label"=>"Social Club","frequency"=>61,"icon"=>""', 'Social Club', 61, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (229, 'landuse:military', '"label"=>"Military","frequency"=>61,"icon"=>""', 'Military', 61, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (230, 'amenity:health_centre', '"label"=>"Health Centre","frequency"=>59,"icon"=>""', 'Health Centre', 59, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (231, 'historic:building', '"label"=>"Building","frequency"=>58,"icon"=>""', 'Building', 58, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (232, 'amenity:clinic', '"label"=>"Clinic","frequency"=>57,"icon"=>""', 'Clinic', 57, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (233, 'highway:services', '"label"=>"Services","frequency"=>56,"icon"=>""', 'Services', 56, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (234, 'amenity:ferry_terminal', '"label"=>"Ferry Terminal","frequency"=>55,"icon"=>""', 'Ferry Terminal', 55, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (235, 'natural:marsh', '"label"=>"Marsh","frequency"=>55,"icon"=>""', 'Marsh', 55, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (236, 'natural:hill', '"label"=>"Hill","frequency"=>54,"icon"=>""', 'Hill', 54, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (237, 'highway:raceway', '"label"=>"Raceway","frequency"=>53,"icon"=>""', 'Raceway', 53, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (238, 'amenity:taxi', '"label"=>"Taxi","frequency"=>47,"icon"=>""', 'Taxi', 47, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (239, 'amenity:take_away', '"label"=>"Take Away","frequency"=>45,"icon"=>""', 'Take Away', 45, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (240, 'amenity:car_rental', '"label"=>"Car Rental","frequency"=>44,"icon"=>""', 'Car Rental', 44, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (241, 'place:islet', '"label"=>"Islet","frequency"=>44,"icon"=>""', 'Islet', 44, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (242, 'amenity:nursery', '"label"=>"Nursery","frequency"=>44,"icon"=>""', 'Nursery', 44, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (243, 'amenity:nursing_home', '"label"=>"Nursing Home","frequency"=>43,"icon"=>""', 'Nursing Home', 43, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (244, 'amenity:toilets', '"label"=>"Toilets","frequency"=>38,"icon"=>""', 'Toilets', 38, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (245, 'amenity:hall', '"label"=>"Hall","frequency"=>38,"icon"=>""', 'Hall', 38, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (246, 'waterway:boatyard', '"label"=>"Boatyard","frequency"=>36,"icon"=>""', 'Boatyard', 36, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (247, 'highway:mini_roundabout', '"label"=>"Mini Roundabout","frequency"=>35,"icon"=>""', 'Mini Roundabout', 35, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (248, 'historic:manor', '"label"=>"Manor","frequency"=>35,"icon"=>""', 'Manor', 35, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (249, 'tourism:chalet', '"label"=>"Chalet","frequency"=>34,"icon"=>""', 'Chalet', 34, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (250, 'amenity:bicycle_parking', '"label"=>"Bicycle Parking","frequency"=>34,"icon"=>""', 'Bicycle Parking', 34, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (251, 'amenity:hotel', '"label"=>"Hotel","frequency"=>34,"icon"=>""', 'Hotel', 34, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (252, 'waterway:weir', '"label"=>"Weir","frequency"=>33,"icon"=>""', 'Weir', 33, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (253, 'natural:wetland', '"label"=>"Wetland","frequency"=>33,"icon"=>""', 'Wetland', 33, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (254, 'natural:cave_entrance', '"label"=>"Cave Entrance","frequency"=>32,"icon"=>""', 'Cave Entrance', 32, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (255, 'amenity:crematorium', '"label"=>"Crematorium","frequency"=>31,"icon"=>""', 'Crematorium', 31, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (256, 'tourism:picnic_site', '"label"=>"Picnic Site","frequency"=>31,"icon"=>""', 'Picnic Site', 31, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (257, 'landuse:wood', '"label"=>"Wood","frequency"=>30,"icon"=>""', 'Wood', 30, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (258, 'landuse:basin', '"label"=>"Basin","frequency"=>30,"icon"=>""', 'Basin', 30, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (259, 'natural:tree', '"label"=>"Tree","frequency"=>30,"icon"=>""', 'Tree', 30, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (260, 'leisure:slipway', '"label"=>"Slipway","frequency"=>29,"icon"=>""', 'Slipway', 29, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (261, 'landuse:meadow', '"label"=>"Meadow","frequency"=>29,"icon"=>""', 'Meadow', 29, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (262, 'landuse:piste', '"label"=>"Piste","frequency"=>28,"icon"=>""', 'Piste', 28, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (263, 'amenity:care_home', '"label"=>"Care Home","frequency"=>28,"icon"=>""', 'Care Home', 28, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (264, 'amenity:club', '"label"=>"Club","frequency"=>28,"icon"=>""', 'Club', 28, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (265, 'amenity:medical_centre', '"label"=>"Medical Centre","frequency"=>27,"icon"=>""', 'Medical Centre', 27, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (266, 'historic:roman_road', '"label"=>"Roman Road","frequency"=>27,"icon"=>""', 'Roman Road', 27, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (267, 'historic:fort', '"label"=>"Fort","frequency"=>26,"icon"=>""', 'Fort', 26, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (268, 'railway:subway_entrance', '"label"=>"Subway Entrance","frequency"=>26,"icon"=>""', 'Subway Entrance', 26, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (269, 'historic:yes', '"label"=>"Yes","frequency"=>25,"icon"=>""', 'Yes', 25, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (270, 'highway:gate', '"label"=>"Gate","frequency"=>25,"icon"=>""', 'Gate', 25, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (271, 'leisure:fishing', '"label"=>"Fishing","frequency"=>24,"icon"=>""', 'Fishing', 24, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (272, 'historic:museum', '"label"=>"Museum","frequency"=>24,"icon"=>""', 'Museum', 24, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (273, 'amenity:car_wash', '"label"=>"Car Wash","frequency"=>24,"icon"=>""', 'Car Wash', 24, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (274, 'railway:level_crossing', '"label"=>"Level Crossing","frequency"=>23,"icon"=>""', 'Level Crossing', 23, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (275, 'leisure:bird_hide', '"label"=>"Bird Hide","frequency"=>23,"icon"=>""', 'Bird Hide', 23, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (276, 'natural:headland', '"label"=>"Headland","frequency"=>21,"icon"=>""', 'Headland', 21, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (277, 'tourism:apartments', '"label"=>"Apartments","frequency"=>21,"icon"=>""', 'Apartments', 21, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (278, 'amenity:shopping', '"label"=>"Shopping","frequency"=>21,"icon"=>""', 'Shopping', 21, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (279, 'natural:scrub', '"label"=>"Scrub","frequency"=>20,"icon"=>""', 'Scrub', 20, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (280, 'natural:fen', '"label"=>"Fen","frequency"=>20,"icon"=>""', 'Fen', 20, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (281, 'building:yes', '"label"=>"Building","frequency"=>200,"icon"=>""', 'Building', 200, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (282, 'mountain_pass:yes', '"label"=>"Mountain Pass","frequency"=>200,"icon"=>""', 'Mountain Pass', 200, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (283, 'amenity:parking', '"label"=>"Parking","frequency"=>3157,"icon"=>""', 'Parking', 3157, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (284, 'highway:bus_stop', '"label"=>"Bus Stop","frequency"=>35777,"icon"=>"transport_bus_stop2"', 'Bus Stop', 35777, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (285, 'place:postcode', '"label"=>"Postcode","frequency"=>27267,"icon"=>""', 'Postcode', 27267, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (286, 'amenity:post_box', '"label"=>"Post Box","frequency"=>9613,"icon"=>""', 'Post Box', 9613, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (287, 'place:houses', '"label"=>"Houses","frequency"=>85,"icon"=>""', 'Houses', 85, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (288, 'railway:preserved', '"label"=>"Preserved","frequency"=>227,"icon"=>""', 'Preserved', 227, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (289, 'waterway:derelict canal', '"label"=>"Derelict Canal","frequency"=>21,"icon"=>""', '', NULL, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (290, 'amenity:dead_pub', '"label"=>"Dead Pub","frequency"=>20,"icon"=>""', 'Dead Pub', 20, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (291, 'railway:disused_station', '"label"=>"Disused Station","frequency"=>114,"icon"=>""', 'Disused Station', 114, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (292, 'railway:abandoned', '"label"=>"Abandoned","frequency"=>641,"icon"=>""', 'Abandoned', 641, NULL, NULL, NULL, NULL);
INSERT INTO classtype (id, classtype, properties, label, frequency, icon, defzoom, defdiameter, simplelabel) VALUES (293, 'railway:disused', '"label"=>"Disused","frequency"=>72,"icon"=>""', 'Disused', 72, NULL, NULL, NULL, NULL);


--
-- Name: classtype_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY classtype
    ADD CONSTRAINT classtype_key PRIMARY KEY (classtype);


--
-- PostgreSQL database dump complete
--

-- Function: gps_get_placeids()

-- DROP FUNCTION gps_get_placeids();

CREATE OR REPLACE FUNCTION gps_get_placeids()
	RETURNS boolean AS
$BODY$
DECLARE
	searchDiam double precision;
	searchMaxAreaDistance double precision;
	searchMaxRank INT;
	searchPlaceId BIGINT;
	searchPlaceIdBackup BIGINT;
	searchParentPlaceId BIGINT;
	searchRank BIGINT;
	addressPlaceId BIGINT;
	searchPartition TEXT;
	searchOsmType TEXT;
	searchOsmId TEXT;
	searchClass TEXT; 
	searchType TEXT;
	searchAdminLevel TEXT;
	searchHouseNumber TEXT;
	searchStreet TEXT;
	searchIsIn TEXT;
	searchPostCode TEXT;
	searchCountryCode TEXT;
	searchExtraTags TEXT;
	searchLinkedPlaceId TEXT;
	searchRankAddress TEXT;
	searchImportance TEXT;
	searchIndexedStatus TEXT;
	searchIndexedDate TEXT;
	searchWikipedia TEXT;
	searchCalculatedCountryCode TEXT;
	searchLanguageAddress TEXT;
	searchPlaceName TEXT;
	searchPlaceNameRef TEXT;
	searchLatitude TEXT;
	searchLongitude TEXT;
	searchAddressType TEXT;
	searchLangAddress TEXT;
	foundResult BOOLEAN;
	aTypeLabel RECORD;
	aTypeLabelJson JSON;
	sTypeLabel TEXT;
	afallBack HSTORE;
	bFallBack BOOLEAN;
	aAddress HSTORE;
	aGps RECORD;
	aPlaceId RECORD;
	aLine RECORD;
	foundCount INTEGER;
	foundText TEXT;
	foundCount1 INTEGER;
	foundCount2 INTEGER;
	sAddressType TEXT;
	sClassType TEXT;
	aKeyValue RECORD;
	aLineJson JSON;
	langAddress TEXT;
	loopcounter INTEGER;
BEGIN

-- Prepare tables
CREATE TABLE IF NOT EXISTS my_sensor
(
	sensor character varying(100) NOT NULL DEFAULT ''::character varying,
	sensor_key character varying(100) NOT NULL DEFAULT ''::character varying,
	key_value text,
	unixtime integer NOT NULL,
	eml_event_timestamp timestamp(6) without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
	CONSTRAINT sensor_primary_key PRIMARY KEY (sensor, sensor_key, eml_event_timestamp)
)
WITH (
	OIDS=FALSE
);
ALTER TABLE my_sensor
	OWNER TO pilhokim;

CREATE OR REPLACE RULE "replace_my_sensor" AS
    ON INSERT TO "my_sensor"
    WHERE
		EXISTS(SELECT 1 FROM my_sensor WHERE sensor=NEW.sensor AND sensor_key=NEW.sensor_key AND eml_event_timestamp=NEW.eml_event_timestamp)
    DO INSTEAD
       (UPDATE my_sensor SET key_value=NEW.key_value WHERE sensor=NEW.sensor AND sensor_key=NEW.sensor_key AND eml_event_timestamp=NEW.eml_event_timestamp);

TRUNCATE TABLE my_sensor;

CREATE TABLE IF NOT EXISTS my_keyvaluestore
(
	my_key text NOT NULL,
	my_value text,
	my_category text NOT NULL,
	CONSTRAINT my_primary_key PRIMARY KEY (my_category, my_key)
)
WITH (
	OIDS=FALSE
);
ALTER TABLE my_keyvaluestore
	OWNER TO pilhokim;

CREATE OR REPLACE RULE "replace_my_keyvaluestore" AS
    ON INSERT TO "my_keyvaluestore"
    WHERE
		EXISTS(SELECT 1 FROM my_keyvaluestore WHERE my_category=NEW.my_category AND my_key=NEW.my_key)
    DO INSTEAD
		(UPDATE my_keyvaluestore SET my_value=NEW.my_value WHERE my_category=NEW.my_category AND my_key=NEW.my_key);

-- Table: my_gpsdata_placeids

-- DROP TABLE my_gpsdata_placeids;

CREATE TABLE IF NOT EXISTS my_gpsdata_placeids
(
	placeid integer NOT NULL,
	latitude double precision NOT NULL,
	longitude double precision NOT NULL,
	unixtimestamp integer NOT NULL,
	eml_event_timestamp timestamp(6) without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
	CONSTRAINT my_gpsdata_placeidskey PRIMARY KEY (placeid, latitude, longitude)
)
WITH (
	OIDS=FALSE
);
ALTER TABLE my_gpsdata_placeids
	OWNER TO postgres;

-- Rule: replacemy_gpsdata_placeids ON my_gpsdata_placeids

-- DROP RULE replacemy_gpsdata_placeids ON my_gpsdata_placeids;

CREATE OR REPLACE RULE replacemy_gpsdata_placeids AS
	ON INSERT TO my_gpsdata_placeids
	WHERE (EXISTS ( SELECT 1
		FROM my_gpsdata_placeids my_gpsdata_placeids_1
		WHERE my_gpsdata_placeids_1.placeid = new.placeid)) DO INSTEAD  UPDATE my_gpsdata_placeids SET latitude = new.latitude, longitude = new.longitude
	WHERE my_gpsdata_placeids.placeid = new.placeid;


-- loopcounter := 0;

-- Read data
FOR aGps IN 
	SELECT latitude, longitude, elevation, unixtimestamp, eml_event_timestamp
	FROM my_gpsdata
LOOP
	searchPlaceId := NULL;
	searchParentPlaceId := NULL;
	searchDiam := 0.0004;
	searchMaxAreaDistance := 1;
	searchMaxRank := 28;
	-- loopcounter := loopcounter + 1;
	searchRank := null;
	addressPlaceId := null;

	
	-- Start reverse geocoding
	-- Find nearest place ID
	LOOP
		-- CONTINUE WHEN searchPlaceId < 1 AND searchDiam < searchMaxAreaDistance;
		EXIT WHEN (searchDiam >= searchMaxAreaDistance OR searchPlaceId IS NOT NULL);
		
		searchDiam = searchDiam * 2;
		
		IF searchDiam > 2 AND searchMaxRank > 4 THEN searchMaxRank := 4;
		ELSIF searchDiam > 1 AND searchMaxRank > 9 THEN searchMaxRank := 8;
		ELSIF searchDiam > 0.8 AND searchMaxRank > 10 THEN searchMaxRank := 10;
		ELSIF searchDiam > 0.6 AND searchMaxRank > 12 THEN searchMaxRank := 12;
		ELSIF searchDiam > 0.2 AND searchMaxRank > 17 THEN searchMaxRank := 17;
		ELSIF searchDiam > 0.1 AND searchMaxRank > 18 THEN searchMaxRank := 18;
		ELSIF searchDiam > 0.008 AND searchMaxRank > 22 THEN searchMaxRank := 22;
		ELSIF searchDiam > 0.001 AND searchMaxRank > 26 THEN searchMaxRank := 26;
		END IF;

		-- -- RAISE NOTICE 'searchDiam (%) searchMaxRank (%)', searchDiam, searchMaxRank;
	  
		SELECT 
			place_id,
			parent_place_id,
			rank_search 
		FROM placex
		WHERE 
			ST_DWithin(ST_SetSRID(ST_Point(aGps.longitude,aGps.latitude),4326), geometry, searchDiam)
			AND rank_search != 28 AND rank_search >= searchMaxRank
			AND (name IS NOT NULL OR housenumber IS NOT NULL)
			AND class not IN ('waterway','railway','tunnel','bridge')
			AND indexed_status = 0 
			AND (ST_GeometryType(geometry) not IN ('ST_Polygon','ST_MultiPolygon') 
			OR ST_DWithin(ST_SetSRID(ST_Point(aGps.longitude,aGps.latitude),4326), centroid, searchDiam))
		ORDER BY ST_distance(ST_SetSRID(ST_Point(aGps.longitude,aGps.latitude),4326), geometry) ASC LIMIT 1
		INTO searchPlaceId, searchParentPlaceId, searchRank;
	END LOOP;

	-- -- RAISE NOTICE 'searchDiam (%) searchMaxRank (%) searchPlaceId (%) searchParentPlaceId (%)', searchDiam, searchMaxRank, searchPlaceId, searchParentPlaceId;

	-- The point we found might be too small - use the address to find what it is a child of
	IF (searchPlaceId IS NOT NULL AND searchMaxRank < 28) THEN
		searchPlaceIdBackup := searchPlaceId;
		IF (searchRank > 28 AND searchParentPlaceId IS NOT NULL) THEN
			searchPlaceId = searchParentPlaceId;
		END IF;

		SELECT address_place_id 
		FROM place_addressline 
		WHERE place_id = searchPlaceId 
		ORDER BY abs(cached_rank_address - searchMaxRank) ASC,cached_rank_address DESC,isaddress DESC,distance DESC LIMIT 1
		INTO searchPlaceId;

		IF (searchPlaceId < 1 OR searchPlaceId IS NULL) THEN
			searchPlaceId = searchPlaceIdBackup;
			-- RAISE NOTICE 'Found no child searchPlaceId (%)', searchPlaceId;
		ELSE
			-- RAISE NOTICE 'Found child searchPlaceId (%)', searchPlaceId;
		END IF;
	END IF;

	IF searchPlaceId IS NOT NULL AND searchPlaceId > 0 THEN
		-- RAISE NOTICE 'Found place id (%)', searchPlaceId;
		SELECT 
			address_place_id 
		FROM place_addressline 
		-- WHERE place_id = searchPlaceId 
		WHERE place_id = searchPlaceId 
		ORDER BY 
			abs(cached_rank_address - 28) ASC,
			cached_rank_address DESC,
			isaddress DESC,
			distance DESC 
		LIMIT 1
		INTO addressPlaceId;

		-- RAISE NOTICE 'Time (%) latitude (%) longitude (%) addressPlaceId (%)', aGps.eml_event_timestamp, aGps.latitude, aGps.longitude, addressPlaceId;
		
		-- Save place ID
		INSERT INTO my_gpsdata_placeids VALUES
		(addressPlaceId, aGps.latitude, aGps.longitude, aGps.unixtimestamp, aGps.eml_event_timestamp);
	END IF;

END LOOP;

RETURN TRUE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gps_reverse_geocode()
  OWNER TO postgres;


-- Function: gps_reverse_geocode_from_placeids()

-- DROP FUNCTION gps_reverse_geocode_from_placeids();

CREATE OR REPLACE FUNCTION gps_reverse_geocode_from_placeids()
	RETURNS boolean AS
$BODY$
DECLARE
	searchDiam double precision;
	searchMaxAreaDistance double precision;
	searchMaxRank INT;
	searchPlaceId BIGINT;
	searchPlaceIdBackup BIGINT;
	searchParentPlaceId BIGINT;
	searchRank BIGINT;
	addressPlaceId BIGINT;
	searchPartition TEXT;
	searchOsmType TEXT;
	searchOsmId TEXT;
	searchClass TEXT; 
	searchType TEXT;
	searchAdminLevel TEXT;
	searchHouseNumber TEXT;
	searchStreet TEXT;
	searchIsIn TEXT;
	searchPostCode TEXT;
	searchCountryCode TEXT;
	searchExtraTags TEXT;
	searchLinkedPlaceId TEXT;
	searchRankAddress TEXT;
	searchImportance TEXT;
	searchIndexedStatus TEXT;
	searchIndexedDate TEXT;
	searchWikipedia TEXT;
	searchCalculatedCountryCode TEXT;
	searchLanguageAddress TEXT;
	searchPlaceName TEXT;
	searchPlaceNameRef TEXT;
	searchLatitude TEXT;
	searchLongitude TEXT;
	searchAddressType TEXT;
	searchLangAddress TEXT;
	foundResult BOOLEAN;
	aTypeLabel RECORD;
	aTypeLabelJson JSON;
	sTypeLabel TEXT;
	afallBack HSTORE;
	bFallBack BOOLEAN;
	aAddress HSTORE;
	aGps RECORD;
	aPlaceId RECORD;
	aLine RECORD;
	foundCount INTEGER;
	foundText TEXT;
	foundCount1 INTEGER;
	foundCount2 INTEGER;
	sAddressType TEXT;
	sClassType TEXT;
	aKeyValue RECORD;
	aLineJson JSON;
	langAddress TEXT;
	loopcounter INTEGER;
BEGIN

-- Prepare tables
CREATE TABLE IF NOT EXISTS my_sensor
(
	sensor character varying(100) NOT NULL DEFAULT ''::character varying,
	sensor_key character varying(100) NOT NULL DEFAULT ''::character varying,
	key_value text,
	unixtime integer NOT NULL,
	eml_event_timestamp timestamp(6) without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
	CONSTRAINT sensor_primary_key PRIMARY KEY (sensor, sensor_key, eml_event_timestamp)
)
WITH (
	OIDS=FALSE
);
ALTER TABLE my_sensor
	OWNER TO pilhokim;

CREATE OR REPLACE RULE "replace_my_sensor" AS
    ON INSERT TO "my_sensor"
    WHERE
		EXISTS(SELECT 1 FROM my_sensor WHERE sensor=NEW.sensor AND sensor_key=NEW.sensor_key AND eml_event_timestamp=NEW.eml_event_timestamp)
    DO INSTEAD
       (UPDATE my_sensor SET key_value=NEW.key_value WHERE sensor=NEW.sensor AND sensor_key=NEW.sensor_key AND eml_event_timestamp=NEW.eml_event_timestamp);

TRUNCATE TABLE my_sensor;

CREATE TABLE IF NOT EXISTS my_keyvaluestore
(
	my_key text NOT NULL,
	my_value text,
	my_category text NOT NULL,
	CONSTRAINT my_primary_key PRIMARY KEY (my_category, my_key)
)
WITH (
	OIDS=FALSE
);
ALTER TABLE my_keyvaluestore
	OWNER TO pilhokim;

CREATE OR REPLACE RULE "replace_my_keyvaluestore" AS
    ON INSERT TO "my_keyvaluestore"
    WHERE
		EXISTS(SELECT 1 FROM my_keyvaluestore WHERE my_category=NEW.my_category AND my_key=NEW.my_key)
    DO INSTEAD
		(UPDATE my_keyvaluestore SET my_value=NEW.my_value WHERE my_category=NEW.my_category AND my_key=NEW.my_key);

-- Table: my_gpsdata_placeids

-- DROP TABLE my_gpsdata_placeids;

CREATE TABLE IF NOT EXISTS my_gpsdata_placeids
(
	placeid integer NOT NULL,
	latitude double precision NOT NULL,
	longitude double precision NOT NULL,
	unixtimestamp integer NOT NULL,
	eml_event_timestamp timestamp(6) without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
	CONSTRAINT my_gpsdata_placeidskey PRIMARY KEY (placeid, latitude, longitude)
)
WITH (
	OIDS=FALSE
);
ALTER TABLE my_gpsdata_placeids
	OWNER TO postgres;

-- Rule: replacemy_gpsdata_placeids ON my_gpsdata_placeids

-- DROP RULE replacemy_gpsdata_placeids ON my_gpsdata_placeids;

CREATE OR REPLACE RULE replacemy_gpsdata_placeids AS
	ON INSERT TO my_gpsdata_placeids
	WHERE (EXISTS ( SELECT 1
		FROM my_gpsdata_placeids my_gpsdata_placeids_1
		WHERE my_gpsdata_placeids_1.placeid = new.placeid)) DO INSTEAD  UPDATE my_gpsdata_placeids SET latitude = new.latitude, longitude = new.longitude
	WHERE my_gpsdata_placeids.placeid = new.placeid;


-- Read place IDs
FOR aPlaceId IN 
	SELECT placeid, latitude, longitude, unixtimestamp, eml_event_timestamp
	FROM my_gpsdata_placeids
LOOP
	-- Initialize variables
	searchPlaceId := null; 
	searchPartition := null; 
	searchOsmType := null; 
	searchOsmId := null; 
	searchClass := null; 
	searchType := null; 
	searchAdminLevel := null;
	searchHouseNumber := null; 
	searchStreet := null; 
	searchIsIn := null; 
	searchPostCode := null; 
	searchCountryCode := null; 
	searchExtraTags := null;
	searchParentPlaceId := null; 
	searchLinkedPlaceId := null; 
	searchRankAddress := null; 
	searchRank := null;
	searchImportance := null; 
	searchIndexedStatus := null; 
	searchIndexedDate := null; 
	searchWikipedia := null; 
	searchCalculatedCountryCode := null; 
	searchLangAddress := null; 
	searchPlaceName := null; 
	searchPlaceNameRef := null; 
	searchLatitude := null; 
	searchLongitude := null;
	foundCount := null;
	foundText := null;
	aTypeLabel := null;
	sTypeLabel := null;
	foundCount := null;
	foundCount1 := null;
	foundCount2 := null;
	
	-- Retrieve address details
	SELECT 
		placex.place_id, partition, osm_type, osm_id, class, type, admin_level, 
		housenumber, street, isin, postcode, country_code, extratags, 
		parent_place_id, linked_place_id, rank_address, rank_search,
		coalesce(importance,0.75-(rank_search::float/40)) AS importance, 
		indexed_status, indexed_date, wikipedia, calculated_country_code,
		get_address_by_language(aPlaceId.placeid, ARRAY['short_name:en-us','short_name:en','name:en-us','name:en','place_name:en-us','place_name:en','official_name:en-us','official_name:en','short_name','name','place_name','official_name','ref','type']),
		get_name_by_language(name, ARRAY['short_name:en-us','short_name:en','name:en-us','name:en','place_name:en-us','place_name:en','official_name:en-us','official_name:en','short_name','name','place_name','official_name','ref','type']),
		get_name_by_language(name, ARRAY['ref']) AS ref,
		(case when centroid is NULL then st_y(st_centroid(geometry)) else st_y(centroid) end) as lat,
		(case when centroid is NULL then st_x(st_centroid(geometry)) else st_x(centroid) end) as lon
	FROM placex 
	WHERE place_id = aPlaceId.placeid
	INTO 
		searchPlaceId, searchPartition, searchOsmType, searchOsmId, searchClass, searchType, searchAdminLevel,
		searchHouseNumber, searchStreet, searchIsIn, searchPostCode, searchCountryCode, searchExtraTags,
		searchParentPlaceId, searchLinkedPlaceId, searchRankAddress, searchRank,
		searchImportance, searchIndexedStatus, searchIndexedDate, searchWikipedia, searchCalculatedCountryCode, 
		searchLangAddress, searchPlaceName, searchPlaceNameRef, searchLatitude, searchLongitude;
	  
	-- SELECT get_address_by_language(aPlaceId.placeid, ARRAY['short_name:en-us','short_name:en','name:en-us','name:en','place_name:en-us','place_name:en','official_name:en-us','official_name:en','short_name','name','place_name','official_name','ref','type']) INTO langAddress; 
	-- Table: my_keyvaluestore
	-- DROP TABLE my_keyvaluestore;

	TRUNCATE TABLE my_keyvaluestore;

	FOR aLine IN 
		SELECT 
			*,
			get_name_by_language(name,ARRAY['short_name:en-us','short_name:en','name:en-us','name:en','place_name:en-us','place_name:en','official_name:en-us','official_name:en','short_name','name','place_name','official_name','ref','type']) as localname 
		FROM get_addressdata(aPlaceId.placeid)
		WHERE isaddress OR type = 'country_code'
		ORDER BY rank_address DESC,isaddress DESC
	LOOP
		aLineJson := row_to_json(aLine);
		aTypeLabel := NULL;
		-- RAISE NOTICE 'aLineJson (%)', aLineJson;

		SELECT count(classtype) 
		FROM classtype 
		WHERE classtype = aLine.class||':'||aLine.type||':'||aLine.admin_level LIMIT 1 
		INTO foundCount;

		IF foundCount > 0 THEN
			sTypeLabel := aLine.class||':'||aLine.type||':'||aLine.admin_level;
			
			SELECT 
			* 
			FROM classtype 
			WHERE classtype = sTypeLabel LIMIT 1 
			INTO aTypeLabel;
			
			-- RAISE NOTICE 'sTypeLabel (%)', sTypeLabel;
		ELSE
		    foundText := NULL;

		    SELECT classtype 
		    FROM classtype 
		    WHERE classtype = aLine.class||':'||aLine.type LIMIT 1 
		    INTO foundText;
		    
		    -- RAISE NOTICE 'classtype (%) foundText (%)', aLine.class||':'||aLine.type, foundText;

		    IF foundText IS NOT NULL THEN
				sTypeLabel := aLine.class||':'||aLine.type;

				SELECT 
				* 
				FROM classtype 
				WHERE classtype = sTypeLabel LIMIT 1 
				INTO aTypeLabel;

				-- RAISE NOTICE 'aTypeLabel (%)', aTypeLabel;
		    ELSE
				sTypeLabel := 'boundary:administrative:'||trim(both ' ' FROM to_char(aLine.rank_address/2, '99999'));
				-- RAISE NOTICE 'aLine.rank_address (%) sTypeLabel (%)',aLine.rank_address, sTypeLabel;

				SELECT 
				classtype 
				FROM classtype 
				WHERE classtype = sTypeLabel LIMIT 1 
				INTO foundText;

				-- RAISE NOTICE 'sTypeLabel (%) foundText (%)', sTypeLabel, foundText;

				IF foundText IS NOT NULL THEN
					SELECT * FROM classtype WHERE classtype = sTypeLabel LIMIT 1 INTO aTypeLabel;
					bFallBack := true;
					-- RAISE NOTICE 'sTypeLabel (%) aTypeLabel (%)', sTypeLabel, aTypeLabel;
				ELSE
					-- RAISE NOTICE 'Manual row entered';
					
					SELECT * FROM classtype LIMIT 1 INTO aTypeLabel;
					aTypeLabel.id = null;
					aTypeLabel.classtype = null;
					aTypeLabel.properties = null;
					aTypeLabel.label = null;
					aTypeLabel.frequency = null;
					aTypeLabel.icon = null;
					aTypeLabel.defzoom = null;
					aTypeLabel.defdiameter = null;
					aTypeLabel.simplelabel = 'address'||trim(both ' ' from to_char(aLine.rank_address, '99999'));
					
					-- RAISE NOTICE 'aTypeLabel (%) ', aTypeLabel;
				END IF;
		    END IF;
		END IF;

		aTypeLabelJson := row_to_json(aTypeLabel);

		-- RAISE NOTICE ' NOT (aTypeLabel IS NULL) (%) aLineJson->>"localname" (%) aLineJson->>"housenumber" (%) ',  NOT (aTypeLabel IS NULL), aLineJson->>'localname', aLineJson->>'housenumber';

		IF (
			( NOT (aTypeLabel IS NULL) AND ((aLineJson->'localname') IS NOT NULL AND (aLineJson->>'localname') IS NOT NULL)) OR 
			((aLineJson->'housenumber') IS NOT NULL AND (aLineJson->>'housenumber') IS NOT NULL)
		) THEN
			IF ((aTypeLabelJson->'simplelabel') IS NOT NULL AND (aTypeLabelJson->>'simplelabel') IS NOT NULL) THEN
				sTypeLabel := lower(aTypeLabel.simplelabel);
			ELSE
				sTypeLabel := lower(aTypeLabel.label);
			END IF;

			sTypeLabel := replace(sTypeLabel, ' ','_');

			-- RAISE NOTICE 'entered sTypeLabel (%) ', sTypeLabel;

			SELECT COUNT(*) FROM my_keyvaluestore WHERE my_category = 'aAddress' AND my_key = sTypeLabel INTO foundCount1;
			SELECT COUNT(*) FROM my_keyvaluestore WHERE my_category = 'aFallback' AND my_key = sTypeLabel INTO foundCount2;

			IF ((foundCount1 < 1) OR (foundCount2 > 0) OR aLine.class = 'place') THEN
			--  aAddress := sTypeLabel||'=>'||f_if(aLine.localname, aLine.localname, aLine.housenumber)::hstore;
				IF (aLine.localname IS NOT NULL) THEN
					INSERT INTO "my_keyvaluestore" (my_category, my_key, my_value) VALUES ('aAddress', sTypeLabel, aLine.localname);
				ELSE
					INSERT INTO "my_keyvaluestore" (my_category, my_key, my_value) VALUES ('aAddress', sTypeLabel, aLine.housenumber);
				END IF;
			END IF;
			-- aFallback := sTypeLabel||'=>'||bFallBack::hstore;
			INSERT INTO "my_keyvaluestore" (my_category, my_key, my_value) VALUES ('aFallBack', sTypeLabel, bFallBack);
		END IF;
	END LOOP;

	sAddressType := '';
	sClassType := searchClass||':'||searchType||':'||searchAdminLevel;

	SELECT COUNT(classType) FROM classType WHERE classType = sClassType INTO foundCount;
	SELECT simpleLabel FROM classType WHERE classType = sClassType INTO foundText;

	IF (foundCount > 0 AND foundText IS NOT NULL) THEN
		sAddressType := foundText;
	ELSE
		sClassType = searchClass||':'||searchType;

		SELECT COUNT(classType) FROM classType WHERE classType = sClassType INTO foundCount;
		SELECT simpleLabel FROM classType WHERE classType = sClassType INTO foundText;

		IF (foundCount > 0 AND foundText IS NOT NULL) THEN
			sAddressType := foundText;
		ELSE
			sAddressType := searchClass;
		END IF;
	END IF;

	searchAddressType := sAddressType;
	  
	SELECT get_address_by_language(aPlaceId.placeid, ARRAY['short_name:en-us','short_name:en','name:en-us','name:en','place_name:en-us','place_name:en','official_name:en-us','official_name:en','short_name','name','place_name','official_name','ref','type']) INTO langAddress; 

	INSERT INTO my_sensor VALUES
	('Nominatim/GPS/ReverseGeocoding', 'latitude', aPlaceId.latitude, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'longitude', aPlaceId.longitude, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchLatitude', searchLatitude, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchLongitude', searchLongitude, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'placeId', searchPlaceId, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchPartition', searchPartition, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchOsmType', searchOsmType, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchOsmId', searchOsmId, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchClass', searchClass, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchType', searchType, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchAdminLevel', searchAdminLevel, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchHouseNumber', searchHouseNumber, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchStreet', searchStreet, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchIsIn', searchIsIn, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchPostCode', searchPostCode, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchCountryCode', searchCountryCode, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchExtraTags', searchExtraTags, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchParentPlaceId', searchParentPlaceId, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchLinkedPlaceId', searchLinkedPlaceId, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchRankAddress', searchRankAddress, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'address', searchStreet||', '||searchIsIn||', '||searchPostCode||', '||searchCountryCode, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp),
	('Nominatim/GPS/ReverseGeocoding', 'searchLangAddress', searchLangAddress, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp);

	-- RAISE NOTICE 'time (%) latitude (%) longitude (%) searchLangAddress: (%) ', aPlaceId.eml_event_timestamp, aPlaceId.latitude, aPlaceId.longitude, searchLangAddress;
	-- ('Nominatim/GPS/ReverseGeocoding', 'langAddress', langAddress, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp);

	foundCount := 0;
	FOR aKeyValue IN 
		SELECT 
		*
		FROM my_keyvaluestore
		WHERE my_category = 'aAddress'
	LOOP
		foundCount := foundCount + 1;
		INSERT INTO my_sensor VALUES
		('Nominatim/GPS/ReverseGeocoding', aKeyValue.my_key, aKeyValue.my_value, aPlaceId.unixtimestamp, aPlaceId.eml_event_timestamp);

		-- RAISE NOTICE 'Insert Query: (%) ', 'INSERT INTO my_sensor VALUES ("Nominatim/GPS/ReverseGeocoding", '||aKeyValue.my_key||', '||aKeyValue.my_value||', '||aGps.unixtimestamp||', '||aGps.eml_event_timestamp||');';
	END LOOP;

END LOOP;
RETURN TRUE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gps_reverse_geocode()
  OWNER TO postgres;
 

Output Data Table Format

Reversed geo-codes will be stored in my_sensor table

-- Table: my_sensor

-- DROP TABLE my_sensor;

CREATE TABLE my_sensor
(
  sensor character varying(100) NOT NULL DEFAULT ''::character varying,
  sensor_key character varying(100) NOT NULL DEFAULT ''::character varying,
  key_value text,
  unixtimestamp integer NOT NULL,
  eml_event_timestamp timestamp(6) without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
  CONSTRAINT sensor_primary_key PRIMARY KEY (sensor, sensor_key, eml_event_timestamp)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE my_sensor
  OWNER TO pilhokim;

-- Rule: replace_my_sensor ON my_sensor

-- DROP RULE replace_my_sensor ON my_sensor;

CREATE OR REPLACE RULE replace_my_sensor AS
    ON INSERT TO my_sensor
   WHERE (EXISTS ( SELECT 1
           FROM my_sensor my_sensor_1
          WHERE my_sensor_1.sensor::text = new.sensor::text AND my_sensor_1.sensor_key::text = new.sensor_key::text AND my_sensor_1.eml_event_timestamp = new.eml_event_timestamp)) DO INSTEAD  UPDATE my_sensor SET key_value = new.key_value
  WHERE my_sensor.sensor::text = new.sensor::text AND my_sensor.sensor_key::text = new.sensor_key::text AND my_sensor.eml_event_timestamp = new.eml_event_timestamp;

 

Next Plan 

Reverse geocoding using Nominatim database needs to import OSM data into the PostgreSQL database which makes the data size pretty big (ex. Entire Italy OSM database costs 40 GB storage. Surely you would need double-size free hard drive to process the data).

Previous Approaches 

We already have used same OSM data but using different methods to perform reverse Geocoding.  It followed the method introduced at http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/ It works succesffully with a small data set (Trento city data) but when applied to a bigger (and very recent) data set, their script file (bulkDB.pl) keep failing with various error messages. So we looked back Nominatim as shown above. For records, check https://gist.github.com/pilhokim/676bebcb2f9475938537

See Also

  1. MariaDB OpenStreetMap Dataset: https://mariadb.com/kb/en/mariadb/development/quality/qa-datasets/openstreetmap-dataset/
  2. Osmosis-MySQL: https://github.com/oschrenk/osmosis-mysql

 

Pics 'n' Trails dataset preparation

This small lab is to post-process Pics 'n' Trails to prepare the data for members. The dataset is composed of binary images and GPS records.

GPS Records Data Schema

GPS records have two types of data: one of CSV text files for 2008 and the other in GDB (Garmin GPS binary format) for 2009 for which GPSBabel is used to decode. Both structures are different as below.

Below is the data structure of CSV text files.

CREATE TABLE `gps_traces` (
  `gps_year` int(11) DEFAULT NULL,
  `gps_month` int(11) DEFAULT NULL,
  `gps_date` int(11) DEFAULT NULL,
  `gps_datestamp` int(11) DEFAULT NULL,
  `gps_seconds` int(11) DEFAULT NULL,
  `gps_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `gps_altitude` double DEFAULT NULL,
  `gps_leglength` double DEFAULT NULL,
  `gps_legtime` int(11) DEFAULT NULL,
  `gps_speed` double DEFAULT NULL,
  `gps_course` double DEFAULT NULL,
  `gps_latitudedirection` char(2) DEFAULT NULL,
  `gps_latitudedegrees` double DEFAULT NULL,
  `gps_latitudefraction` double DEFAULT NULL,
  `gps_longitudedirection` char(2) DEFAULT NULL,
  `gps_longitudedegrees` double DEFAULT NULL,
  `gps_longitudefraction` double DEFAULT NULL,
  `gps_latitudenumeric` double DEFAULT NULL,
  `gps_longitudenumeric` double DEFAULT NULL,
  PRIMARY KEY (`gps_timestamp`),
  KEY `gps_latitudenumeric` (`gps_latitudenumeric`),
  KEY `gps_longitudenumeric` (`gps_longitudenumeric`),
  KEY `gps_altitude` (`gps_altitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Use the below command to import local data files

LOAD DATA LOCAL INFILE 'gps20080929tsv.txt' IGNORE INTO TABLE gps_traces;

Below is the data structure of GDB data files.

CREATE TABLE `gps_garmin_gps` (
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `altitude` double DEFAULT NULL,
  `date` char(255) DEFAULT NULL,
  `time` char(255) DEFAULT NULL,
  `gps_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`gps_timestamp`),
  KEY `longitude` (`longitude`),
  KEY `latitude` (`latitude`),
  KEY `altitude` (`altitude`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Use the below command to import local data files

LOAD DATA LOCAL INFILE '20090108.txt' IGNORE INTO TABLE gps_garmin_gps FIELDS TERMINATED BY ',';

As a statistics, he has collected 514,670 GPS records in 2008 and 29,337 records in 2009.

Image data set Import

 0 ? gps2Num($exifCoord[0]) : 0;
	$minutes = count($exifCoord) > 1 ? gps2Num($exifCoord[1]) : 0;
	$seconds = count($exifCoord) > 2 ? gps2Num($exifCoord[2]) : 0;

	$flip = ($hemi == 'W' or $hemi == 'S') ? -1 : 1;
	return floatval($flip * ($degrees +($minutes/60)+($seconds/3600)));
}

function gps2Num($coordPart) {
	$parts = explode('/', $coordPart);
	if (count($parts) <= 0)
		return 0;

	if (count($parts) == 1)
		return $parts[0];

	return floatval($parts[0]) / floatval($parts[1]);
}

// Set time zone
date_default_timezone_set("UTC");

$path = "";
$set_timeadjust = 0;

$path= "/Users/pilhokim/Documents/Research/eLifeLog/data/pics_n_trails/photos/";
$thumbnail_16_path = '/Users/pilhokim/Documents/Research/eLifeLog/data/pics_n_trails/thumbnail_16/';
$thumbnail_64_path = '/Users/pilhokim/Documents/Research/eLifeLog/data/pics_n_trails/thumbnail_64/';

$bCreateThumbnail = 1;

// Set up the MySQL connection
$mysqli = new mysqli("127.0.0.1", "user_id", "password", "pics_n_trails", 3306);

/* check connection */
if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
  	exit();
}
// Prepare the temporary table 
$query = "TRUNCATE TABLE pics";
if (!($result = $mysqli->query($query))) {
	printf("Table truncation error: %s\n", $mysqli->error);
	exit();
}
$query = "TRUNCATE TABLE pics_exif";
if (!($result = $mysqli->query($query))) {
	printf("Table truncation error: %s\n", $mysqli->error);
	exit();
}
$query = "TRUNCATE TABLE pics_thumbnail_16";
if (!($result = $mysqli->query($query))) {
	printf("Table truncation error: %s\n", $mysqli->error);
	exit();
}
$query = "TRUNCATE TABLE pics_thumbnail_64";
if (!($result = $mysqli->query($query))) {
	printf("Table truncation error: %s\n", $mysqli->error);
	exit();
}

// Recursively read all files in the selected directory 
$ite=new RecursiveDirectoryIterator($path);
$bytestotal=0;
$nbfiles=0;
$imagepath = $path;
$imagesubpath = '';

$ite_image = new RecursiveDirectoryIterator($imagepath);
foreach (new RecursiveIteratorIterator($ite_image) as $imagefilename=>$imagecur) {
	if ($bCreateThumbnail) {
		$newimagepath = $imagecur->getPathname();
		$newimagefile = $imagecur->getFileName();
		$newimagefullpath = substr($newimagepath, 0, strlen($newimagepath) - strlen($newimagefile));
		$newimagesubpath = substr($newimagefullpath, strlen($imagepath) - strlen($newimagefullpath));

		if ($imagesubpath != $newimagesubpath && is_dir($imagepath.$newimagesubpath)) {  
			$imagesubpath = $newimagesubpath;
			echo "Entering the subpath: $imagesubpath\n";
			if (strlen($imagesubpath) > 1) {
				// Create sub directory
				mkdir($thumbnail_16_path.$imagesubpath);
				mkdir($thumbnail_64_path.$imagesubpath);
			}
		}
	}

	if (strstr($imagecur, ".jpg") != "") {
		$filesize=$imagecur->getSize();
		$modified_time = $imagecur->getMTime();
		$bytestotal+=$filesize;
		$nbfiles++;
		echo "$imagefilename => $filesize\n";

		$query = "INSERT IGNORE INTO pics(filepath, image_UTC_timestamp, image_UTC_unix_timestamp)";
		$query = $query."	SELECT ";
		$query = $query."'$imagecur', ";
		$query = $query." CONVERT_TZ(FROM_UNIXTIME(".$modified_time."), '+9:00', '+0:00'), ";
		$query = $query." UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(".$modified_time."), '+9:00', '+0:00'))";
		
		if (!($result = $mysqli->query($query))) {
			echo $query."\n";
			printf("Image insertion error: %s\n", $mysqli->error);
			return 0;
		}

		// Create thumbnail
		if ($bCreateThumbnail) {
			list($width, $height) = getimagesize($imagecur->getPathname());

			if ($width >= $height) {
				$thumbnail_16 = imagecreatetruecolor(16, intval($height/$width*16));
				$thumbnail_64 = imagecreatetruecolor(64, intval($height/$width*64));
			}
			else {
				$thumbnail_16 = imagecreatetruecolor(intval($width/$height*16), 16);
				$thumbnail_64 = imagecreatetruecolor(intval($width/$height*64), 64);
			}

			$source = imagecreatefromjpeg($imagecur);

			imagecopyresized($thumbnail_16, $source, 0, 0, 0, 0, imagesx($thumbnail_16), imagesy($thumbnail_16), $width, $height);
			imagejpeg($thumbnail_16, $thumbnail_16_path.$imagesubpath.$imagecur->getFileName(), 100);

			imagecopyresized($thumbnail_64, $source, 0, 0, 0, 0, imagesx($thumbnail_64), imagesy($thumbnail_64), $width, $height);
			imagejpeg($thumbnail_64, $thumbnail_64_path.$imagesubpath.$imagecur->getFileName(), 100);

			/*
			 * After run query
			UPDATE pics
					  SET filepath = REPLACE(filepath, 
					  '/Users/pilhokim/Documents/Research/eLifeLog/data/pics_n_trails',
					  '/var/chroot/home/content/78/7627678/data/users/pics_n_trails'
			);

			UPDATE pics
					  SET 
					  filepath_thumbnail_16 = REPLACE(filepath, 'photos', 'thumbnail_16'),
								 filepath_thumbnail_64 = REPLACE(filepath, 'photos', 'thumbnail_64');
			 */

		}

		// Read EXIF information
		$hasGPS = 0;
		$exif = exif_read_data($imagecur, 0, true);
		if ($exif) {
			$query = "INSERT IGNORE INTO pics_exif(filepath, image_UTC_unix_timestamp, section, section_key, key_value) ";
			$query = $query."	VALUES ";

			$insert_count = 0;
			foreach ($exif as $key => $section) {
				$exif_section = "$key";
				foreach ($section as $name => $val) {
					$exif_key = "$name";

					if ($name == "GPSLatitude") {
						$hasGPS = 1;
					}

					if (is_array($val)) {
						$exif_value = ""; 
						$count = 0;
						foreach ($val as $single_val) {
							if ($count == 0) {
								$exif_value = $exif_value."$single_val"; 
							}
							else {
								$exif_value = $exif_value."\t$single_val"; 
							}
							$count = $count + 1;
						}   
					}   
					else { 
						$exif_value = "$val"; 
					}   

					$exif_value = addslashes($exif_value);

					if ($insert_count == 0) {
						$query = $query." ('$imagefilename', UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(".$modified_time."), '+9:00', '+0:00')), '$exif_section', '$exif_key', '$exif_value') ";
					}
					else {
						$query = $query.", ('$imagefilename', UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(".$modified_time."), '+9:00', '+0:00')), '$exif_section', '$exif_key', '$exif_value') ";
					}
					$insert_count = $insert_count + 1;
				}
			}

			// Check GPS info
			if ($hasGPS) {
				$longitude = $exif["GPS"]["GPSLongitude"];
				$longituderef = $exif["GPS"]["GPSLongitudeRef"];
				$latitude = $exif["GPS"]["GPSLatitude"];
				$latituderef = $exif["GPS"]["GPSLatitudeRef"];
			
				$lon = getGps($longitude, $longituderef);
				$lat = getGps($latitude, $latituderef);

				if ($lon) {
						  $query = $query.", ('$imagefilename', UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(".$modified_time."), '+9:00', '+0:00')), 'GPS', 'Longitude', '$lon') "; }
				if ($lat) {
						  $query = $query.", ('$imagefilename', UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(".$modified_time."), '+9:00', '+0:00')), 'GPS', 'Latitude', '$lat') "; 
				}
			}
		
			if (!($result = $mysqli->query($query))) {
				printf("Image exif insertion error: %s\n", $mysqli->error);
			}
		}
	}
}	

unset($imagefilename, $query);

/* Update the photo timestamp by the location */

$mysqli->close();

$bytestotal=number_format($bytestotal);
echo "Total: $nbfiles files, $bytestotal bytes\n";

Porting Apace Mahout for eLifeLog SAS

Apache Mahout is a machine learning library to build scalable machine learning libraries.

Test Installation

Environments

  • Hardware: Macbook Pro
  • OS: OS X Lion

Software Installation

Not that much hassles in installation

References

 

Reference for Esper + Streaming Sources for CEP

This lab works as the reference to set up user's own CEP environment for lifelog data mining. Specifically we will focus on the use of Esper as the CEP engine and JRuby for stream source generation.

About Esper

Esper is a event stream engine for intelligent complex event processing. Follow the short instruction at here and the full tutorial. For deeper understanding on their event processing language (EPL), look into this reference.

References

First Experiment

We first tried jRuby as the stremaing sources for Esper testing as desribed at here and its sample code. This handles the case using twitter streams as the source for Esper testing.

Note on Installing Necessary Gems for Tutorial 

sudo jruby -S gem update

sudo JRUBY_OPTS="-Xcext.enabled=true" jruby -S gem install tweetstream

Problems

One (Tweetstream) of the ruby gems necessary to run this example was not working well with jRuby 2.0 with a known bug.

Using Twitter Java Client for Streaming 

Our next try was directly using Twitter's Java client interface, hbc. This works well and also the integration with esper was easy too. 

Connecting Esper to Storm

openBmap cell location database

Add GSM and Wifi reverse geocode databases

Within a number of available cell location database, we chose openBmap and process their data to import into the database. This section briefly shows how we processed them.

As of now (Jan 10, 2012), openBmap data sets are composed of cellular map data and wifi map data  in which data structures are slightly different. So for this experiment we manage both using two different databases. 

Import GSM data

Data tables are prepared based on their log formats, which is in fact imcomplete, and thus inspecting actual files.

 

-- MySQL dump 10.13  Distrib 5.5.11, for Win64 (x86)
--
-- Host: localhost    Database: openbmap
-- ------------------------------------------------------
-- Server version	5.5.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `cell`
--

DROP TABLE IF EXISTS `cell`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cell` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cellid` int(11) DEFAULT NULL,
  `mcc` int(11) DEFAULT NULL,
  `mnc` int(11) DEFAULT NULL,
  `lac` int(11) DEFAULT NULL,
  `ss` int(11) DEFAULT NULL,
  `act` varchar(50) DEFAULT NULL,
  `rxlev` int(11) DEFAULT NULL,
  `psc` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cellid` (`cellid`)
) ENGINE=InnoDB AUTO_INCREMENT=1512570 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `cell_neighbour`
--

DROP TABLE IF EXISTS `cell_neighbour`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cell_neighbour` (
  `servingid` int(11) NOT NULL,
  `neighborhoodid` int(11) NOT NULL,
  PRIMARY KEY (`servingid`,`neighborhoodid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `gps`
--

DROP TABLE IF EXISTS `gps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gps` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(15) DEFAULT NULL,
  `lng` double DEFAULT NULL,
  `lat` double DEFAULT NULL,
  `alt` double DEFAULT NULL,
  `hdg` double DEFAULT NULL,
  `spe` double DEFAULT NULL,
  `accuracy` double DEFAULT NULL,
  `hdop` double DEFAULT NULL,
  `vdop` double DEFAULT NULL,
  `pdop` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `lng` (`lng`),
  KEY `lat` (`lat`)
) ENGINE=InnoDB AUTO_INCREMENT=268229 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `logfile`
--

DROP TABLE IF EXISTS `logfile`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `logfile` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `revision` varchar(50) DEFAULT NULL,
  `swid` varchar(255) DEFAULT NULL,
  `swver` varchar(50) DEFAULT NULL,
  `filepath` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9506 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `scan`
--

DROP TABLE IF EXISTS `scan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scan` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `logfileid` int(11) DEFAULT NULL,
  `time` varchar(15) DEFAULT NULL,
  `distance` double DEFAULT NULL,
  `gpsid` int(11) DEFAULT NULL,
  `servingid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `logfileid` (`logfileid`),
  KEY `time` (`time`),
  KEY `gpsid` (`gpsid`),
  KEY `serviningid` (`servingid`)
) ENGINE=InnoDB AUTO_INCREMENT=268228 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-10 13:55:35

query($query))) {
		printf("Table truncation error: %s\n", $mysqli->error);
		exit();
	}
}

// Recursively read all files in the selected directory 
$ite = new RecursiveDirectoryIterator($path);
foreach (new RecursiveIteratorIterator($ite) as $filename=>$cur) {
	if (strstr($cur, ".xml") != "") {
		echo "Processing: ".$cur->getPathName().PHP_EOL;

		$filepath = str_ireplace("\\", "/", $cur->getPathname());

		$doc = new DOMDocument();
		$doc->load($filepath);

		$xpath = new DOMXpath($doc);
		$elements = $xpath->query("//*");

		foreach ($elements as $element) {
			if ($element->nodeName == "logfile") {
				$query = "INSERT IGNORE INTO logfile (manufacturer, model, revision, swid, swver, filepath) VALUES "; 

				$manufacturer = $element->getAttribute('manufacturer');
				$model = $element->getAttribute('model');
				$revision = $element->getAttribute('revision');
				$swid = $element->getAttribute('swid');
				$swver = $element->getAttribute('swver');

				$query .= "('$manufacturer','$model','$revision','$swid','$swver','$filepath')";

				if (!($result = $mysqli->query($query))) {
			   	printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   }  

				$logfileid = $mysqli->insert_id;

				$scans = $element->childNodes;
				foreach ($scans as $scan) {
					if ($scan->nodeName != "scan") continue;

					$time = $scan->getAttribute('time');
					$distance = $scan->getAttribute('distance');

					$childs = $scan->childNodes;

					$gsmnieghbours = array();
					foreach ($childs as $child) {
						if ($child->nodeName == "gps") {
							$gps_time = $child->getAttribute("time");
							$lng = $child->getAttribute("lng");
							$lat = $child->getAttribute("lat");
							$alt = $child->getAttribute("alt");
							$hdg = $child->getAttribute("hdg");
							$spe = $child->getAttribute("spe");
							$accuracy = $child->getAttribute("accuracy");
							$hdop = $child->getAttribute("hdop");
							$vdop = $child->getAttribute("vdop");
							$pdop = $child->getAttribute("pdop");
							
							$query = "INSERT IGNORE INTO gps (time, lng, lat, alt, hdg, spe, accuracy, hdop, vdop, pdop) VALUES "; 
							$query .= "('$gps_time','$lng','$lat','$alt','$hdg','$spe','$accuracy','$hdop','$vdop','$pdop')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  

							$gpsid = $mysqli->insert_id;
						}
						else if ($child->nodeName == "gsmserving" || $child->nodeName == "gsmneighbour") {
							$mcc = $child->getAttribute("mcc");
							$mnc = $child->getAttribute("mnc");
							$lac = $child->getAttribute("lac");
							$cellid = $child->getAttribute("id");
							$ss = $child->getAttribute("ss");
							$act = $child->getAttribute("act");
							$rxlev = $child->getAttribute("rxlev");
							
							$query = "INSERT IGNORE INTO cell (mcc, mnc, lac, cellid, ss, act, rxlev) VALUES "; 
							$query .= "('$mcc','$mnc','$lac','$cellid','$ss','$act','$rxlev')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  
							else {
								if ($child->nodeName == "gsmserving") {
									$servingid =  $mysqli->insert_id;
								}
								else if ($child->nodeName == "gsmneighbour") {
									array_push($gsmnieghbours, $mysqli->insert_id);
								}
							}
						}
					}

					$query = "INSERT IGNORE INTO scan (logfileid, time, distance, gpsid, servingid) VALUES "; 
					$query .= "('$logfileid','$time','$distance','$gpsid','$servingid')";

					if (!($result = $mysqli->query($query))) {
			   		printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   	}  
					else $scanid = $mysqli->insert_id;

					$query_pattern = "INSERT IGNORE INTO cell_neighbour (servingid, neighborhoodid) VALUES "; 
					foreach ($gsmnieghbours as $neighborhoodid) {
						$query = $query_pattern;
						$query .= "('$servingid','$neighborhoodid')";

						if (!($result = $mysqli->query($query))) {
			   			printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   		}  
					}
				}
			}
		}
	}
}	

$mysqli->close();

?>

 

Import Wifi location data

Data tables are prepared based on their log formats, which is in fact imcomplete, and thus inspecting actual files.

-- MySQL dump 10.13  Distrib 5.5.11, for Win64 (x86)
--
-- Host: localhost    Database: openbmap_wifi
-- ------------------------------------------------------
-- Server version	5.5.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `gps`
--

DROP TABLE IF EXISTS `gps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gps` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(15) DEFAULT NULL,
  `lng` double DEFAULT NULL,
  `lat` double DEFAULT NULL,
  `alt` double DEFAULT NULL,
  `hdg` double DEFAULT NULL,
  `spe` double DEFAULT NULL,
  `accuracy` double DEFAULT NULL,
  `hdop` double DEFAULT NULL,
  `vdop` double DEFAULT NULL,
  `pdop` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `lng` (`lng`),
  KEY `lat` (`lat`)
) ENGINE=InnoDB AUTO_INCREMENT=154027 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `logfile`
--

DROP TABLE IF EXISTS `logfile`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `logfile` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `revision` varchar(50) DEFAULT NULL,
  `swid` varchar(255) DEFAULT NULL,
  `swver` varchar(50) DEFAULT NULL,
  `filepath` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3408 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `scan`
--

DROP TABLE IF EXISTS `scan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scan` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `logfileid` int(11) DEFAULT NULL,
  `time` varchar(15) DEFAULT NULL,
  `distance` double DEFAULT NULL,
  `gpsid` int(11) DEFAULT NULL,
  `wifiapid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `logfileid` (`logfileid`),
  KEY `time` (`time`),
  KEY `gpsid` (`gpsid`),
  KEY `serviningid` (`wifiapid`)
) ENGINE=InnoDB AUTO_INCREMENT=77014 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wifiap`
--

DROP TABLE IF EXISTS `wifiap`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wifiap` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bssid` varchar(12) DEFAULT NULL,
  `md5essid` varchar(31) DEFAULT NULL,
  `capa` varchar(255) DEFAULT NULL,
  `ss` int(11) DEFAULT NULL,
  `ntiu` int(11) DEFAULT NULL,
  `enc` int(11) DEFAULT NULL,
  `im` int(11) DEFAULT NULL,
  `enc_type` varchar(50) DEFAULT NULL,
  `chan` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cellid` (`bssid`)
) ENGINE=InnoDB AUTO_INCREMENT=268548 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-10 13:54:59

query($query))) {
		printf("Table truncation error: %s\n", $mysqli->error);
		exit();
	}
}

// Recursively read all files in the selected directory 
$ite = new RecursiveDirectoryIterator($path);
foreach (new RecursiveIteratorIterator($ite) as $filename=>$cur) {
	if (strstr($cur, ".xml") != "") {
		echo "Processing: ".$cur->getPathName().PHP_EOL;

		$filepath = str_ireplace("\\", "/", $cur->getPathname());

		$doc = new DOMDocument();
		$doc->load($filepath);

		$xpath = new DOMXpath($doc);
		$elements = $xpath->query("//*");

		foreach ($elements as $element) {
			if ($element->nodeName == "logfile") {
				$query = "INSERT IGNORE INTO logfile (manufacturer, model, revision, swid, swver, filepath) VALUES "; 

				$manufacturer = $element->getAttribute('manufacturer');
				$model = $element->getAttribute('model');
				$revision = $element->getAttribute('revision');
				$swid = $element->getAttribute('swid');
				$swver = $element->getAttribute('swver');

				$query .= "('$manufacturer','$model','$revision','$swid','$swver','$filepath')";

				if (!($result = $mysqli->query($query))) {
			   	printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   }  

				$logfileid = $mysqli->insert_id;

				$scans = $element->childNodes;
				foreach ($scans as $scan) {
					if ($scan->nodeName != "scan") continue;

					$time = $scan->getAttribute('time');
					$distance = $scan->getAttribute('distance');

					$childs = $scan->childNodes;

					$gsmnieghbours = array();
					foreach ($childs as $child) {
						if ($child->nodeName == "gps") {
							$gps_time = $child->getAttribute("time");
							$lng = $child->getAttribute("lng");
							$lat = $child->getAttribute("lat");
							$alt = $child->getAttribute("alt");
							$hdg = $child->getAttribute("hdg");
							$spe = $child->getAttribute("spe");
							$accuracy = $child->getAttribute("accuracy");
							$hdop = $child->getAttribute("hdop");
							$vdop = $child->getAttribute("vdop");
							$pdop = $child->getAttribute("pdop");
							
							$query = "INSERT IGNORE INTO gps (time, lng, lat, alt, hdg, spe, accuracy, hdop, vdop, pdop) VALUES "; 
							$query .= "('$gps_time','$lng','$lat','$alt','$hdg','$spe','$accuracy','$hdop','$vdop','$pdop')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  

							$gpsid = $mysqli->insert_id;
						}
						else if ($child->nodeName == "wifiap") {
							$bssid = $child->getAttribute("bssid");
							$md5essid = $child->getAttribute("md5essid");
							$capa = $child->getAttribute("capa");
							$ss = $child->getAttribute("ss");
							$ntiu = $child->getAttribute("ntiu");
							$enc = $child->getAttribute("enc");
							$im = $child->getAttribute("im");
							$enc_type = $child->getAttribute("enc_type");
							$chan = $child->getAttribute("chan");
							
							$query = "INSERT IGNORE INTO wifiap (bssid, md5essid, capa, ss, ntiu, enc, im, enc_type, chan) VALUES "; 
							$query .= "('$bssid','$md5essid','$capa','$ss','$ntiu','$enc','$im','$enc_type','$chan')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  
							else {
								$wifiapid =  $mysqli->insert_id;
							}
						}
					}

					$query = "INSERT IGNORE INTO scan (logfileid, time, distance, gpsid, wifiapid) VALUES "; 
					$query .= "('$logfileid','$time','$distance','$gpsid','$wifiapid')";

					if (!($result = $mysqli->query($query))) {
			   		printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   	}  
					else $scanid = $mysqli->insert_id;
				}
			}
		}
	}
}	

$mysqli->close();

?>

 

Implementing Event summarization on LifeLog Data

Project Member: Sajan Raj OJha

Problem Description
The influence of digital devices in human life cannot be ignored. Because of the easy availability of the digital devices the amount of digital data a human generates has grown enormously. The rich and vast amount of data that can be captured by these ubiquitous devices ranges from communication, location, proximity, motion, and video, to name a few. Recently, researchers are realizing the rich information that can be captured by these ubiquitous devices, which has potential impact on a vast range of domains including epidemiology, psychology and sociology, urban planning, security and intelligence, and health monitoring. The potential for use of this information is huge and mostly unexplored[1] . There is big research in the field of managing Personal Information. Personal Information Management (PIM) is an activity in which an individual stores his/her personal information items in order to retrieve and use them later. Such information items include files, emails, Web favorites, contacts, and notes . User has to personally search and summarize all the events himself, which is very daunting and cumbersome task. Managing them according to their nature for example grouping all the related images according to an event such as a Birthday Party. Mainly our focus is on the summarization and display of the event that has occurred in a person’s life.

Proposed Solution
We will try to build a system that is capable to summarize the images taken in near similar location along with GPS data. Image grouping will be done using different clustering techniques. Image clustering is a process of grouping images based on their similarity. The image clustering usually uses the color component, texture, edge, shape, or mixture of two components, etc[3].
The clustered image will help in exact event summarization in the particular location.

References
1. K. Farrahi and D. Gatica-Perez Discovering Routines from Large-Scale Human Locations using Probabilistic Topic Models ACM Transactions on Intelligent Systems and Technology, Special Issue on Activity Recognition, Vol. 2. No. 1, 2011.
2. Ofer Bergman, Ruth Beyth-Marom, Rafi Nachmias, Noa Gradovitch, and Steve Whittaker. 2008. Improved search engines and navigation preference in personal information management. ACM Trans. Inf. Syst. 26, 4, Article 20 (October 2008), 24 pages.
5. Journey on Image Clustering Based on Color Composition, Achmad Nizar Hidayanto, Elisabeth Martha Koeanan.

Sensors

Lifelogging starts from objective monitoring of a user digitizing human actions using various sensors. This category includes our hands-on experiments testing or developing various lifelogging sensors. We recently bought a new hardware system including the GoPro 3D system, Garmin Edge 500, an external hard drive case, a battery charger to build up a new Phase-II life log experiment and more soon. I was satisfied with the first testing result and here the course of e-log system design will be recorded for your information.

 

Garmin Edge 500

Garmin Edge 500 is a light weight GPS initially designed for bike riders. But it is also good for walkers. I put it in the small pocket at the top my sack that I carry everyday. For its performance test, I carried it on the way to back to home by walk. Below screenshots show its performance and accuracy very well in comparions with iPhone 3GS. 

The first image is the GPS record of Garmin on my commuting route from my office at POVO to my home at TRENTO downtown. It shows my route precisely. On average, the route error was less than 10 meters. The biggest one is about 40 meters away from the route and I was walking under the tree. This route is like getting down from the moutain top. So it was like walking through the woods.

This image is the result of iPhone 3GS. I turned it on at the same time with Garmin. It only records a portion of my route when I am completely exposed to the sun light (where no tree over my head). No needs for comparison. Garmin win!

GoPro 3D system

I've played with this new system time-to-time for two weeks now. Overall I found a number of major benefits and drawbacks to use for life logging experiments. Let's first see its technical specification.

  • Lens Type: Fixed Focus (2ft/.6m – ∞), glass, f/2.8, Super low-light sensitivity (>1.4 V/lux-sec)
  • Angle of View: 170º ultra wide angle in WVGA, 720p, or 960p mode, 127º wide angle in 1080p mode
  • HD Video Resolution Modes: 
1080p = 1920×1080 pixels (16:9), 30 fps, 15 Mbit/s data rate
960p = 1280×960 pixels (4:3), 30 fps, 12 Mbit/s data rate
  • Sensor Type: 1/2.5″ HD CMOS, 2.2µm-sized pixels
  • Video Format: H.264 compression, saved as Windows- & Mac-compatible MPEG4 (.mp4) file
  • Exposure Control: Auto with user selectable center weighted average and spot metering settings, Auto white balance.
  • Dimensions: 130mm x 62mm x 45mm, 350 gram

Originally GoPro HD camera looks designed to capture sport activities where fast shutter speed, small size, light weight, wide view angle, long battery time and the quality of video are important. It comes with many types of accessories for this purpose. 3D camera is an extension to capture a movie in 3D using two GoPro HD camera. All satisifiy the need for life logs.

Compared to ViconRevue, it weighs double and with an enclosing case, it clearly gets heavier. I felt uncomportable when I hang it on my neck, whereas with ViconRevue, I sometimes completely forgot I had it on my neck. So I had to use it with the chest mount (See the picture).

One problem with the chest mount is that acutal video shots tremble a lot as I was walking or moving. This happens equally for ViconRevue, which is a camera that takes a shot every tens of seconds. So for ViconRevue images mostly get motion blurred. For GoPro, videos just tremble a lot. The good part is that thanks to their fast shutter speed captured video frames are cristal clear without any blurring. The answer is then software video stablization.

Challenging problem is becuase I am moving, in other words camera is moving forward, most frame-by-frame matching methods (Feature points matching or phase correlation) do not work well. I am currently working on this matter since without it its 3D video easily gives you a headache. 

MPI Lifelogger design

This page is dedicated to the MPI (Multiple-Perspective-Interactive) lifelogging device design experiment. An open source & open design project contributed by multiple eLifeLog expert members.

MPI Lifelogger (April 16th, 2013)

[First prototype: April 16th, 2013]

It will envisages a better lifelogging hardware in combination of eLifeLog APIs. Firstly all relevant information will be collected here. And we will see its practicability and possible ideas.

 

MPI logger weight

The weight of fully assembled system is about 945 grams (47 grams custom-made five camera mount, 501 grams 5 GoPro aqua boxes, and 397 grams 5 GoPro cameras).

Figure: 

Portable Sensor Board Design

 

Raspberry PI & Arduino

  • Order page: https://export.farnell.com/jsp/raspi/orderPad.jsp?&country=IT
  • Wiki: http://elinux.org/RaspberryPiBoard
  • Extensions:
    • Raspberry Pi + Arduino + Node = Home Automation: After figuring out how to set up the Raspberry Pi and how to communicate between a Raspberry Pi and an Arduino, I wanted to try a simple home-automation project that incorporated several really awesome and useful technologies:
    • The #RaspberryPi Tricorder – the PiPodCorder: The PiPodCorder is a Pi/Arduino combination taking readings from 2 temperature sensors, a hall-effect magnetism sensor and an ultrasonic distance sensor which it then displays, with the aid of a Python script, on an LCD display. The Pi also displays it’s own IP address(es) which can come in useful sometimes!
    • Raspberry Pi to Arduino shields connection bridge: The idea behind the Raspberry Pi to Arduino shields connection bridge is to allow to use any of the shields, boards and modules designed for Arduino in Raspberry Pi. It includes also the possibility of connecting digital and analog sensors, using the same pinout of Arduino but with the power and capabilities of Raspberry.
    • STARTER KIT FOR RASPBERRY PI (A/V EDITION) + RASPBERRY PI: Cooking Hacks launches its new Starter Kit for Raspberry Pi with Audio/Video Kit, including the Raspberry Pi board. This one is focused to all people who want to take advantage of GPIO connection, making new proyects with this awesome board. It will allow you to get a "ready-to-use" kit for your Raspberry Pi board, including all you need to connect it to your TV. It includes:
    • http://www.cooking-hacks.com/index.php/gprs-sim900-shield-for-raspberry-pi.html: GPRS SIM900 shield for Raspberry Pi offers GPRS connection to your Raspberry Pi board. It includes the SIM900 communication module from SIMCom. It is necessary an antenna in order to establish a communication.
       

References

Prototype History

First prototype: April 16th, 2013

The first prototype is a collection of 5 GoPro Hero3 black edition with an anchor designed for them. This anchor (designed by @Aleg) is a 3D printed one...

MPI Lifelogger (April 16th, 2013)

[First prototype: April 16th, 2013]

(@Aleg, please put some details below).

Downloads

Visit  https://github.com/eLifeLogTeam/mpi_lifelogger

Features

 

...

Improvements

...

 

 

 

Raspberry PI setup

This lab collects information on Raspberry Pi setup for lifelog management.

References:

Linking NAS with RPI

Since RPI has a limited storage, we link it with the NAS (MyLiveBook Duo Live in this experiment). When linking NAS with the RPI, we first tried SAMBA connection that NAS natively supports but found many problems like permission or symbolic linking within a link. So we decided to use the mounting method through FTP (Not SFTP due to the speed problem) using curlftpfs. Details on the final configuration will be recorded later.

Additional tips when reading USB sticks (ex. camera memory card), then check out this page http://elinux.org/RPi_Adding_USB_Drives

Studies on wireless data transmission

We are looking for the way for real-time synchronization of multiple external devices through wireless SD cards embodied in the sensor (Cameras, sensors, etc).

One of our earliest effort to gain the full control is on Eye-fi and its hack iii. I first ported iii codes for Mac. After downloading their codes, run autogen.sh there. It will complain missing packages, which mostly you can find in MacPort (Run 'sudo port install missing-packages'). For libarchive installation, I used the code at github.  In my case, it couldn't find autosprintf library which were located at /opt/local. The workaround I applied for compilation is

./configure CXXFLAGS="-I/opt/local/include -L/opt/local/lib"

You will find binaries (iiid, iii-extract-riff-chunk) under src sub-directory.

We can find more info on Eye-fi hack (exact keywords to google). And later will add more if using otherwise iii.

GoPro & GPS Time sync: Problems and Solutions

A GoPro camera also has its own internal clock where again the sync between GPS is quite important. Recent data from Pierre shows some challenging problem that two cameras (one taken by Pierre and the other by his friend) showed inconsistency in time that makes the time sync between three sensors quite challenging: two GoPro cameras and one GPS. So I first found the time gap between two cameras by analyzing the sound wave correlation (See the below screen shot of Audacity for Mac). 

So I could realize that the time difference between two cameras is 00:01:22 considerating the difference in their creation time. However, this does not mean that we can simply shift the creation time of one video file of two by that much becuase sadly the file creation time (which you can identify using ffmpeg -i inputfile.MP4) is not exact. 

To verify the problem in setting the exact clock of GoPros, I tried to sync the GoPro clock with the UTC time (Take a video shot of http://www.timeanddate.com/worldclock/city.html?n=215 using GoPro). I set the clocks of two cameras at the same moment but the output later checked using the video shows 23 seconds difference between two clocks (Weird!). Thus the exact start time of Pierre's cases should be retrieved from the GPS coordinate by matching the scene in the video. Once we find a one good matching spot between GPS and GoPro, we can use it for the entire collections.

Unfortunately, GoPro cameras do not have a way to set the time resoultion accurate to seconds. Its interface supports down to Minute setting. Thus to prevent further troubles, here I am setting a number of rules for time synchronization.

  • Our two cameras are marked at the backside as "L" and "R" each. Its memory card is also marked like. Thus DO NOT mix up the memory card with the other camera.
  • At the start of video shots, take a video of exact time. You may use the GPS clocks. Press "Enter" key of Garmin GPS for 3 seconds, date and timestamp will be shown at the bottom. But it is a little tricky to catch that small fonts in a reflective GPS screen. So you may want to sync with iPhone but never use its internal clock. Rather open the browser and take a video shot of http://www.timeanddate.com/worldclock/fullscreen.html?n=215 (Bookmark this link with your iPhone). 

As of today (July 8th, 2011), both cameras are tested a number of times. Check out below information after calibration. Hope the cameras keeps this info without further changes. So it is important never taking out a battery from a camera.

  • Left GoPro camera: -1 seconds delay. After taking a movie, you have to change the creation time by adding 1 second from the original.
  • Right GoPro camera: -1 seconds delay. After taking a movie, you have to change the creation time by adding 1 seconds from the original.

Calibration notes:

 

 
 creation_time   : 2011-07-08 17:51:39
                                  2:42
                              17:54:21  ==> 17:53:34 +47 seconds
                              
                   2011-07-08 17:51:16
                                  2:42
                              17:53:58  ==> 17:53:34 +24 seconds
                              
                              37  14
                              
     Right           2011-07-08 20:01:22 
                                00:00:07
                                20:01:29 ==> 20:00:57 
    Left             2011-07-08 21:07:15
                                00:00:03
                                21:07:18 ==> 20:07:50 +32 -60 : 14
                    2011-07-08 21:15:51
                                00:00:03
                                21:15:54 ==> 21:15:23 +22 -60 : 14 36 - 60 : 24
                    2011-07-08 21:40:27
                               00:00:01
                               21:40:28 ==> 21:40:08 +20, press after 20 seconds
                    2011-07-08 21:45:12
                               00:00:01
                               21:45:13 ==> 21:45:06 +7 press after 7 seconds
                    2011-07-08 21:49:24
                               00:00:01
                               21:49:25 ==> 21:49:22 +3 press after 3 seconds
                    2011-07-08 21:52:52
                               00:00:03
                               21:52:55 ==> 21:52:20 +30 press after +30
                    2011-07-08 21:56:54
                               00:00:03
                               21:56:57 ==> 21:57:06 -6 press after +24
                    2011-07-08 22:01:59
                               00:00:03
                               22:02:02 ==> 22:02:03 -1
                               
        Right       2011-07-08 20:01:22
          00:00:29
          20:01:47 ==> 21:00:50 
        2011-07-08 22:06:33
                  00:00:02
                  22:06:35 ==> 22:06:27 +7
        2011-07-08 22:09:46 ==> 22:09:17 +30
        2011-07-08 22:12:44 ==> 22:13:00 press after +30
        2011-07-08 22:15:42 ==> 22:15:43 -1
                    

Student projects

This lab is the collection of student projects

Summarization of lifelog data

Problem description
Lifelog data is a huge amout of heterogeneous information that describe the life of a person. The main problem in using these data is how to extract useful information and show them to the user in a fast and easy-to-use manner.
The summarization of lifelog data can be useful in many context:

  • Helps mental diseased people to recover lost memories;
  • Can retrieve digital information acquired over the years;
  • Can retrieve our personal information about physical activity or, in general, past experience.

Problem and solution specification
We want to address the problem of summarising frequent event that happens during a user–specified period of time. The key idea that drives our solution is to proceed with the summarisation by steps, as it follows:

  • GPS Points: first of all we need to extract all the paths that the subject has done in the given period of time. With this data we can recognize the most frequent path.
  • People: from the paths it is possible to extract the people that the subject have encounter more times, in order to get the frequent person seen.
  • Images: with the new information we can now get the most interesting pictures that can describe the place and the people seen in this period of time. The photos will be selected from the ones captured on the starting and arrival place and we use some criteria to choose which one is better.

Plan of work
Our work can be divided in four ordered phases, like follows:

  1. Path identification
    We try to get the paths by grouping the single GPS points. The idea is to start with the first point and then add one point at a time (ordered by timestamp) until the object is on the arrival area.
    • Used criteria: we assume that the starting and ending point of a path is when the user stand still (or in a small area) in a place for at least 10–20 min (this is done by analysing the distances between the points of the last 20 min).
  2. Clustering area and retrieve path of interest
    At this step we cluster similar area or path together and then we can retrieve the most frequent tracks. To do this we use the frequent itemset search algorithm on location of starting and ending points. From this set we will select the two most frequent paths.
  3. People of interest
    We can connect all the images to the people that appear in them (there can be some anonymous person) and find the people that the subject have seen more times during the path.
  4. Images to remember
    The people seen by the subject can help us to restrict the number of interesting photos to publish in the summary. We believe that it’s easier to remember events from the people that have taken part in it. Another criteria is to use the SenseCam’s data like light level and accelerometer. Because this take place in different days, we use an algorithm that can split events in order to avoid to get too many images from the same event.
  5. Present the summarised data
    Provide the summarisation to the user in a fashion way. We will provide the possibility to get the wanted number of solutions.

How to evaluate our idea
The best method to evaluate if our system work is to get some summarisation from different periods of time and ask Pil Ho Kim and other people that take part in the life–log project to try to remember what they have done. Another way to evaluate is to compare some experimental solutions with the summarisation system made by our colleagues, for example giving a measure of quality of the images.
We will add some other measure as we improve the project and we get information about summarising algorithm from the other groups.

References

  1. A. R. Doherty, C. Ó Conaire, M. Blighe, A. F. Smeaton, and N. E. O’Connor. Combining image descriptors to effectively retrieve events from visual lifelogs. In Proceeding of the 1st ACM international conference on Multimedia information retrieval, MIR ’08, pages 10–17, New York, NY, USA, 2008. ACM.
  2. A. R. Doherty and A. F. Smeaton. Automatically segmenting lifelog data into events. In Proceedings of the 2008 Ninth International Workshop on Image Analysis for Multimedia Interactive Services, pages 20–23, Washington, DC, USA, 2008. IEEE Computer Society.
  3. A. Fitzgibbon and E. Reiter. “Memories for life” Managing information over a human lifetime. 2003.
  4. A. J. Sellen, A. Fogg, M. Aitken, S. Hodges, C. Rother, and K. Wood. Do life-logging technologies support memory for the past?: an experimental study using sensecam. In Proceedings of the SIGCHI conference on Human factors in computing systems, CHI ’07, pages 81–90, New York, NY, USA, 2007. ACM.
  5. A. J. Sellen and S. Whittaker. Beyond total capture: a constructive critique of lifelogging. Commun. ACM, 53:70–77, May 2010.

Summerization of lifelog images