Mapfeatures i PostgreSQL

Download data

Indlæs i PostgreSQL

Danske vejskilte navne

Mapillarys trafiknavne er på engelsk. For at gøre brugen af trafikskilte datasættet lettere er der påbegyndt en dansk oversættelse samt den officielle danske tavlekode betegnelse. Eksempel 'regulatory--keep-right-g1' er på dansk 'Påbudt passage højre om D 15'. Oversættelsen kan hentes i denne her CSV fil- Oversættelsen er ikke komplet endnu.
Når oversættelse samt Mapillary trafikdatasæt skal merges i PostgreSQL skal der laves et VIEW i stil med

-- VIEW af tavlekode oversættelsen og Mapillary rå GeoJSON
CREATE OR REPLACE VIEW proj_mapillary.trafikskilte_med_tavlekode AS
SELECT gid, value, dansk_tavlekode, tavlekode_kategori, tavlekode, first_seen, last_seen, direction, n_img_det, uniq_users, image_keys, geom 
FROM proj_mapillary.trafikskilte_ballerup_2019_11_01, proj_mapillary.mapillary_tavlekode
 WHERE  proj_mapillary.trafikskilte_ballerup_2019_11_01.value=proj_mapillary.mapillary_tavlekode.mapillary_api_name
-- Vælg i SELECT gid kolonne først så undgår man QGIS problem med unik-nøgle for et VIEW i DB-manager

Hvis man ønsker at få ugedag, måned og år med som kolonner, så trækkes disse fra timestamp i Mapillary data fx '2018-11-02 14:50:51' - Så kan et VIEW laves således

-- Hvis man ønsker at få ugedage, måneder og år med i sit VIEW. Disse værdier dannes fra Mapillarys timestamp data som bruger ISO 8601 for dato og tid fx '2018-11-02 14:50:51'
CREATE OR REPLACE VIEW proj_mapillary.trafikskilte_med_tavlekode AS
SELECT gid, value, dansk_tavlekode, tavlekode_kategori, tavlekode, first_seen, last_seen, direction, n_img_det, uniq_users, image_keys, geom,
to_char(first_seen, 'Day') AS first_seen_ugedag, to_char(first_seen, 'Month') AS first_seen_maaned, EXTRACT(YEAR FROM first_seen) AS first_seen_aar,
to_char(last_seen, 'Day') AS last_seen_ugedag, to_char(last_seen, 'Month') AS last_seen_maaned, EXTRACT(YEAR FROM last_seen) AS last_seen_aar 
FROM proj_mapillary.trafikskilte_ballerup_2019_12_04, proj_mapillary.mapillary_tavlekode
 WHERE  proj_mapillary.trafikskilte_ballerup_2019_12_04.value=proj_mapillary.mapillary_tavlekode.mapillary_api_name; 
</syntaxhighlight>

SQL sandkasse for trafikskilte

Eksempler på SQL søgninger med Mapillary trafikskiltedata fra Ballerup og forespørgsler i andre datasæt.

-- Hvilke tavlekode kategorier er der flest trafikskilte i fx antal forbudstavler i alt
SELECT tavlekode_kategori, COUNT(tavlekode_kategori)
FROM proj_mapillary.trafikskilte_med_tavlekode
GROUP BY tavlekode_kategori
ORDER BY COUNT(tavlekode_kategori) DESC;

-- Hvilken slags trafikskilte er der flest af fx hvor mange 'A 39 Vejarbejde' er der
SELECT dansk_tavlekode, COUNT(dansk_tavlekode), tavlekode, tavlekode_kategori, value
FROM proj_mapillary.trafikskilte_med_tavlekode
GROUP BY dansk_tavlekode, tavlekode_kategori, tavlekode, value
ORDER BY COUNT(dansk_tavlekode) DESC;

-- Hvilke trafikskilte er der flest af i postnummer 2760 Måløv
SELECT dansk_tavlekode, COUNT(dansk_tavlekode), tavlekode, tavlekode_kategori
FROM proj_mapillary.trafikskilte_med_tavlekode, _00_grundkort._00_02_postnummer
WHERE ST_Within (proj_mapillary.trafikskilte_med_tavlekode.geom,  _00_grundkort._00_02_postnummer.the_geom) AND postnr_fra = '2760'
GROUP BY dansk_tavlekode, tavlekode_kategori, tavlekode
ORDER BY COUNT(dansk_tavlekode) DESC;

-- Vis alle advarselstavler i postnummer 2740 Skovlunde
SELECT dansk_tavlekode, tavlekode, tavlekode_kategori
FROM proj_mapillary.trafikskilte_med_tavlekode, _00_grundkort._00_02_postnummer
WHERE ST_Within (proj_mapillary.trafikskilte_med_tavlekode.geom,  _00_grundkort._00_02_postnummer.the_geom) AND postnr_fra = '2740' AND tavlekode_kategori = 'advarselstavle'
ORDER BY tavlekode ASC;

-- Vis alle forbudstavler i Pederstrup Sogn
SELECT dansk_tavlekode, tavlekode, tavlekode_kategori
FROM proj_mapillary.trafikskilte_med_tavlekode, _00_grundkort._00_02_sogn
WHERE ST_Within (proj_mapillary.trafikskilte_med_tavlekode.geom,  _00_grundkort._00_02_sogn.the_geom) AND sognekode = '9089' AND tavlekode_kategori = 'forbudstavle'
ORDER BY tavlekode ASC;

-- Hvor mange trafikskilte er der per km2 i Ballerup Kommune
SELECT COUNT(value) / 34.09 AS antal_skilte_per_km2 
FROM proj_mapillary.trafikskilte_med_tavlekode;

