Разработка веб-картографического приложения для конкурса NP@Mapillary 2015

Александр Клешнин

GIS Analyst, Intetics Geo

NP@MApillary 2015

Конкурс по сбору геотегированных фотографий в объектах природно-заповедного фонда (ПЗФ) Украины

Продолжительность конкурса: 15.09.2015 - 15.12.2015

Wiki OSM: http://wiki.openstreetmap.org/wiki/Uk:NP@Mapillary_2015

OSM Forum: http://forum.openstreetmap.org/viewtopic.php?id=40906

 Объекты ПЗФ:

• Национальные природные парки
• Региональные ландшафтные парки
• Ботанические сады
• Дендрологические парки
• Зоологические парки
• Парки-памятники садово-паркового искусства

Подробно о конкурсе:

NP@MApillary 2015

Wiki OSM: http://wiki.openstreetmap.org/wiki/Uk:NP@Mapillary_2015

OSM Forum: http://forum.openstreetmap.org/viewtopic.php?id=40906

ЗАДАЧА

• Получить статистику собранных геотегированных фотографий в пределах объекта ПЗФ для каждого пользователя
• Отобразить на web-карте объекты ПЗФ и треки собранных геотегированных фотографий

http://www.mapillary.com/map/places/85633805

материалы проекта

Репозиторий GitHub:

https://github.com/spatialhast/np.mapillary.tools - data processing tools

https://github.com/spatialhast/np.mapillary - web-map

ИСПОЛЬЗУЕМОЕ ПО/БИБЛИОТЕКИ/ТЕХНОЛОГИИ

QGIS, PostgreSQL/PostGIS, Leaflet, Tangram, Mapillary API, imposm3, ogr2ogr, Python, Shell...

исходные данные

Mapillary Geotagged Photos

Protected Areas Boundary

• coordinates
• user name
• date capture

• PA name

GeoTAGGED PHOTOS Processing

Mapillary API https://a.mapillary.com/

Python Tools for Mapillary:

https://github.com/mapillary/mapillary_tools/tree/master/python

https://github.com/dalacost/mapillary_tools/tree/master/python

download_gpx_from_sequences.py
this scripts is a fast way to download GPS traces from the sequences uploaded to Mapillary. Download traces inside a rect (min_lat, max_lat, min_lon, max_lon).

join_gpx_mapillary_files.py
a fast way to join a lot of GPX files downloaded from Mapillary if you use download_gpx_from_sequences.py, this is your second step for join all in just one file.

python download_gpx_from_sequences.py [-h]
                                      [-r min_lat max_lat min_lon max_long]
                                      [-m MAXRESULTS] [-u USERNAME]
                                      [-d1 STARTDATE] [-d2 ENDDATE]

python join_gpx_mapillary_files.py [gpx_directory] [outputfile.gpx]

GeoTAGGED PHOTOS Processing

Get list of users in country:

https://a.mapillary.com/v2/stats/toplist?client_id=CLIENT_ID&cname=ukraine&limit=100

for username in alex7 algot alkarol andygol b108 baditaflorin cartolab cut dmbreaker durko_freemap edjone ghostishev gwin hast ikovaltaras imsamurai ivic4u jan_mapper maxim75 older prudenko sanjak serge serhijdubyk severyndubyk urbalazs velmyshanovnyi vsviridov wiktorn yamaxim yevgeniy8 zvid
#for username in hast prudenko severyndubyk
do
	MAPILLARYUSER=$username
	
	echo -e "\e[32mHello "$MAPILLARYUSER"!\e[0m"

	# initialize folders
	rm -R _gpx
	rm -R _merge_gpx
	mkdir _merge_gpx

	echo -e "\e[32m"$MAPILLARYUSER", I'm start download your GPS traces\e[0m"
	python mapillary_tools/download_gpx_from_sequences.py -r ${BBOX} -m ${MAXRESULTS} -u ${MAPILLARYUSER}

	echo -e "\e[32m"$MAPILLARYUSER", I'm start union your GPS traces\e[0m"	
	python mapillary_tools/join_gpx_mapillary_files.py _gpx _merge_gpx/${MAPILLARYUSER}.gpx

	echo -e "\e[32m"$MAPILLARYUSER", I'm start import your GPS traces in database\e[0m"
	ogr2ogr  -overwrite -s_srs "+init=epsg:4326" -t_srs "+init=epsg:4326" -f "PostgreSQL" PG:"host=$HOST user=$USER dbname=$DBNAME password=$PASSWORD" _merge_gpx/${MAPILLARYUSER}.gpx -nln gpx_user_${MAPILLARYUSER}

	psql -h $HOST -p $PORT -d $DBNAME -U $USER -c "ALTER TABLE gpx_user_${MAPILLARYUSER} DROP COLUMN track_fid,DROP COLUMN track_seg_id, 
		DROP COLUMN track_seg_point_id, DROP COLUMN ele, DROP COLUMN magvar, DROP COLUMN geoidheight, DROP COLUMN cmt, DROP COLUMN src, 
		DROP COLUMN link1_href, DROP COLUMN link1_text, DROP COLUMN link1_type, DROP COLUMN link2_href, DROP COLUMN link2_text,
		DROP COLUMN link2_type, DROP COLUMN sym, DROP COLUMN type, DROP COLUMN fix, DROP COLUMN sat, DROP COLUMN hdop, DROP COLUMN vdop,
		DROP COLUMN pdop, DROP COLUMN ageofdgpsdata, DROP COLUMN dgpsid;"
	  
	psql -h $HOST -p $PORT -d $DBNAME -U $USER -c "UPDATE gpx_user_${MAPILLARYUSER} SET name = '${MAPILLARYUSER}'"

	echo ""
