mapproxy.yaml

services:
  wms:
    md:
        title: MapProxy WMS Proxy
        abstract: This is the fantastic MapProxy.
        online_resource: http://mapproxy.org/
        contact:
            person: Your Name Here
            position: Technical Director
            organization:
            address: Fakestreet 123
            city: Somewhere
            postcode: 12345
            country: Germany
            phone: +49(0)000-000000-0
            fax: +49(0)000-000000-0
            email: [email protected]
        access_constraints:
            This service is intended for private and
            evaluation use only. The data is licensed
            as Creative Commons Attribution-Share Alike 2.0
            (http://creativecommons.org/licenses/by-sa/2.0/)
        fees: 'None'

sources:
  test_wms:
    type: wms
    req:
      url: http://osm.omniscale.net/proxy/service?
      layers: osm

  places:
    type: mapnik
    layers: stopplace, place, quay
    transparent: true
    mapfile: /home/skinkie/mapnik/world_style.xml

layers:
  - name: cascaded_test
    title: Cascaded Test Layer
    sources: [test_wms]

views.sql

drop view  quay_geom ;
drop view  stopplace_geom ;
drop view  place_geom ;

create view quay_geom as select quaycode, name, town, hoofdmodaliteit, tweedemodaliteit, ST_Transform(the_geom, 4326) from mv_quays join mv_passengerstopassignment using (quaycode) join stoppoint on (stoppoint.operator_id = dataownercode||':'||mv_passengerstopassignment.userstopcode);

create view stopplace_geom as select stopplacecode, sp.stopplacetype, sp.town, sp.publicname, ST_Transform(the_geom, 4326) from mv_stop_places as sp join (select stopplacecode, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from mv_quays join mv_passengerstopassignment using (quaycode) join stoppoint on (stoppoint.operator_id = dataownercode||':'||mv_passengerstopassignment.userstopcode) group by stopplacecode) as x using (stopplacecode);

create view place_geom as select placecode, p.publicname, p.town, ST_Transform(the_geom, 4326) from mv_places as p join (select placecode, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from mv_stop_places join mv_quays using (stopplacecode) join mv_passengerstopassignment using (quaycode) join stoppoint on (stoppoint.operator_id = dataownercode||':'||mv_passengerstopassignment.userstopcode) group by placecode) as x using (placecode);


create table quay_geom as select quaycode, name, town, hoofdmodaliteit, tweedemodaliteit, ST_Transform(the_geom, 4326) from mv_quays join mv_passengerstopassignment using (quaycode) join stoppoint on (stoppoint.operator_id = dataownercode||':'||mv_passengerstopassignment.userstopcode);

create table stopplace_geom as select stopplacecode, sp.stopplacetype, sp.town, sp.publicname, ST_Transform(the_geom, 4326) from mv_stop_places as sp join (select stopplacecode, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from mv_quays join mv_passengerstopassignment using (quaycode) join stoppoint on (stoppoint.operator_id = dataownercode||':'||mv_passengerstopassignment.userstopcode) group by stopplacecode) as x using (stopplacecode);

create table place_geom as select placecode, p.publicname, p.town, ST_Transform(the_geom, 4326) from mv_places as p join (select placecode, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from mv_stop_places join mv_quays using (stopplacecode) join mv_passengerstopassignment using (quaycode) join stoppoint on (stoppoint.operator_id = dataownercode||':'||mv_passengerstopassignment.userstopcode) group by placecode) as x using (placecode);

word_style.xml

<Map background-color="transparent" srs="+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs">

<!--  <Style name="nld_adm1">
    <Rule>
      <LineSymbolizer />
      <PolygonSymbolizer fill="#00ad00" fill-opacity="0.6" />
    </Rule>
  </Style>-->
  <Style name="bearing">
    <Rule>
        <MaxScaleDenominator>30000</MaxScaleDenominator>
        <MinScaleDenominator>100</MinScaleDenominator>

      <LineSymbolizer stroke-width="2" />
    </Rule>
  </Style>

  <Style name="quay">
    <Rule>
        <MaxScaleDenominator>30000</MaxScaleDenominator>
        <MinScaleDenominator>100</MinScaleDenominator>

      <PointSymbolizer />
    </Rule>
    <Rule>
        <MaxScaleDenominator>4000</MaxScaleDenominator>
        <MinScaleDenominator>100</MinScaleDenominator>
        <TextSymbolizer face-name="DejaVu Sans Book" size="10" fill="blue" halo-fill= "white" halo-radius="1" placement-type="simple" allow-overlap="true">[quaycode]</TextSymbolizer>
    </Rule>
  </Style>
  <Style name="place">
    <Rule>
      <PolygonSymbolizer fill="#00ad00" fill-opacity="0.6" />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
    <Rule>
        <MaxScaleDenominator>30000</MaxScaleDenominator>
        <MinScaleDenominator>4000</MinScaleDenominator>
      <TextSymbolizer face-name="DejaVu Sans Book" size="10" fill="black" halo-fill= "white" halo-radius="1" placement-type="simple" allow-overlap="false">[publicname]</TextSymbolizer>
    </Rule>
    <Rule>
        <MaxScaleDenominator>4000</MaxScaleDenominator>
        <MinScaleDenominator>1000</MinScaleDenominator>
      <TextSymbolizer face-name="DejaVu Sans Book" size="10" fill="#004400" halo-fill= "white" halo-radius="1" placement-type="simple" allow-overlap="true">[placecode]</TextSymbolizer>
    </Rule>
  </Style>
  <Style name="stopplace">
    <Rule>
      <Filter>[stopplacetype] = 'onstreetBus'</Filter>
      <PolygonSymbolizer fill="#fd6000" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
     <Rule>
      <Filter>[stopplacetype] = 'combiMetroTram'</Filter>
      <PolygonSymbolizer fill="#fd00e0" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
     <Rule>
      <Filter>[stopplacetype] = 'combiTramBus'</Filter>
      <PolygonSymbolizer fill="#fde000" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
    <Rule>
      <Filter>[stopplacetype] = 'onstreetTram'</Filter>
      <PolygonSymbolizer fill="#bd0000" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
    <Rule>
      <Filter>[stopplacetype] = 'ferryPort'</Filter>
      <PolygonSymbolizer fill="#00fd00" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
    <Rule>
      <Filter>[stopplacetype] = 'metroStation'</Filter>
      <PolygonSymbolizer fill="#0000fd" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
     <Rule>
      <Filter>[stopplacetype] = 'tramStation'</Filter>
      <PolygonSymbolizer fill="#00a0fd" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
    <Rule>
      <Filter>[stopplacetype] = 'other'</Filter>
      <PolygonSymbolizer fill="#a0a0a0" fill-opacity="0.6"  />
      <LineSymbolizer stroke="#000000" stroke-width="0.1" />
    </Rule>
    <Rule>
        <MaxScaleDenominator>10000</MaxScaleDenominator>
        <MinScaleDenominator>5000</MinScaleDenominator>
      <TextSymbolizer face-name="DejaVu Sans Book" size="10" fill="black" halo-fill= "white" halo-radius="1" placement-type="simple" allow-overlap="true">[publicname]</TextSymbolizer>
    </Rule>
    <Rule>
        <MaxScaleDenominator>5000</MaxScaleDenominator>
        <MinScaleDenominator>4000</MinScaleDenominator>
      <TextSymbolizer face-name="DejaVu Sans Book" size="10" fill="black" halo-fill= "white" halo-radius="1" placement-type="simple" allow-overlap="true">[stopplacetype]</TextSymbolizer>
    </Rule>
    <Rule>
        <MaxScaleDenominator>4000</MaxScaleDenominator>
        <MinScaleDenominator>1000</MinScaleDenominator>
      <TextSymbolizer face-name="DejaVu Sans Book" size="10" fill="#990000" halo-fill= "white" halo-radius="1" placement-type="simple" allow-overlap="true">[stopplacecode]</TextSymbolizer>
    </Rule>
  </Style>

<!--  <Layer name="place" srs="+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs">
    <StyleName>place</StyleName>
    <Datasource>
      <Parameter name="type">shape</Parameter>
      <Parameter name="file">place_geom.shp</Parameter>
    </Datasource>
  </Layer>
 <Layer name="stopplace" srs="+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs">
    <StyleName>stopplace</StyleName>
    <Datasource>
      <Parameter name="type">shape</Parameter>
      <Parameter name="file">stopplace_geom.shp</Parameter>
    </Datasource>
  </Layer>
    <Layer name="quay" srs="+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs">
    <StyleName>quay</StyleName>
    <Datasource>
      <Parameter name="type">shape</Parameter>
      <Parameter name="file">quay_geom.shp</Parameter>
    </Datasource>
  </Layer>


  -->
<Layer name="place" status="on" srs="+proj=latlong +datum=WGS84">
        <StyleName>place</StyleName>
        <Datasource>
          <Parameter name="type">postgis</Parameter>
          <Parameter name="host">localhost</Parameter>
          <Parameter name="dbname">kaart</Parameter>
          <Parameter name="table">place_geom</Parameter>
        </Datasource>
    </Layer>

<Layer name="stopplace" status="on" srs="+proj=latlong +datum=WGS84">
        <StyleName>stopplace</StyleName>
        <Datasource>
          <Parameter name="type">postgis</Parameter>
          <Parameter name="host">localhost</Parameter>
          <Parameter name="dbname">kaart</Parameter>
          <Parameter name="table">(select *, (publicname || '^M' || stopplacecode) as name from stopplace_geom) as stopplace_geom</Parameter>
        </Datasource>
    </Layer>
<Layer name="quay" status="on" srs="+proj=latlong +datum=WGS84">
        <StyleName>quay</StyleName>
        <Datasource>
          <Parameter name="type">postgis</Parameter>
          <Parameter name="host">localhost</Parameter>
          <Parameter name="dbname">kaart</Parameter>
          <Parameter name="table">quay_geom</Parameter>
        </Datasource>
    </Layer>
<Layer name="bearing" status="on" srs="+proj=latlong +datum=WGS84">
        <StyleName>bearing</StyleName>
        <Datasource>
          <Parameter name="type">postgis</Parameter>
          <Parameter name="host">localhost</Parameter>
          <Parameter name="dbname">kaart</Parameter>
          <Parameter name="table">windroos_wgs84</Parameter>
        </Datasource>
    </Layer>

    <!--
<Layer name="nld_adm1" status="on" srs="+proj=latlong +datum=WGS84">
        <StyleName>nld_adm1</StyleName>
        <Datasource>
          <Parameter name="type">postgis</Parameter>
          <Parameter name="host">localhost</Parameter>
          <Parameter name="dbname">skinkie</Parameter>
          <Parameter name="table">nld_adm1</Parameter>
        </Datasource>
    </Layer>
-->


</Map>

worstcluster.sql

drop table mv_stop_places;
create table mv_stop_places as (
SELECT * FROM (SELECT DISTINCT ON (stopplacecode)
stopplacecode,
NULL::text as stopplaceownercode,
NULL::text as uic,
NULL::text as stopplacetype,
stopname as publicname,
NULL::text as publicnamemedium,
NULL::text as publicnamelong,
NULL::text as description,
NULL::text as stopplaceindication,
CASE WHEN (dataownercode = 'CXX' AND name like '%, %') THEN split_part(name,', ',1)
     ELSE town END as town,
stt_name as street,
NULL::text as location,
NULL::text as stopplacelevel,
NULL::integer as rd_x,
NULL::integer as rd_y,
NULL::integer as rd_z,
placecode
FROM quays_wouter
WHERE getingetout::boolean AND stopplacecode is not null
ORDER BY stopplacecode,(dataownercode = main_operator AND stopplacecode is not NULL) DESC,dataownercode ASC
) as x
ORDER BY town,publicname
);

drop table stopplacetypes;
create table stopplacetypes (
    modes TEXT primary key,
    stopplacetype TEXT,
    priority integer
);
insert into stopplacetypes VALUES ('ferry','ferryPort',2);
insert into stopplacetypes VALUES ('metro','metroStation',9);
insert into stopplacetypes VALUES ('bus','onstreetBus',2);
insert into stopplacetypes VALUES ('tram','onstreetTram',2);
insert into stopplacetypes VALUES ('bustram','combiTramBus',2);
insert into stopplacetypes VALUES ('busbustram','combiTramBus',2);
insert into stopplacetypes VALUES ('metrotram','combiMetroTram',9);
insert into stopplacetypes VALUES ('train','railStation',10);

update mv_stop_places msp
SET stopplacetype = coalesce(types.stopplacetype,'other')
FROM (
SELECT stopplacecode,coalesce(stopplacetype) as stopplacetype FROM (
SELECT stopplacecode,array_to_string(array_agg(transmode ORDER BY transmode),'') as modes
FROM (
 SELECT stopplacecode,unnest(array_cat(array_agg(distinct first_mode),array_agg(distinct second_mode)))::text as transmode 
 FROM quays_wouter
 GROUP BY stopplacecode
) as x
WHERE transmode IS NOT NULL
GROUP BY stopplacecode) as y LEFT JOIN stopplacetypes USING (modes)) as types
WHERE msp.stopplacecode = types.stopplacecode;

UPDATE mv_stop_places
SET stopplacetype = 'tramStation' WHERE stopplacetype = 'onstreetTram' AND stopplacecode in (
SELECT DISTINCT ON (stopplacecode) stopplacecode FROM (SELECT DISTINCT ON (quaycode) *
                                                       FROM quays_wouter
                                                       ORDER BY quaycode DESC,(dataownercode = main_operator) DESC,userstopcode,dataownercode ASC) as 
y
WHERE nullif(sidecode,'-') is not null);

COPY mv_stop_places from '/tmp/trein_stations.csv' CSV HEADER;

copy (SELECT * FROM mv_stop_places) to '/tmp/stopplaces.csv' CSV HEADER  DELIMITER '|';


drop table mv_places;
create table mv_places as ( 
SELECT DISTINCT ON (placecode)
placecode,
publicname,
town,
NULL::Text as description
FROM mv_stop_places LEFT JOIN stopplacetypes USING (stopplacetype)
WHERE placecode is not null
ORDER BY placecode ASC,(stopplacetype = 'railStation') desc,priority DESC
);
COPY (SELECT * FROM mv_places) to '/tmp/places.csv' CSV HEADER  DELIMITER '|';

drop table mv_stopplacehierarchy;
create table mv_stopplacehierarchy as ( 
SELECT DISTINCT ON (placecode,stopplacecode)
placecode,
stopplacecode
FROM mv_stop_places
ORDER BY placecode,stopplacecode
);
COPY (SELECT * FROM mv_stopplacehierarchy) to '/tmp/stopplacehierarchy.csv' CSV HEADER  DELIMITER '|';

drop table if exists ovauthority;
create table ovauthority (
  ov_authority_code TEXT,
  ov_authority text
);

INSERT INTO ovauthority VALUES ('ZLD','Provincie Zeeland');
INSERT INTO ovauthority VALUES ('SRE','Samenwerkingsverband Regio Eindhoven');
INSERT INTO ovauthority VALUES ('SGH','Stadsgewest Haaglanden');
INSERT INTO ovauthority VALUES ('SAN','Stadsregio Arnhem Nijmegen');
INSERT INTO ovauthority VALUES ('SRR','Stadsregio Arnhem Nijmegen');
INSERT INTO ovauthority VALUES ('PZH','Provincie Zuid-Holland');
INSERT INTO ovauthority VALUES ('PZH','Provincie Noord-Holland');
INSERT INTO ovauthority VALUES ('SRR','Stadsregio Rotterdam');
INSERT INTO ovauthority VALUES ('SRA','Stadsregio Amsterdam');
INSERT INTO ovauthority VALUES ('PUT','Provincie Utrecht');
INSERT INTO ovauthority VALUES ('PNB','Provincie Noord-Brabant');
INSERT INTO ovauthority VALUES ('PNH','Provincie Noord-Holland');
INSERT INTO ovauthority VALUES ('FLV','Flevoland');
INSERT INTO ovauthority VALUES ('FLV','Provincie Flevoland');
INSERT INTO ovauthority VALUES ('BRU','Bestuur Regio Utrecht');
INSERT INTO ovauthority VALUES ('LMB','Provincie Limburg');
INSERT INTO ovauthority VALUES ('DR','Provincie Drenthe');
INSERT INTO ovauthority VALUES ('OVS','Overijssel');
INSERT INTO ovauthority VALUES ('GLD','Provincie Gelderland');
INSERT INTO ovauthority VALUES ('RT','Regio Twente');
INSERT INTO ovauthority VALUES ('FR','Provincie Friesland');
INSERT INTO ovauthority VALUES ('GR','Provincie Groningen');

drop table mv_quays;
create table mv_quays as (
SELECT * FROM (
SELECT DISTINCT ON (quaycode)
ov_code as OVA,
quaycode as quaycode,
userstopcode,
NULL::text as duplicaat_haltenr,
NULL::text as haltebord_nr,
stopname as haltenaam,
NULL::text as haltenaam_corr,
nullif(sidecode,'-') as perroncode,
CASE WHEN (dataownercode = 'CXX' AND name like '%, %') THEN split_part(name,', ',1)
     ELSE town END as woonplaats,
NULL::text as woonplaats_corr,
gm_code as gemeentecode,
gm_name as gemeentenaam,
bhr_code as wegbeheerdercode,
NULL::text as wegbeheerdercode_cor,
bhr_type as wegbeheerdertype,
NULL::text as wegbeheerdertype_cor,
stt_name as straat,
NULL::text as straat_coor,
NULL::text as locatie_omschrijving,
array_agg(destinations) OVER (PARTITION BY quaycode) as lijn_bestemming,
first_mode as hoofdmodaliteit,
stopplacecode,
second_mode as tweedemodaliteit,
NULL::text as StopPlaceCodeCHB,
rd_x,
rd_y,
NULL::integer as rd_x_corr,
NULL::integer as rd_y_corr,
coalesce(windroos.median, windroos_naive_nwb_final.median) as heading,
NULL::Integer as heading_corr,
status as status,
NULL::text as status_corr,
type,
NULL::text as type_corr,
NULL::text as opnamedatum,
NULL::boolean as wheelchairaccessible,
NULL::boolean as visualaccessible,
NULL::text as perrontype,
NULL::float as diepte,
NULL::float as halteerlengte,
NULL::float as halteerplaatslengte,
NULL::float as halteerlengte_uitrijhoek,
NULL::float as perronhoogte,
NULL::float as perronbreedte,
NULL::float as perronbreedte_uitstapdeur,
NULL::float as lengte_verhoogddeel,
NULL::float as min_breedte,
NULL::boolean as hellingbaan,
NULL::integer as hellingbaan_lengte,
NULL::float as hellingbaan_breedte,
NULL::float as perron_hoogteverschil,
NULL::boolean as markering_perronrand,
NULL::boolean as geleidelijn,
NULL::boolean as geleidelijn_gehelehalte,
NULL::boolean as geleidelijn_gehelehalte_omgeving,
NULL::boolean as instapmarkering,
NULL::boolean as instapmarkering_afwijkend,
NULL::boolean as haltebord_aanwezig,
level::integer as niveau,
NULL::boolean as lift,
NULL::boolean as afvalbak,
NULL::boolean as halteverlicht,
NULL::boolean as abri,
NULL::boolean as informatievitrine,
NULL::boolean as abri_reclame,
NULL::boolean as zitplaats,
NULL::boolean as fietsparkeermogelijkheid,
NULL::integer as fietsparkeerplaatsen,
NULL::boolean as ovc_checkin,
NULL::boolean as ovc_oplaad,
NULL::boolean as statische_reisinfo,
NULL::boolean as lijnennetkaart,
NULL::boolean as dris,
null::boolean as dris_audio,
null::integer as drisregels,
NULL::Text as haltefotoref
FROM quays_wouter qwc LEFT join windroos on (replace(operator_id, ':', '|') = docusc AND windroos.stddev_pop <= 15)  LEFT JOIN windroos_naive_nwb_final ON (windroos_naive_nwb_final.operator_id = dataownercode||':'||userstopcode  AND windroos_naive_nwb_final.stddev_pop <= 15)
WHERE getingetout::boolean
ORDER BY quaycode DESC,(dataownercode = main_operator) DESC,userstopcode,dataownercode ASC
) as x
ORDER BY quaycode
);
copy (SELECT * From MV_quays) to '/tmp/quays.csv' CSV HEADER DELIMITER '|';


copy (
SELECT * FROM (
SELECT DISTINCT ON (quaycode)
ov_code as OVA,
quaycode as quaycode,
userstopcode,
NULL::text as duplicaat_haltenr,
NULL::text as haltebord_nr,
stopname as haltenaam,
NULL::text as haltenaam_corr,
nullif(sidecode,'-') as perroncode,
CASE WHEN (dataownercode = 'CXX' AND name like '%, %') THEN split_part(name,', ',1)
     ELSE town END as woonplaats,
NULL::text as woonplaats_corr,
gm_code as gemeentecode,
gm_name as gemeentenaam,
bhr_code as wegbeheerdercode,
NULL::text as wegbeheerdercode_cor,
bhr_type as wegbeheerdertype,
NULL::text as wegbeheerdertype_cor,
stt_name as straat,
NULL::text as straat_coor,
NULL::text as locatie_omschrijving,
array_agg(destinations) OVER (PARTITION BY quaycode) as lijn_bestemming,
first_mode as hoofdmodaliteit,
stopplacecode,
second_mode as tweedemodaliteit,
NULL::text as StopPlaceCodeCHB,
rd_x,
rd_y,
NULL::integer as rd_x,
NULL::integer as rd_y,
coalesce(windroos.median, windroos_naive_nwb_final.median) as heading,
NULL::Integer as heading_corr,
status as status,
NULL::text as status_corr,
type,
NULL::text as type_corr,
NULL::boolean as onlygetout,
concessioncode
FROM quays_wouter qwc LEFT join windroos on (replace(operator_id, ':', '|') = docusc AND windroos.stddev_pop <= 15)  LEFT JOIN windroos_naive_nwb_final ON (windroos_naive_nwb_final.operator_id = dataownercode||':'||userstopcode  AND windroos_naive_nwb_final.stddev_pop <= 15)
WHERE getingetout::boolean
ORDER BY quaycode DESC,(dataownercode = main_operator) DESC,userstopcode,dataownercode ASC
) as x
ORDER BY quaycode
) to '/tmp/quaysbase.csv' CSV HEADER DELIMITER '|';

drop table mv_stopplacepassengergroup;
create table mv_stopplacepassengergroup as ( 
SELECT DISTINCT ON (stopplacecode,quaycode)
StopPlaceCode,
quaycode
FROM quays_wouter
WHERE getingetout::boolean
ORDER BY stopplacecode,quaycode DESC,(dataownercode = main_operator) DESC,userstopcode,dataownercode ASC
);
copy (SELECT * FROM mv_stopplacepassengergroup)  to '/tmp/stopplacepassengergroup.csv' CSV HEADER  DELIMITER '|';

drop table mv_passengerstopassignment;
create table mv_passengerstopassignment as (
SELECT DISTINCT ON (dataownercode,userstopcode)
dataownercode,
CASE WHEN (dataownercode = 'GVB') THEN lpad(userstopcode,5,'0') ELSE userstopcode END as userstopcode,
quaycode,destinations
FROM quays_wouter
WHERE getingetout::boolean
ORDER BY dataownercode,userstopcode
);
copy (SELECT * FROM mv_passengerstopassignment) to '/tmp/passengerstopassignment.csv' CSV HEADER DELIMITER '|';

worstenfabriek.sql

create table windroos_shapes as select routeref, geom, operator_id from (SELECT routeref,st_makeline(array_agg(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) ORDER BY pointorder )) as geom FROM pointinroute WHERE privatecode is null AND latitude > 50.7 GROUP BY routeref) as x join journeypattern using (routeref);