-- Vis top 20 af trafikskilte som Mapillarys algoritmer har fanget objektet flest gange i mht antal billeder
SELECT dansk_tavlekode, n_img_det
FROM proj_mapillary.trafikskilte_med_tavlekode
ORDER BY n_img_det DESC
LIMIT 20;
-- Hvilken ugedag har algoritmerne fanget flest trafikskilte første gang
SELECT first_seen_ugedag AS ugedag, COUNT(first_seen_ugedag) AS antal_skilte
FROM proj_mapillary.trafikskilte_med_tavlekode
GROUP BY first_seen_ugedag 
ORDER BY COUNT(first_seen_ugedag) DESC;

-- Hvilken ugedag har algoritmerne fanget flest trafikskilte sidste gang
SELECT last_seen_ugedag AS ugedag, COUNT(last_seen_ugedag) AS antal_skilte
FROM proj_mapillary.trafikskilte_med_tavlekode
GROUP BY last_seen_ugedag 
ORDER BY COUNT(last_seen_ugedag) DESC;

-- Hvilken måned har algoritmerne fanget flest trafikskilte første gang
SELECT first_seen_maaned AS maaned, COUNT(first_seen_maaned) AS antal_skilte
FROM proj_mapillary.trafikskilte_med_tavlekode
GROUP BY first_seen_maaned 
ORDER BY COUNT(first_seen_maaned) DESC;

-- Hvilken måned har algoritmerne fanget flest trafikskilte sidste gang
SELECT last_seen_maaned AS maaned, COUNT(last_seen_maaned) AS antal_skilte
FROM proj_mapillary.trafikskilte_med_tavlekode
GROUP BY last_seen_maaned 
ORDER BY COUNT(last_seen_maaned) DESC;


-- Vis top 50 trafikskilte som har længst antal dage mellem det først blev set og så sidste gang det blev set
SELECT dansk_tavlekode, tavlekode, last_seen - first_seen AS dage, n_img_det, first_seen, last_seen
FROM proj_mapillary.trafikskilte_med_tavlekode
ORDER BY dage DESC
LIMIT 50;

-- Vis hvilke A 39 vejarbejde skilte som der har stået længst tid - OBS kan være vejarbejde af flere perioder og ikke kun en
SELECT dansk_tavlekode,tavlekode, last_seen - first_seen AS dage, first_seen, last_seen, image_keys, n_img_det
FROM proj_mapillary.trafikskilte_med_tavlekode 
WHERE tavlekode = 'A 39'
ORDER BY  dage  DESC;

-- Vis hvilke trafikskilte der første gang blev set i 2016 af Mapillarys algoritmer
SELECT dansk_tavlekode, tavlekode, first_seen, last_seen
FROM proj_mapillary.trafikskilte_med_tavlekode
WHERE first_seen > '2015-12-31' AND first_seen < '2017-01-01'
ORDER BY first_seen ASC;

-- Hvilke tavlekategorier blev der spottet flest af første gang i 2017
SELECT tavlekode_kategori, COUNT(tavlekode_kategori)
FROM proj_mapillary.trafikskilte_med_tavlekode
WHERE first_seen > '2016-12-31' AND first_seen < '2018-01-01'
GROUP BY tavlekode_kategori
ORDER BY COUNT(tavlekode_kategori) DESC;

-- Hvilke trafikskilte er først set i maj 2018 eller sidst set i maj 2018 i postnummer 2760 Måløv
SELECT dansk_tavlekode,  tavlekode, first_seen, last_seen
FROM proj_mapillary.trafikskilte_med_tavlekode, _00_grundkort._00_02_postnummer
WHERE (first_seen > '2018-04-30' AND first_seen < '2018-06-01' OR last_seen > '2018-04-30' AND last_seen < '2018-06-01') AND ST_Within(proj_mapillary.trafikskilte_med_tavlekode.geom,  _00_grundkort._00_02_postnummer.the_geom) AND postnr_fra = '2760'
ORDER BY first_seen ASC;


-- Vis hvilke nye trafikskilte som Mapillarys algoritmer har fundet efter 29. juni 2019 og hvor Mapillary bruger neogeografen har taget billederne
SELECT value, dansk_tavlekode, tavlekode, first_seen, last_seen,tavlekode_kategori, uniq_users  
FROM proj_mapillary.trafikskilte_med_tavlekode 
WHERE first_seen > '2019-06-29' AND uniq_users ILIKE '%TXDkLFIbQaZqoj_1TQ07gw%'
ORDER BY first_seen ASC;

-- trafikskilte fundet efter 29. juni 2019 og ordnet efter gruppeoptælling og Mapillary bruger neogeografen
SELECT dansk_tavlekode, COUNT(dansk_tavlekode), tavlekode, tavlekode_kategori
FROM proj_mapillary.trafikskilte_med_tavlekode
WHERE first_seen > '2019-06-29' AND uniq_users ILIKE '%TXDkLFIbQaZqoj_1TQ07gw%'
GROUP BY dansk_tavlekode, tavlekode_kategori, tavlekode, value
ORDER BY COUNT(dansk_tavlekode) DESC;

-- Vis hvilke trafiksskilte som er blevet set før 30. juni 2019 som er blevet genset i Mapillary billeder taget efter 29. juni 2019 og hvor Mapillary bruger neogeografen har taget billederne.
SELECT value, dansk_tavlekode, tavlekode, last_seen - first_seen AS dage, last_seen, first_seen, n_img_det, uniq_users, tavlekode_kategori 
FROM proj_mapillary.trafikskilte_med_tavlekode
WHERE first_seen < '2019-06-30' AND last_seen > '2019-06-29' AND uniq_users ILIKE '%TXDkLFIbQaZqoj_1TQ07gw%'
ORDER BY dage DESC; 

Alle ovenstående SQL søgninger kan afvikles i QGIS DB-styring i SQL vinduet.