It’s been busy week. That’s why #GeospatialTuesday entry is coming on Friday. Better late than never. The week started with QCon conference in London, where I talked about and demoed geospatial data processing using SAP HANA. At the same time Craig was doing his magic explaining Express edition to the conference participants.
While travelling to London I found that interesting discussion on Reddit (the original post there was deleted for whatever reason) about the size of London comparing to European countries. What a great idea to show the use of SAP HANA and data sets we’ve been using in previous blogs already!
So, the question is what countries in Europe have population smaller than London?
Here is the answer from my SAP HANA Express and the data we already have after previous blogs.
Now let me walk you through how I approached it.
We should have all required data available already:
* GeoNames (which has population data and locations for countries and cities) in schema GEONAME,
* Country shapes in the table “TESTSGEO”.“cntry00”,
* Continents shapes in the table “TESTSGEO”.“continent”
From GeoNames data we need data for the Great Britain (instead of Poland as in the previous blog) and countries (where featurecode is PCLI) only. To get all countries lets download the complete dataset, but then filter and upload only what we need. wget http://download.geonames.org/export/dump/allCountries.zip unzip allCountries.zip cat allCountries.txt | grep PCLI > allPCLI.txt wc -l allPCLI.txt
The output from the last command (counting lines in allPCLI.txt file) was 201 for me at the time of writing.
Let’s load this file into a separate table, so following statements will be more clear. CREATE TABLE “GEONAME”.“PCLIS” LIKE “GEONAME”.“GEONAMES” WITH NO DATA; IMPORT FROM CSV FILE ’/usr/sap/HXE/HDB90/work/geonames/allPCLI.txt’ INTO “GEONAME”.“PCLIS” WITH FIELD DELIMITED BY ’t’ DATE FORMAT ‘YYYY-MM-DD’ ERROR LOG ‘PCLI.err’ NO TYPE CHECK THREADS 8 OPTIONALLY ENCLOSED BY “ COLUMN LIST ("GEONAMESID”, “NAME”, “ASCIINAME”, “ALTERNATENAMES”, “LATITUDE”, “LONGITUDE”, “FEATURECLASS”, “FEATURECODE”, “COUNTRYCODE”, “CC2”, “ADMIN1CODE”, “ADMIN2CODE”, “ADMIN3CODE”, “ADMIN4CODE”, “POPULATION”, “ELEVATION”, “DEM”, “TIMEZONE”, “MODIFICATIONDATE”); UPDATE “GEONAME”.“PCLIS” SET “LOC_4326” = ST_GeomFromText(‘POINT(’ || “LONGITUDE” || ’ ’ || “LATITUDE” || ’)’, 4326), “LOC_3857” = ST_GeomFromText(‘POINT(’ || “LONGITUDE” || ’ ’ || “LATITUDE” || ’)’, 4326).ST_Transform(3857) WHERE “LOC_4326” is null;
Now that all data is there let query it… select ST_UnionAggr(d.“SHAPE”).ST_asGeoJSON() from ( select b.“LOC_4326” from “GEONAME”.“GEONAMES” a join “GEONAME”.“PCLIS” b on a.POPULATION >= b.POPULATION join “TESTSGEO”.“continent” c on c.“SHAPE”.ST_Covers(b.“LOC_4326”) = 1 where a.NAME = ‘London’ and a.FEATURECODE = ‘PPLC’ and c.“CONTINENT” = ‘Europe’ ) c join “TESTSGEO”.“cntry00” d on c.“LOC_4326”.ST_Transform(1000004326).ST_Within(d.“SHAPE”) = 1;
…and use another popular website http://geojsonlint.com/ to visualize the GeoJSON output from the query. That was it.
I am writing this in Gdańsk airport finishing this busy week. Today I presented here at Kariera IT conference. And I am excited as it was the biggest number of participants I had so far at this events series. Next stop will be in Warsaw on March 24th. See you there? http://bit.ly/2pPPoHR #SAP #SAPCloud #AI
