About

The eLog library was initially developed as a research prototype and later published for lifelogging researchers in 2010 to help them easily analyze heterogenous data and complex visualization. It has been kept growing with the progress of mobile computing environments and recently its UI part is released with GPL v3 license for wider usage. The eLog UI library is optimized for mobile environment and can get easily integrated with existing Web services.

Who We Are

The original work was proposed by Pil Ho and later extended the work with collaboration with 28 researchers around the world who contributed their lifelogs, collaborated for lifelog analysis and share research results to build up an open lifelogging platform for the public. Pil Ho has been keeping the development updating the library following up the progress in mobile computing.

Updates

  • Nov. 2014: Change the web page skin using bootstrap.
  • Nov. 2014: Published elog UI library as GPL v3.
  • Oct. 2014: Version up eLog library and documentation.

 

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