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

GIS Analyst, Intetics Geo

NP@MApillary 2015

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

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

 Объекты ПЗФ:

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

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

NP@MApillary 2015

Mapillary is a service for crowdsourcing street levels photos.

Global community - Global coverage

Global community - Global coverage

43,010,186  photos

1,063,120  kilometers

GPS: Garmin eTrex 30 | Camera: GoPro HERO 2

License

The images on Mapillary can by used under Creative Commons Attribution-ShareAlike 4.0 International License (CC-BY-SA). There is special permission to derive data from the photos for contributing to OpenStreetMap and Wikimedia Commons. The GPX tracks can be used without restriction, and derived data can be used provided it is ODbL.

ЗАДАЧА

  • Получить статистику собранных геотегированных фотографий в пределах объекта ПЗФ для каждого пользователя
  • Отобразить на web-карте объекты ПЗФ и треки собранных геотегированных фотографий
Mapillary user name Protected Area Name Photo Count
hast Регіональний ландшафтний парк «Сокольники-Помірки» 1717
prudenko Національний природний парк «Голосіївський» 60
cartolab Національний природний парк «Слобожанський» 34

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

Репозиторий 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/

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

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

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

protected AREAS boundary

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
"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-страницы

СОЗДАНИЕ 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

СОЗДАНИЕ 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);
};

результат

ВОПРОСЫ?

GIS Analyst, Intetics Geo