GRID harmonization

[ghilbrae]

In order to make the re-projection, we'd like to know more about the output GRID.

We already know that it must be EPSG 3035 but we also need the reference raster.

I guess we'd also need a reference raster for Europe, one for Naples, and, in the future, one for each DC and probably, one for each study area we'd like to cover. In this case, who will be defining this reference raster? (Is this specified in the Data Package? Is it created when the user selects an area? Any other option?)

[stefanon]

Hi, if I understand well, you need EEA reference grids, that can be found there, up to 1km cell size:

https://data.europa.eu/euodp/data/dataset/data_eea-reference-grids-2

[maesbri]

As discussed in today's telco, AIT (@bernhardsk ) will provide the reference vector grids: 1km2, 500m2, 250m2 and 100m2. (These grids will cover the whole europe)

[negroscuro]

Please AIT (@bernhardsk ), can you upload the rest of the reference grids (other resolutions) and upload them to ATOS ftp?

The folder where to store them is:
/clarityftp/europe/reference_grid

Thank you in advance.

[negroscuro]

I already uploaded 10km and 100km grid resolution into ATOS FTP:
/clarityftp/europe/reference_grid/10km
and
/clarityftp/europe/reference_grid/100km
respectively.

Anyway as i understood from Mattia, 500m and 250m are the more important ones.

[ghilbrae]

Ok, let us know when the reference grids are uploaded. We need them ASAP to do the interpolation we committed to have today.

[ghilbrae]

We've uploaded some reference grids to the FTP. You can find them at the directory: Europe/reference_tiff

[humerh]

I generated the grids for 1km, 500m and 250m and I uploaded the generated file (compressed via .7Z) to the FTP server.
Additionally I added a short description, how to load CSV-files in QGIS. Over this way you can transform the grids to any other format (Shape, Geopackage, ...)

I also added the small JAVA file, which I used for generation - just for documentation.
Unfortunately it was not possible to generate this big files as SHP-files on my "small" computer for all Europe directly with geotools in time, so I implemented the generation of CSV-Files "by hand".

I uploaded also a shape file for the rotated pole EUR11 grid.

[ghilbrae]

OK, we'll take a look at the files and come back if we have any question.

On a side note, are you going to work with CSV files? This is a very odd choice and not a very common format for geo data.

[maesbri]

these csv files will be transformed into shape or geopackage format (event into geotiff if necessary)

[humerh]

Sorry for generating .CSV file, but that was the most efficient way for me to generate the file by hand and to compress them and to Transfer them to the FTP Server.
As described you can easily convert this file to SHP or to other formats over the QGIS tool or any other GIS toool.

YES, the implemented projection of the polygon used EPSG:3035 projection.
You have to selct this projection during Import!!

[humerh]

What is the EUR11-Grid?

The Hazard Grids come in NetCDF files and contains a grid using a "rotated pole coordinate System" (EUR11)
The Dimension of this grid in the NetCDF file is 345x351.
If you extract this grid to a table and you generate a cellId (i,j) in the Format "jjjiii" you can join this to my provides Shapefile.
Example: map grid[0,0] to '000000'
grid[0,1] to '001000'
....
grid[123,456] to '456123'

So you can use Standard Shape-Viewer to visualise the hazard data from NetCDF without understanding the "rotated pole projection".
I generated this Shapefile by myself to simplify my work and to be able to apply standard "Vector Algebra" to these data.

[humerh]

Organisation of Grids on National Level?! (proposal)

As I assume, the handling of the rasters for "ALL EUROPE", especially for 250m-Grids, will NOT be successful and lead to unacceptable performance and ressource damand, I suggest to switch to a National Organisation of data.

As we have selected the Country during the definitin of a study, this can then be used as a Parameter to select the right datasource.

I think, this should be discussed and decided!!

[maesbri]

Supposing that we have the whole vector grid layer in a Postgis database (and exposed via Geoserver WFS), you will never retrieve the whole layer but only the area of your study. That's the purpose of using a query with the bounding box (or irregular polygon) defining the study area.

[stefanon]

That's right! once in a Postgis db exposing data via WMS for viewing or WFS for working with, by means of an OGC compliant software like Geoserver, the GetMap requests with bounding box parameters are used for keeping the data transfer limited to only the necessary with good performances.

[maesbri]

I have converted the 500m csv to the GeoPackage format and uploaded it to the sftp.