create table windroos_shapes_naive as select journeypatternref, geom from (SELECT journeypatternref,st_makeline(array_agg(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) ORDER BY pointorder )) as geom FROM pointinjourneypattern JOIN stoppoint ON (pointref = stoppoint.id) GROUP BY journeypatternref) as x;


select ST_ClosestPoint(sc.geom, sp.the_geom), ST_LineInterpolatePoint(sc.geom, ST_LineLocatePoint(sc.geom, ST_ClosestPoint(sc.geom, sp.the_geom)) + 0.000001)  from pointinjourneypattern join stoppoint as sp ON  (pointref = sp.id) join windroos_shapes_naive as sc USING (journeypatternref) limit 10;


where ST_LineLocatePoint(windroos_shapes_naive.geom, ST_ClosestPoint(stoppoint.the_geom, windroos_shapes_naive.geom))

create table test_windroos as select name, operator_id, bearing/(2*pi())*360::int as bearing, ST_MakeLine(the_geom, ST_Translate( the_geom, sin(bearing)*0.0004, cos(bearing)*0.0004)) as the_geom from (select sp.name, sp.operator_id, sp.the_geom, ST_Azimuth(ST_ClosestPoint(sc.geom, sp.the_geom), ST_LineInterpolatePoint(sc.geom, ST_LineLocatePoint(sc.geom, ST_ClosestPoint(sc.geom, sp.the_geom)) + 0.000001)) as bearing from journeypattern as jp join pointinjourneypattern as pj on (pj.journeypatternref = jp.id) join windroos_shapes as sc using (routeref) join stoppoint as sp on (pj.pointref = sp.id) where split_part(sc.operator_id,':',1) in ('CXX','HTM','RET','GVB','QBUZZ','ARR','SYNTUS') AND ST_NumPoints(sc.geom) > 2 and ST_LineLocatePoint(sc.geom, ST_ClosestPoint(sp.the_geom, sc.geom)) between 0 and 0.99999) as x;

