pilhokim/classtype.sql

## classtype.sql
--
-- 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
--

## gps_get_address
-- Function: gps_get_address(double precision, double precision)
-- This directly retrieves the full address from latitude and longitude
-- DROP FUNCTION gps_get_address(double precision, double precision);

CREATE OR REPLACE FUNCTION gps_get_address(flat double precision, flon double precision)
  RETURNS text 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;
  aLine RECORD;
  foundCount INTEGER;
  foundText TEXT;
  foundCount1 INTEGER;
  foundCount2 INTEGER;
  sAddressType TEXT;
  sClassType TEXT;
  aKeyValue RECORD;
  aLineJson JSON;
  langAddress TEXT;
BEGIN


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,
  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;

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);

TRUNCATE TABLE my_keyvaluestore;

searchPlaceId := NULL;
searchParentPlaceId := NULL;
searchDiam := 0.0004;
searchMaxAreaDistance := 1;
searchMaxRank := 28;

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(fLon,fLat),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(fLon,fLat),4326), centroid, searchDiam))
  ORDER BY ST_distance(ST_SetSRID(ST_Point(fLon,fLat),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 < 1 or searchPlaceId IS NULL THEN
  RAISE NOTICE 'Found no place id (%)', searchPlaceId;
  return false;
ELSE
  RAISE NOTICE 'Found place id (%)', searchPlaceId;
END IF;

select
  address_place_id
from place_addressline
where place_id = searchPlaceId
order by
  abs(cached_rank_address - 28) asc,
  cached_rank_address desc,
  isaddress desc,
  distance desc
limit 1
INTO addressPlaceId;

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(addressPlaceId, 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 = addressPlaceId
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(addressPlaceId, 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;


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(addressPlaceId)
  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
        aTypeLabel := ROW(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'address'||trim(both ' ' from to_char(aLine.rank_address, '99999')))::classtype;
        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 ((aLineJson->'simplelabel') IS NOT NULL AND  aTypeLabel.simplelabel IS NOT NULL) THEN
      sTypeLabel := aTypeLabel.simplelabel;
    ELSE
      sTypeLabel := 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 (aLine.class = 'place' OR (foundCount1 < 1) OR (foundCount1 > 0)) 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(addressPlaceId, 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;

  RETURN langAddress;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gps_get_address(double precision, double precision)
  OWNER TO postgres;

## gps_get_address_detail.sql
-- Function: gps_get_address_detail(double precision, double precision)

DROP FUNCTION gps_get_address_detail(double precision, double precision);
CREATE OR REPLACE FUNCTION gps_get_address_detail(flat double precision, flon double precision)
  RETURNS RECORD 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;
  aLine RECORD;
  foundCount INTEGER;
  foundText TEXT;
  foundCount1 INTEGER;
  foundCount2 INTEGER;
  sAddressType TEXT;
  sClassType TEXT;
  aKeyValue RECORD;
  aLineJson JSON;
  langAddress TEXT;
  retResult RECORD;
BEGIN

CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

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,
  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;

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);

TRUNCATE TABLE my_keyvaluestore;

searchPlaceId := NULL;
searchParentPlaceId := NULL;
searchDiam := 0.0004;
searchMaxAreaDistance := 1;
searchMaxRank := 28;

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(fLon,fLat),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(fLon,fLat),4326), centroid, searchDiam))
  ORDER BY ST_distance(ST_SetSRID(ST_Point(fLon,fLat),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 < 1 or searchPlaceId IS NULL THEN
  RAISE NOTICE 'Found no place id (%)', searchPlaceId;
  return false;
ELSE
  RAISE NOTICE 'Found place id (%)', searchPlaceId;
END IF;

select
  address_place_id
from place_addressline
where place_id = searchPlaceId
order by
  abs(cached_rank_address - 28) asc,
  cached_rank_address desc,
  isaddress desc,
  distance desc
limit 1
INTO addressPlaceId;

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::float8/40)) as importance,
  indexed_status, indexed_date, wikipedia, calculated_country_code,
  get_address_by_language(addressPlaceId, 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 = addressPlaceId
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(addressPlaceId, 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;


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(addressPlaceId)
  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
        -- aTypeLabel := ROW(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'address'||trim(both ' ' from to_char(aLine.rank_address, '99999')))::classtype;
        SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'address'||trim(both ' ' from to_char(aLine.rank_address, '99999')) INTO aTypeLabel;
        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 ((aLineJson->'simplelabel') IS NOT NULL AND (aTypeLabelJson->>'simplelabel') IS NOT NULL) THEN
      sTypeLabel := aTypeLabel.simplelabel;
    ELSIF ((aLineJson->'label') IS NOT NULL AND (aTypeLabelJson->>'label') IS NOT NULL) THEN
      sTypeLabel := aTypeLabel.label;
    END IF;

    IF (sTypeLabel IS NOT NULL) THEN
      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 (aLine.class = 'place' OR (foundCount1 < 1) OR (foundCount1 > 0)) 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 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(addressPlaceId, 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;

SELECT
  '{"latitude":"'||searchLatitude||'",'||
  '"longitude":"'||searchLongitude||'",'||
  '"distance_in_meters":"'||earth_distance(ll_to_earth(flat::float8, flon::float8), ll_to_earth(searchLatitude::float8, searchLongitude::float8))||'",'||
  string_agg('"addr'||lower(my_key)||'":"'||my_value||'"', ',')||','||
  '"addr:langAddress":"'||langAddress||'"'||
  '}'
FROM my_keyvaluestore
WHERE
  my_value IS NOT NULL AND
  my_value != '' AND
  my_category = 'aAddress'
INTO retResult;

RETURN retResult;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gps_get_address_detail(double precision, double precision)
  OWNER TO postgres;

## gps_get_placeids.sql
-- Function: gps_get_placeids()
-- This performs the reverse geocoding on GPS data stored in my_gpsdata table.
-- You may change the source GPS data table and modify the code inside
-- 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;
-- 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;

## gps_reverse_geocode_from_placeids.sql
-- Function: gps_reverse_geocode_from_placeids()
-- Reversed data will be stored at my_sensor table
-- 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;
	-- Function: gps_get_address(double precision, double precision)
	-- This directly retrieves the full address from latitude and longitude
	-- DROP FUNCTION gps_get_address(double precision, double precision);

	CREATE OR REPLACE FUNCTION gps_get_address(flat double precision, flon double precision)
	RETURNS text 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;
	aLine RECORD;
	foundCount INTEGER;
	foundText TEXT;
	foundCount1 INTEGER;
	foundCount2 INTEGER;
	sAddressType TEXT;
	sClassType TEXT;
	aKeyValue RECORD;
	aLineJson JSON;
	langAddress TEXT;
	BEGIN


	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,
	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;

	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);

	TRUNCATE TABLE my_keyvaluestore;

	searchPlaceId := NULL;
	searchParentPlaceId := NULL;
	searchDiam := 0.0004;
	searchMaxAreaDistance := 1;
	searchMaxRank := 28;

	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(fLon,fLat),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(fLon,fLat),4326), centroid, searchDiam))
	ORDER BY ST_distance(ST_SetSRID(ST_Point(fLon,fLat),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 < 1 or searchPlaceId IS NULL THEN
	RAISE NOTICE 'Found no place id (%)', searchPlaceId;
	return false;
	ELSE
	RAISE NOTICE 'Found place id (%)', searchPlaceId;
	END IF;

	select
	address_place_id
	from place_addressline
	where place_id = searchPlaceId
	order by
	abs(cached_rank_address - 28) asc,
	cached_rank_address desc,
	isaddress desc,
	distance desc
	limit 1
	INTO addressPlaceId;

	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(addressPlaceId, 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 = addressPlaceId
	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(addressPlaceId, 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;


	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(addressPlaceId)
	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
	aTypeLabel := ROW(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'address'\|\|trim(both ' ' from to_char(aLine.rank_address, '99999')))::classtype;
	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 ((aLineJson->'simplelabel') IS NOT NULL AND aTypeLabel.simplelabel IS NOT NULL) THEN
	sTypeLabel := aTypeLabel.simplelabel;
	ELSE
	sTypeLabel := 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 (aLine.class = 'place' OR (foundCount1 < 1) OR (foundCount1 > 0)) 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(addressPlaceId, 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;

	RETURN langAddress;
	END;
	$BODY$
	LANGUAGE plpgsql VOLATILE
	COST 100;
	ALTER FUNCTION gps_get_address(double precision, double precision)
	OWNER TO postgres;
	-- Function: gps_get_address_detail(double precision, double precision)

	DROP FUNCTION gps_get_address_detail(double precision, double precision);
	CREATE OR REPLACE FUNCTION gps_get_address_detail(flat double precision, flon double precision)
	RETURNS RECORD 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;
	aLine RECORD;
	foundCount INTEGER;
	foundText TEXT;
	foundCount1 INTEGER;
	foundCount2 INTEGER;
	sAddressType TEXT;
	sClassType TEXT;
	aKeyValue RECORD;
	aLineJson JSON;
	langAddress TEXT;
	retResult RECORD;
	BEGIN

	CREATE EXTENSION IF NOT EXISTS cube;
	CREATE EXTENSION IF NOT EXISTS earthdistance;

	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,
	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;

	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);

	TRUNCATE TABLE my_keyvaluestore;

	searchPlaceId := NULL;
	searchParentPlaceId := NULL;
	searchDiam := 0.0004;
	searchMaxAreaDistance := 1;
	searchMaxRank := 28;

	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(fLon,fLat),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(fLon,fLat),4326), centroid, searchDiam))
	ORDER BY ST_distance(ST_SetSRID(ST_Point(fLon,fLat),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 < 1 or searchPlaceId IS NULL THEN
	RAISE NOTICE 'Found no place id (%)', searchPlaceId;
	return false;
	ELSE
	RAISE NOTICE 'Found place id (%)', searchPlaceId;
	END IF;

	select
	address_place_id
	from place_addressline
	where place_id = searchPlaceId
	order by
	abs(cached_rank_address - 28) asc,
	cached_rank_address desc,
	isaddress desc,
	distance desc
	limit 1
	INTO addressPlaceId;

	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::float8/40)) as importance,
	indexed_status, indexed_date, wikipedia, calculated_country_code,
	get_address_by_language(addressPlaceId, 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 = addressPlaceId
	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(addressPlaceId, 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;


	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(addressPlaceId)
	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
	-- aTypeLabel := ROW(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'address'\|\|trim(both ' ' from to_char(aLine.rank_address, '99999')))::classtype;
	SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'address'\|\|trim(both ' ' from to_char(aLine.rank_address, '99999')) INTO aTypeLabel;
	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 ((aLineJson->'simplelabel') IS NOT NULL AND (aTypeLabelJson->>'simplelabel') IS NOT NULL) THEN
	sTypeLabel := aTypeLabel.simplelabel;
	ELSIF ((aLineJson->'label') IS NOT NULL AND (aTypeLabelJson->>'label') IS NOT NULL) THEN
	sTypeLabel := aTypeLabel.label;
	END IF;

	IF (sTypeLabel IS NOT NULL) THEN
	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 (aLine.class = 'place' OR (foundCount1 < 1) OR (foundCount1 > 0)) 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 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(addressPlaceId, 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;

	SELECT
	'{"latitude":"'\|\|searchLatitude\|\|'",'\|\|
	'"longitude":"'\|\|searchLongitude\|\|'",'\|\|
	'"distance_in_meters":"'\|\|earth_distance(ll_to_earth(flat::float8, flon::float8), ll_to_earth(searchLatitude::float8, searchLongitude::float8))\|\|'",'\|\|
	string_agg('"addr'\|\|lower(my_key)\|\|'":"'\|\|my_value\|\|'"', ',')\|\|','\|\|
	'"addr:langAddress":"'\|\|langAddress\|\|'"'\|\|
	'}'
	FROM my_keyvaluestore
	WHERE
	my_value IS NOT NULL AND
	my_value != '' AND
	my_category = 'aAddress'
	INTO retResult;

	RETURN retResult;

	END;
	$BODY$
	LANGUAGE plpgsql VOLATILE
	COST 100;
	ALTER FUNCTION gps_get_address_detail(double precision, double precision)
	OWNER TO postgres;
	-- Function: gps_get_placeids()
	-- This performs the reverse geocoding on GPS data stored in my_gpsdata table.
	-- You may change the source GPS data table and modify the code inside
	-- 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;
	-- 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()
	-- Reversed data will be stored at my_sensor table
	-- 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;