done

Import GPX sequences in PostGIS database:

GeoTAGGED PHOTOS Processing

--UNION ALL IMPORTED 'gpx_user_%USERNAME%' TABLES IN 'union_gpx_data' TABLE
SELECT maskunion('public', 'gpx_user_', 'union_gpx_data');
--
--
--'union_gpx_data': RENAME 'ogc_fid', 'wkb_geometry', 'time' FIELDS. DELETE 'desc' FIELD. CHANGE DATA TYPE FOR 'gpxtime' FIELD
ALTER TABLE "union_gpx_data" RENAME ogc_fid TO id; 
ALTER TABLE "union_gpx_data" RENAME wkb_geometry TO geom; 
ALTER TABLE "union_gpx_data" RENAME "time" TO gpxtime;
ALTER TABLE "union_gpx_data" DROP COLUMN "desc";
ALTER TABLE "union_gpx_data" ALTER COLUMN gpxtime TYPE date USING gpxtime::date;

Create and format 'union_gpx_data' table:

protected AREAS boundary

http://opengeo.intetics.com.ua/osm/pa/

http://gis-lab.info/qa/osmshp.html

Данные OpenStreetMap в формате shape-файлов:

protected AREAS boundary

OpenStreetMap data for Ukraine

http://download.geofabrik.de/

wget -O ${HOME}/osmpa/osmdata/ukraine-latest.osm.pbf "http://download.geofabrik.de/europe/ukraine-latest.osm.pbf" ${HOME}/osmpa/osmdata

protected AREAS boundary

imposm3 import -read ukraine-latest.osm.pbf -write -cachedir cache -connection "postgis://$USER:$PASSWORD@$HOST:5432/$DBNAME?sslmode=disable&prefix=NONE" -dbschema-import public -mapping mapping.json -diff -srid 4326 -overwritecache

Imposm3

http://imposm.org/

https://github.com/omniscale/imposm3

"nature_conservation_polygon": {
    "fields": [{
        "type": "geometry",
        "name": "geom",
        "key": null
    }, 
    {
        ...
    },
    {
        "type": "string",
        "name": "name",
        "key": "name"
    }],
    "type": "polygon",
    "mapping": {
        "boundary": ["protected_area", "national_park"]
    }
}

CREATE OR REPLACE FUNCTION maskunion(IN _schema TEXT, IN _parttionbase TEXT, TEXT)
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || $3;
    EXECUTE 'CREATE TABLE ' || $3 || '
        (
          ogc_fid integer,
          wkb_geometry geometry(Point,4326),
          "time" timestamp with time zone,
          name character varying,
          "desc" character varying
        )';
    FOR row IN
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'INSERT INTO ' || $3 || ' SELECT * FROM ' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

--SELECT maskunion('public', 'gpx_user_', 'union_gpx_data');

POSTGIS DATA PROCESSING

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT)
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;
 
--SELECT footgun('public', 'gpx_user_');

POSTGIS DATA PROCESSING

CREATE OR REPLACE FUNCTION utmzone(geometry)
   RETURNS integer AS
 $BODY$
 DECLARE
     geomgeog geometry;
     zone int;
     pref int;

 BEGIN
     geomgeog:= ST_Transform($1,4326);

     IF (ST_Y(geomgeog))>0 THEN
        pref:=32600;
     ELSE
        pref:=32700;
     END IF;

     zone:=floor((ST_X(geomgeog)+180)/6)+1;

     RETURN zone+pref;
 END;
 $BODY$ LANGUAGE 'plpgsql' IMMUTABLE
   COST 100;

