Instantly share code, notes, and snippets.

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

This comment has been minimized.

Owner

pilhokim commented Dec 4, 2014

Usage example:
Put (Latitude, longitude, unixtimestamp in integer, timestamp with microsecond if available:

SELECT lookup_address(46.038843, 11.058370, 1278691460, '2010-07-09 18:04:20');

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment