30 lines
1.3 KiB
SQL
30 lines
1.3 KiB
SQL
-- colon before variable: for a prepared statement using named placeholders, this will be a parameter name of the form :name
|
|
CREATE SCHEMA IF NOT EXISTS :schema;
|
|
SET search_path to :schema, public;
|
|
|
|
CREATE OR REPLACE VIEW :schema.vw_export_poi AS
|
|
|
|
SELECT foo.id, foo.kat, tl.deutsch as kat_de, tl.english as kat_en, foo.kat_sub as kat_sub_de, foo.kat_sub as kat_sub_en, foo.name,
|
|
foo.adr_street AS strasse, foo.plz, foo.gemeinde, foo.landkreis,
|
|
foo.stellplaetze, foo.emob, foo.linien,
|
|
foo.website, foo.phone, foo.betreiber, foo.oeffnungszeiten, foo.st_x as x_wgs84, foo.st_y as y_wgs84, foo.haltestelle
|
|
FROM
|
|
(
|
|
--- erstellt die Abfrage für den POI-Export
|
|
-- Teil POI
|
|
SELECT
|
|
id, kategorie AS kat, kat_sub, name,
|
|
trim(concat(adr_street, ' ', adr_housenumber)) as adr_street,
|
|
--- adr_street||' '||adr_housenumber as adr_street,
|
|
cast( adr_postcode as integer) as plz, landkreis, gemeinde,
|
|
0 as stellplaetze, false as emob,NULL as linien, website, phone, operator AS betreiber,
|
|
'' as oeffnungszeiten,
|
|
--- opening_hours,
|
|
st_x,st_y, NULL as haltestelle
|
|
FROM tbl_poi WHERE true
|
|
--- dient zum check ob im Feld adr_postcode nur Zahlen stehen
|
|
AND (adr_postcode ~ '^[0-9]+$' OR adr_postcode IS NULL))
|
|
as foo, tbl_kat_translate tl
|
|
WHERE foo.kat = tl.kat
|
|
ORDER BY tl.kat, foo.landkreis, foo.gemeinde, foo.name;
|