POSTGIS DATA PROCESSING

--UNION ALL IMPORTED 'gpx_user_%USERNAME%' TABLES IN 'union_gpx_data' TABLE
SELECT maskunion('public', 'gpx_user_', 'union_gpx_data');
--
--
--'union_gpx_data': RENAME 'ogc_fid', 'wkb_geometry', 'time' FIELDS. DELETE 'desc' FIELD. CHANGE DATA TYPE FOR 'gpxtime' FIELD
ALTER TABLE "union_gpx_data" RENAME ogc_fid TO id; 
ALTER TABLE "union_gpx_data" RENAME wkb_geometry TO geom; 
ALTER TABLE "union_gpx_data" RENAME "time" TO gpxtime;
ALTER TABLE "union_gpx_data" DROP COLUMN "desc";
ALTER TABLE "union_gpx_data" ALTER COLUMN gpxtime TYPE date USING gpxtime::date;

-- CREATE 'ncp_buffer' TABLE WITH 20m GEOM BUFFER FROM 'nature_conservation_polygon' TABLE
DROP TABLE IF EXISTS ncp_buffer;
CREATE TABLE ncp_buffer AS SELECT a.name, a.boundary, ST_Buffer( ST_Transform(a.geom, utmzone(ST_Centroid(a.geom))), 20) AS geom FROM nature_conservation_polygon a;
ALTER TABLE ncp_buffer ALTER COLUMN geom TYPE Geometry(Geometry,4326) USING ST_Transform(geom,4326);

POSTGIS DATA PROCESSING

POSTGIS DATA PROCESSING

--CREATE 'data_first_time' TABLE WITH 'gpxtime' BETWEEN '2015-09-15 - 2015-12-15'
DROP TABLE IF EXISTS data_first_time;
CREATE TABLE data_first_time AS SELECT * FROM union_gpx_data WHERE gpxtime BETWEEN '2015-09-15 00:00:00'::timestamp AND '2015-12-15 23:59:59'::timestamp ORDER BY id DESC;
--
--CREATE 'data_first_in_np' WITH POINTS IN NP 
DROP TABLE IF EXISTS data_first_in_np;
CREATE TABLE data_first_in_np AS SELECT a.* FROM data_first_time a, ncp_buffer b WHERE ST_Intersects(a.geom, b.geom);
--
--ADD NP NAMES TO POINTS
ALTER TABLE data_first_in_np ADD COLUMN paname character varying; 
UPDATE data_first_in_np points SET paname = p.name 
FROM ncp_buffer p WHERE ST_Contains(p.geom, points.geom);
--
--CREATE TABLE WITH STATISTICS
DROP TABLE IF EXISTS table_count_first;
CREATE TABLE table_count_first AS SELECT name, paname, COUNT(*) FROM data_first_in_np GROUP BY name, paname ORDER BY count;
--
--CREATE JSON DATA
COPY (SELECT array_to_json(array_agg(row_to_json(t))) FROM (SELECT  name, array_agg(paname) AS paname, SUM(count) AS count FROM table_count_first GROUP BY name ORDER BY count DESC) t) to '/home/hast/np.mapillary.tools/data/table_count_first.json';
--COPY (SELECT array_to_json(array_agg(row_to_json(t))) FROM (SELECT  name, array_agg(paname) AS paname, SUM(count) AS count FROM table_count_first GROUP BY name ORDER BY count DESC) t) to 'D:\np.mapillary.tools\data\table_count_first.json';
--
SELECT footgun('public', 'gpx_user_');

DATA FOR Web application

ogr2ogr -nlt POLYGON -f "GeoJSON" data/nature_conservation_polygon.geojson PG:"host=$HOST user=$USER dbname=$DBNAME password=$PASSWORD" nature_conservation_polygon -simplify 0.001 -lco COORDINATE_PRECISION=5

• nature_conservation_polygon.geojson
• table_count_first.json
• table_count_second.json

СОЗДАНИЕ web-страницы

http://leafletjs.com/

СОЗДАНИЕ web-страницы

Mapillary Vector Tiles

https://d2munx5tg0hw47.cloudfront.net/tiles/{z}/{x}/{y}.mapbox

СОЗДАНИЕ web-страницы

<!--CSS-->
<link rel="stylesheet" href="assets/leaflet-0.7.7/leaflet.css" />
<link rel="stylesheet" href="assets/bootstrap-3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="assets/bootstrap-table/bootstrap-table.min.css">	
<link rel="stylesheet" href="assets/font-awesome-4.2.0/css/font-awesome.min.css">	