(Un?)surprisingly the new file is 4GB (compressed is about 183MB) which is way bigger than the uncompressed csv.

It remains to be assessed the performance of using PostGIS database for operating with it (note: check some optimizations that should be included in the database such as clustering the data in the table according to the spatial index: https://www.postgresql.org/docs/11/sql-cluster.html)

It also remains to be checked, but I guess the raster version might be also lighter ...

[ghilbrae]

We have just uploaded a bunch of layers to the FTP:

• A zip file containing the reference grid rasterized (tif) at 500 m following the template -> /clarityftp/europe/reference_grid/500m
• A zip file with the population re-projected to 500 m (total and age ranges) -> /clarityftp/europe/population
• A zip file with the heat-wave hazard re-projected at 500 m -> /clarityftp/europe/hazard indices/heat/heat wave duration

[maesbri]

I have tried to put some order in the sftp (more in particular in the folder related to the european reference grid).

Please, when uploading data to the sftp try to be consistent with the folder and file names, othewise sometimes is not evident to know what is it about.

See the example below, I have renamed the files indicating if it is a csv, geopackage or geotfif

[maesbri]

@ghilbrae

I am not sure if it makes sense resampling the heat wave duration index (and other datasets) from the 1km resolution to the 500m ... as we are producing something from a coarser resolution. It should be the other way around, from the 500m2 aggregate the data for producing a 1km2 if it is necessary.

Besides, I thought it was agreed to have for all datasets produced by the project in a resolution of 500m2 for the screening.

[negroscuro]

Both grids, 500 and 250m are in Atos Potgis database, also pubished in ATOS Geoserver as WFS.
I clusterized both grid database tables.

[negroscuro]

I need help on regard of the European etrs laea 500m grid.

For some reason it is missing Iceland, so cities there (like Reijkavik) are not going to be working for input layer for local effects generation.
I tried to generate a matching grid for that area but after some working hours I realized the gridID’s are missing (they look like 500mE15470N10320 )

In the end I understood @humerh are the one who provided the grid, and I do not know if a new version can be sent to me or if the Iceland part can be sent independantly to be put togheter with the rest in Atos database or if the gridID’s are not needed in such case I can keep using the one I just generated.

[negroscuro]

This was originally posted in:
clarity-h2020/data-package#59 (comment)

I moved it here, it says:

I spotted an issue on europan grid 500m ETRS LAEA. it seems Iceland is out of the grid so this means besides having all datasets, missing the grid is enough to not be able to generate Reykjavik cells and its land use percentages.
I am trying to generate specific area of the grid "manually" for iceland to add it to the european grid I work with (by the way who provided it? beacause I was searching of european grid in EEA and only found 1km,10km,100km).
https://www.eea.europa.eu/data-and-maps/data/eea-reference-grids-2

Darker area is Reijkavik.

I was able to generate a 500m cell grid for Iceland and then isolate those cells overlapping Iceland boundaries, so now I am addding those cells to the old 500m laea etrs grid for europe, now to be complete including Iceland and having Reijkavik working, so now land use percentages for Reijkavik can be generated.

The only problem wich I need help with, is that each gridid are not there but I am not sure if they are needed... so should I create them somehow? but I am not sure how to do it. Europen grid ETRS LAEA 500m has those gridid's looking like: "500mE15470N10320"

Does all this make any sense or should I remove Reijkavik?

Originally posted by @negroscuro in clarity-h2020/data-package#59 (comment)

[humerh]

You kan find the shapefiles of iceland here:
https://www.eea.europa.eu/data-and-maps/data/eea-reference-grids-2/gis-files/iceland-shapefile
but this file incudes the raster only ffor larger than 1kmx1km.
I generated the european grid for the smaller sized raster for all European region, where the European Population Raster (1kmx1km) contained data. It seems, that Iceland was not included in the population raster of the year 2011 (GEOSTAT_grid_POP_1K_2011_V2_0_1.csv).

So currently we will not be possible to generate results for this regions, as we also have no population data.

After some minutes of "googling" I found an additional data set, which we should handle:
JRC-GHSL_AIT-grid-POP_1K_2011.csv – the modelled data from JRC and AIT for those countries that could not provide national data: CY, IS, LU, Bosnia and Herzegovina, Serbia, Montenegro, the Former Yugoslav Republic of Macedonia, Andorra, Isle of Man, Monaco, San Marino, Vatican State;

It would be possible to import also these population data and also the grid definitions of these countries; after this import also these countries can be supported.

[negroscuro]

That dataset only includes 1km x 1km grid for Iceland.
I manage to create the grid for 500m x 500m cells matching the rest of the european grid.

The problem now is solved since Reijkavik can be generated, not all needed is there, but as you point the mortality data for those other cities are not in our database, so if you can share that data, it would be nice to add them, I will try to import it but I guess most of those cities are not available among the cities from urban atlas, ESM and STL, that means we will no be able to generate local effects for them but we can add them anyway just in case datasets improve in the future...

[DenoBeno]

Are we really discussing the heat mortality on Island? While we are at it, how about Greenland? :-b Mario Nuñez <notifications@github.com> schrieb am Do., 9. Jan. 2020, 17:10:

…

That dataset only includes 1km x 1km grid for Iceland. I manage to create the grid for 500m x 500m cells matching the rest of the european grid. The problem now is as you point the mortality data for those cities. If you can share that data, it would be nice, I will try to import it but I guess most of those cities are not available among the cities from urban atlas, ESM and STL, but we can add them anyway just in case... — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#42?email_source=notifications&email_token=AAWTC7QWVYKLIH4X74A2INLQ45EA3A5CNFSM4GFEOLCKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEIQ2SLY#issuecomment-572631343>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAWTC7W2UK7BMHCH4RCUTU3Q45EA3ANCNFSM4GFEOLCA> .

[humerh]

I have imported the population data and have generated the grids for Island and other small countries (like CY, IS, LU, Bosnia and Herzegovina, Serbia, Montenegro, the Former Yugoslav Republic of Macedonia, Andorra, Isle of Man, Monaco, San Marino, Vatican State)

I have uploaded the generated files to the FTP-Server.

[negroscuro]

I found grid data in FTP, but for population I assume you imported it in your system so probably I need an URL's to access it like the other mortality data you provided, am I right?

[negroscuro]

I am having issues to import new grid you provided:

-Error because SRID is 0 instead of 3035 (I tried a workaround by setting new table SRID to 0 to be able to import all, and then planning to do a conversion to 3035 but I do not understand why it says it is 0 since coordinates look pretty similar to the ones in 3035)
-Error because GRIDID is repeated "500mE50135N22320", this is worse since both repeated rows seems to have different coordinates, so one of them need an appropiate GRIDID but I do not know which one to set.

[humerh]

The featureName for the population is: "tab.CLY_POPULATION.2016".
So the whole URL is:
https://service.emikat.at/EmiKatTst/api/scenarios/2846/feature/tab.CLY_POPULATION.2016/table/data?rownum=1000

It is important to know, that on the boarder between countries you will get the grid (GridId) for each country extra.

So it may be, that a grid cell exists more than one time.

The structure of each grid cell is constructed very simple:
gridId;GEOM
500mE63490N16300; POLYGON ((6349000 1630000, 6349500 1630000, 6349500 1630500, 6349000 1630500, 6349000 1630000))
500mE63490N16305; POLYGON ((6349000 1630500, 6349500 1630500, 6349500 1631000, 6349000 1631000, 6349000 1630500))
The coordinate values are "Meters" in the EPSG:3035 coordinate systems, defined for the lower-left point of the grid cell; lenght of the cell sides is given by the prefix "500m".

So it is no miracle to construct this grid raster manually.

[negroscuro]

Nice, I will download mortality data.

But regarding the grid, I am not sure to understand. If I try to insert new grid for iceland in database I got an error because of gridid is not unique, so I do not know how to add that dataset to our database... Do you mean I have to remove each duplicate manually and croscheck gridID being the correct one?

It also gave me error on the SRID 0... which cause not to complete the operation since database expects SRID 3035

[negroscuro]

I finally updated the european grid with Iceland area generating gridIds as explained by Heinrich which is a result of combination of coordinates from cells. Like this:
CONCAT('500mE',SUBSTRING(ST_Xmin(geom)::varchar,1,5),'N',SUBSTRING(ST_Ymin(geom)::varchar,1,5) )
I also added some cells missing from Barcelona near airport area nad 2 more for west area of Napoli in the coast, since I noticed they were missing and that was as well stopping my scripts to generate land use data and land u se percentages as consequence.