CREATE FUNCTION _final_median(anyarray) RETURNS float8 AS $$
  WITH q AS
  (
     SELECT val
     FROM unnest($1) val
     WHERE VAL IS NOT NULL
     ORDER BY 1
  ),
  cnt AS
  (
    SELECT COUNT(*) AS c FROM q
  )
  SELECT AVG(val)::float8
  FROM
  (
    SELECT val FROM q
    LIMIT  2 - MOD((SELECT c FROM cnt), 2)
    OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)
  ) q2;
$$ LANGUAGE sql IMMUTABLE;

CREATE AGGREGATE median(anyelement) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=_final_median,
  INITCOND='{}'
);

create table windroos_naive_nwb2 as select operator_id, bearing, ST_MakeLine(a, ST_Translate( a, sin(radians(bearing))*10, cos(radians(bearing))*10)) as the_geom from (select distinct operator_id, (bearing/(2*pi())*360) as bearing, a as a from (select operator_id, ST_Azimuth(ST_ClosestPoint(geom, a), ST_ClosestPoint(geom, b)) as bearing, a, geom from half join nwb.wegvlakken using (gid) ) as z) as y;

create table windroos_naive_nwb_final as select operator_id, median(bearing)::int, stddev_pop(bearing)::int  from windroos_naive_nwb2 where bearing is not null group by operator_id;

create view windroos_wgs84 as select operator_id, bearing, st_transform(the_geom, 4326) from windroos_naive_nwb2 ;