<!--JavaScript-->
<script src="assets/jquery-1.11.3.min.js"></script>
<script src="assets/leaflet-0.7.7/leaflet.js"></script>
<script src="assets/bootstrap-3.3.5/js/bootstrap.min.js"></script>
<script src="assets/bootstrap-table/bootstrap-table.min.js"></script>
<script src="assets/leaflet.easybutton/easy-button.js"></script>	
<script src="assets/leaflet-hash.js"></script>	
<script src="assets/tangram.min.js"></script>	

СОЗДАНИЕ web-страницы

Tangram is a JavaScript library for rendering 2D & 3D maps live in a web browser with WebGL.

Tangram

https://github.com/tangrams/tangram

СОЗДАНИЕ web-страницы

var layerOSM = L.tileLayer('http://{s}.tiles.mapbox.com/v4/mapbox.light/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoibWFwYm94IiwiYSI6IlhHVkZmaW8ifQ.hAMX5hSW-QnTeRCMAy9A8Q', {
	attribution: 'Map style © <a href="https://www.mapbox.com/about/maps/">MapBox</a>; Map data © <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
});
var map = new L.Map('map', {
	layers: [layerOSM],
	minZoom: 6,
	maxZoom: 15,
	zoom: 7, 
	center: [48.6, 32.2],
	maxBounds: new L.LatLngBounds(new L.LatLng(43.9655, 12.4158), new L.LatLng(53.0720, 52.0862))
});

var nature_conservation_polygon = L.geoJson(null, {
	style: function(feature) {
		if (feature.properties.name === null) {
			return {
				color: "#ff4c4c",
				fill: true,
				opacity: 0.7,
				weight: 2
			};
		} else
		return {
			color: "#9ace00",
			fill: true,
			opacity: 0.7,
			weight: 2
		};
	},
	onEachFeature: function(feature, layer_nature_conservation_polygon) {
		layer_nature_conservation_polygon.on({
			dblclick: function(e) {
				var url = 'http://www.openstreetmap.org/' + (feature.properties.osm_id < 0 ? 'relation/' + feature.properties.osm_id * (-1) : 'way/' + feature.properties.osm_id);
				window.open(url);
			}
		});
	}
});
$.getJSON("data/nature_conservation_polygon.geojson", function(data) {
	nature_conservation_polygon.addData(data);
});	
nature_conservation_polygon.addTo(map);	

СОЗДАНИЕ web-страницы

var layer = Tangram.leafletLayer({ scene: 'assets/scene.yaml' });
layer.addTo(map);

cameras:
    camera1:
        type: perspective
        vanishing_point: [-.25, -.75]
    camera2:
        type: isometric
        axis: [.0, 1.]
        active: true
lights:
    light1:
        type: directional
        diffuse: 1
        ambient: .35

sources:
    mapillary:
        type: MVT
        url:  https://d2munx5tg0hw47.cloudfront.net/tiles/{z}/{x}/{y}.mapbox
        max_zoom: 15
layers:
     mapillary-sequences:
        data: { source: mapillary }
        filter: |
            function() {
                return (
                    properties.min &&
                    properties.max &&
                    feature.captured_at > properties.min &&
                    feature.captured_at < properties.max
                );
            }
        properties: 
            key_text: ""
            value_text: ""
            newest: '#00ff00'
            oldest: '#0000ff'
            min: 1442264400000
            max: 1469183337901
        draw:
            lines:
                interactive: true
                order: 99
                color: 'rgba(253,154,0,0.5)'
                width: 3px
            text:
                order: 100
                text_source: username
                font:
                    typeface: 8pt Helvetica
                    fill: 'rgba(17,1,150,0.6)'
                    stroke: { color: white, width: 2 }

scene.yaml

СОЗДАНИЕ web-страницы

<table data-toggle="table" data-url="data/table_count_first.json" data-cache="false" data-height="auto">
    <thead>
        <tr>
            <th data-field="place" data-formatter="placeNumber">Місце</th>
            <th data-field="name" data-formatter="linkUserName">Mapillary профіль</th>
            <th data-field="paname" data-formatter="panameReplace">Об'єкти ПЗФ</th>
            <th data-field="count">Кількість фото</th>
        </tr>
    </thead>
</table>

function placeNumber(value, row, index) {
	return 1 + index;
};

function linkUserName(value, row) {
	return '<a target="_blank" href="https://www.mapillary.com/profile/' + value + '">' + value + '</a>';
};

function panameReplace(value, row, index) {
	return JSON.stringify(value).replace(/","/g, ', ').slice(2, -2);
};

результат

http://spatialhast.github.io/np.mapillary/

ВОПРОСЫ?

Александр Клешнин

GIS Analyst, Intetics Geo

kleshnin.alexander@gmail.com