Skip to content

Instantly share code, notes, and snippets.

@uprel

uprel/search_views.sql

Last active February 24, 2021 12:46
Show Gist options
  • Save uprel/11ea3032bf97d8c38c044d09a92cf3a3 to your computer and use it in GitHub Desktop.
Save uprel/11ea3032bf97d8c38c044d09a92cf3a3 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. uprel revised this gist Feb 24, 2021. 1 changed file with 0 additions and 3 deletions.
    3 changes: 0 additions & 3 deletions search_views.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -3,7 +3,6 @@ CREATE OR REPLACE VIEW public.search_pop_places AS
    (pop_places.type::text || ' '::text) || pop_places.name::text AS displaytext,
    '01_pop_places'::text AS search_category,
    pop_places.geom AS the_geom,
    st_geometrytype(pop_places.geom) AS geometry_type,
    'pop_places'::text AS showlayer
    FROM pop_places;

    @@ -12,7 +11,6 @@ CREATE OR REPLACE VIEW public.search_airports AS
    (airports.name || ' (' || airports.iata ||')') AS displaytext,
    '02_airports'::text AS search_category,
    airports.geom AS the_geom,
    st_geometrytype(airports.geom) AS geometry_type,
    'airports'::text AS showlayer
    FROM airports;

    @@ -21,6 +19,5 @@ CREATE OR REPLACE VIEW public.search_admin AS
    (admin.admintype::text || ' '::text) || admin.name::text AS displaytext,
    '03_municipality'::text AS search_category,
    admin.geom AS the_geom,
    st_geometrytype(admin.geom) AS geometry_type,
    'municipality'::text AS showlayer
    FROM admin;
  2. uprel created this gist Jan 6, 2018.
    26 changes: 26 additions & 0 deletions search_views.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,26 @@
    CREATE OR REPLACE VIEW public.search_pop_places AS
    SELECT pop_places.name AS searchstring,
    (pop_places.type::text || ' '::text) || pop_places.name::text AS displaytext,
    '01_pop_places'::text AS search_category,
    pop_places.geom AS the_geom,
    st_geometrytype(pop_places.geom) AS geometry_type,
    'pop_places'::text AS showlayer
    FROM pop_places;

    CREATE OR REPLACE VIEW public.search_airports AS
    SELECT (airports.name || ' ' || airports.iata) AS searchstring,
    (airports.name || ' (' || airports.iata ||')') AS displaytext,
    '02_airports'::text AS search_category,
    airports.geom AS the_geom,
    st_geometrytype(airports.geom) AS geometry_type,
    'airports'::text AS showlayer
    FROM airports;

    CREATE OR REPLACE VIEW public.search_admin AS
    SELECT admin.name AS searchstring,
    (admin.admintype::text || ' '::text) || admin.name::text AS displaytext,
    '03_municipality'::text AS search_category,
    admin.geom AS the_geom,
    st_geometrytype(admin.geom) AS geometry_type,
    'municipality'::text AS showlayer
    FROM admin;
    42 changes: 42 additions & 0 deletions table_structure.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    CREATE TABLE public.pop_places
    (
    gid integer NOT NULL DEFAULT nextval('pop_places_gid_seq'::regclass),
    name character varying(254),
    iso_cc character varying(4),
    type character varying(30),
    rank smallint,
    population character varying(30),
    geom geometry(Point),
    CONSTRAINT pop_places_pkey PRIMARY KEY (gid)
    )

    CREATE TABLE public.airports
    (
    gid integer NOT NULL DEFAULT nextval('airports_gid_seq'::regclass),
    iso_cc character varying(4),
    name character varying(100),
    icao character varying(5),
    iata character varying(5),
    geom geometry(Point,4326),
    CONSTRAINT airports_pkey PRIMARY KEY (gid)
    )

    CREATE TABLE public.admin
    (
    gid integer NOT NULL DEFAULT nextval('admin_gid_seq'::regclass),
    name character varying(44),
    country character varying(50),
    iso_code character varying(6),
    iso_cc character varying(2),
    iso_sub character varying(5),
    admintype character varying(50),
    disputed integer,
    notes character varying(254),
    autonomous integer,
    countryaff character varying(100),
    continent character varying(13),
    land_type character varying(20),
    land_rank integer,
    geom geometry(MultiPolygon,4326),
    CONSTRAINT admin_pkey PRIMARY KEY (gid)
    )