Search for ski slopes from space
If you like skiing or snowboarding on non-rolled slopes, or you are just tired of the boring ironed slopes of Sorochan and Volena with their crazy prices for lifts - then this article is for you.
In February 2000, the Endeavor shuttle for 11 days filmed a topology of the earth using Spaceborne Imaging Radar-C / X-band Synthetic Aperture Radar. See details here.
The result of this project was a publicly available database with data on the topology of the earth with the exception of its polar regions.
')
The full data array is huge in size and operating with it within the PC is rather problematic. Fortunately, on the Consortium for Spatial Information project site you can download a topological file for the area you are interested in.
The data is available in ESRI GRID (ARC ASCII) format, which is a simple height matrix. Each cell of the matrix has the same latitude and longitude. The file consists of meta-information and a matrix of heights in 6001 rows and 6001 columns.
Most likely the most suitable tool for solving this problem is Mathlab, but for lack of it - I used the improvised tools: MSSQL, Dapper, and the console application on .Net
As a result, the task is divided into the following steps:
That data loading did not take the whole day - I used the package loader - SqlClient.SqlBulkCopy.
Step 2 turned out to be the most resource-intensive. The first idea was to zadzhoynit the table on itself, approximately as follows:
However, on a table of 36,000,000 records, such a query is unbearably slow — many, many hours. Various experiments with indices did not give a significant increase in productivity.
As always it happens - the simplest solution turned out to be the most effective: if you add a field with a counter to the table with dots - and then, knowing the number of columns in each row, you can write a label to search for neighboring IDs:
Which allows to significantly simplify the JOIN:
If there is an index on the ID field, such a query is quite effective and is processed in less than 15 minutes.
For building chains of the fragments found earlier, a recursive CTE query of the following form turned out to be the most effective:
Exporting to KML using the SharpKml library is extremely simple. A Placemark object is created, which contains a LineString object containing previously calculated coordinates and height.
To ski or board somewhere to go, and not just stuck in the snow, the slope should be at least 10-12 degrees, and that was at least some interest the length of the slope should be at least 200-300 meters.
On the other hand - a slope of more than 35 degrees does not imply a relaxed skiing anymore - let's leave such cool to professionals.
As an example, the characteristics of the same Sorochan site .
The highest point in the Moscow region is 317 meters, located in the station area of 147 km. Mozhaiskogo direction ( map ). The lowest point is 75 meters ( map ), located within the boundaries of Rybinsk along the Volga.
The slope with the largest drop in the 60 m and a length of 250 m is again on the banks of the Volga near the village of Chukavino, Tver region ( map ).
Unfortunately, this slope, like most of the others with a good drop and a slope, is located along the river bank and is covered with forest, however I managed to find several theoretically suitable catable options:
It goes without saying that the famous ski resorts were in the sample:
It should be taken into account that the indicated figures are inaccurate due to the specificity of the data: each “square” in the height matrix has a side of 92.7 m.
As a POC, I chose a slope in the vicinity of the village of Starovo, Dmitrovsky District.
Local grandmothers said that here, during the time of the king of peas, the ski lift worked. The slope is gently sloping, but rather long, overgrown with shrubs in the lower reaches. Here, in the village, if you believe wikimapia has a base of hang gliders.
Video testing of this slope can be found here .
→ Project address on githaba
→ Examples of generated KML files
→ Installer
In February 2000, the Endeavor shuttle for 11 days filmed a topology of the earth using Spaceborne Imaging Radar-C / X-band Synthetic Aperture Radar. See details here.
The result of this project was a publicly available database with data on the topology of the earth with the exception of its polar regions.
')
The full data array is huge in size and operating with it within the PC is rather problematic. Fortunately, on the Consortium for Spatial Information project site you can download a topological file for the area you are interested in.
The data is available in ESRI GRID (ARC ASCII) format, which is a simple height matrix. Each cell of the matrix has the same latitude and longitude. The file consists of meta-information and a matrix of heights in 6001 rows and 6001 columns.
Most likely the most suitable tool for solving this problem is Mathlab, but for lack of it - I used the improvised tools: MSSQL, Dapper, and the console application on .Net
As a result, the task is divided into the following steps:
- We upload data from a file into a table containing the height, the row and column number in the original matrix and the height.
- We start the SQL query for the selection of neighboring fragments, for which the height difference fits into the search criteria we are interested in.
- We launch the query on the search for chains from the fragments obtained in paragraph 2.
- We export chains of the necessary length in KML.
That data loading did not take the whole day - I used the package loader - SqlClient.SqlBulkCopy.
Step 2 turned out to be the most resource-intensive. The first idea was to zadzhoynit the table on itself, approximately as follows:
select * from Points p1 JOIN Points p2 ON p1.row<>p2.row and p1.[Column]<>p2.[Column] AND p2.row between p1.row-1 and p1.Row+1 and p2.[Column] between p1.[Column]-1 and p1.[Column]+1 However, on a table of 36,000,000 records, such a query is unbearably slow — many, many hours. Various experiments with indices did not give a significant increase in productivity.
As always it happens - the simplest solution turned out to be the most effective: if you add a field with a counter to the table with dots - and then, knowing the number of columns in each row, you can write a label to search for neighboring IDs:
| ID-6002 | ID-6001 | ID-6000 |
| ID-1 | ID | ID + 1 |
| ID + 6000 | ID + 6001 | ID + 6002 |
Which allows to significantly simplify the JOIN:
select * from Points p1 join Points p2 on (p2.id in (p1.id-6000-2,p1.id-6000-1,p1.id-6000,p1.id-1,p1.id+1,p1.id+6000 ,p1.id+6000+1,p1.id+6000+2)) WHERE p1.Elevation-p2.Elevation>X If there is an index on the ID field, such a query is quite effective and is processed in less than 15 minutes.
For building chains of the fragments found earlier, a recursive CTE query of the following form turned out to be the most effective:
with chains (id,id1,id2,level,parentID,alt) AS ( select c1.id,c1.id1,c1.id2,0 as level,c1.id as parentID, c1.alt1 as alt from candidates c1 where not exists (select * from candidates c2 where c2.id2=c1.id1) and exists(select * from candidates c2 where c2.id1=c1.id2) UNION ALL select c1.id,c1.id1,c1.id2, level+1 as level,c2.id as parentID,c1.alt2 as alt from candidates c1 join chains c2 on c1.id1=c2.id2 ) select distinct p.*,level,ParentID from chains c1 join candidates c2 on c1.id=c2.id join Points p on p.ID=c2.id1 or p.id=c2.id2 Exporting to KML using the SharpKml library is extremely simple. A Placemark object is created, which contains a LineString object containing previously calculated coordinates and height.
Search terms
To ski or board somewhere to go, and not just stuck in the snow, the slope should be at least 10-12 degrees, and that was at least some interest the length of the slope should be at least 200-300 meters.
On the other hand - a slope of more than 35 degrees does not imply a relaxed skiing anymore - let's leave such cool to professionals.
As an example, the characteristics of the same Sorochan site .
- The length of the tracks: from 500 to 1000 meters.
- The width of the tracks: from 30 to 70 meters.
- Height difference: from 70 to 90 meters.
- Height above sea level: 230 meters.
- Average bias: 10-12 degrees.
- Maximum bias: 25 degrees.
results
The highest point in the Moscow region is 317 meters, located in the station area of 147 km. Mozhaiskogo direction ( map ). The lowest point is 75 meters ( map ), located within the boundaries of Rybinsk along the Volga.
The slope with the largest drop in the 60 m and a length of 250 m is again on the banks of the Volga near the village of Chukavino, Tver region ( map ).
Unfortunately, this slope, like most of the others with a good drop and a slope, is located along the river bank and is covered with forest, however I managed to find several theoretically suitable catable options:
| Delta | Slope length | Coordinates | Comment |
|---|---|---|---|
| 51 | 300 | 56 ° 16'4.70''; 38 ° 20'34.20'' | Waterfall "Gremyachy" very beautiful place |
| 43 | 200 | 56 ° 26'22.08''C; 37 ° 50'37.35'' | Village Starovo, Dmitrovsky district |
| 51 | 200 | 55 ° 38'21.04''C; 37 ° 51'37.99'' | Dzerzhinsky quarry |
| 42 | 200 | 56 ° 28'55.13''; 38 ° 11'55.16'' | Opposite Zagorskaya PSP. D. Vypuklovo |
| 59 | 250 | 55 ° 17'28.08''C; 38 ° 43'5.33''B | White Mountain. Voskresensk |
It goes without saying that the famous ski resorts were in the sample:
| Resort | Height, m | Slope length, m |
|---|---|---|
| Free | 42 | 200 |
| Soros | 40 | 200 |
| Chulkovo | 46 | 200 |
| Vine | 42 | 200 |
| Paramonovo | 50 | 250 |
It should be taken into account that the indicated figures are inaccurate due to the specificity of the data: each “square” in the height matrix has a side of 92.7 m.
As a POC, I chose a slope in the vicinity of the village of Starovo, Dmitrovsky District.
Local grandmothers said that here, during the time of the king of peas, the ski lift worked. The slope is gently sloping, but rather long, overgrown with shrubs in the lower reaches. Here, in the village, if you believe wikimapia has a base of hang gliders.
Video testing of this slope can be found here .
→ Project address on githaba
→ Examples of generated KML files
→ Installer
Source: https://habr.com/ru/post/324120/
All Articles