-
-
Save pilhokim/bc7b4f66cdb8117d6edc to your computer and use it in GitHub Desktop.
Nominatim OSM reverse geocoding PostgreSQL statements excerpted from Nominatim codes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- 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 | |
-- | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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');