Recently, I stumbled upon John Nelson’s nice Pen&Ink Style for Esri ArcGIS Pro. In his blog John asked to steal this style for ones own project. I always wanted to have a personalised and slightly geeky geo poster for my office. Here is what I’ve come out with:
Feel free to download this poster and forward it to your favorite printing service. The JPG version should also be suitable as a wallpaper. I would be honoured to find some footage of your usage in the comments section of this blog!
How to create such a poster?
Since I am an engineer, I have a strong desire to briefly tell you how to assemble such a nice poster using OpenStreetMap data, SAP HANA Spatial and Esri ArcGIS Pro.
Luckily a colleague of mine uploaded the OSM planet file to an SAP HANA instance. To give you an order of magnitude: The file contains
- more than 188 million linestrings,
- almost 130 million points and
- more than 400 million polygons.
What you see on the poster above are solely lines (streets) and polygons (SAP buildings). To get the data of all SAP buildings, we can issue the following query:
SELECT * FROM "planet_osm_polygon" WHERE "way".ST_IntersectsRectPlanar(ST_GeomFromText('Point(960228.5605 6323500.4656)', 3857), ST_GeomFromText('Point(964083.8531 6325965.5597)', 3857)) = 1 AND "way_area" < 800*800 AND ("tags" LIKE '%SAP%' OR "name" LIKE '%SAP%' OR ("name" LIKE 'P%' AND "amenity" LIKE 'parking') AND "building" IS NOT NULL)
Next, I have to admit that I did a bit of trial and error to find the right set of streets. Including too many streets (e.g. small paths or service roads) makes the poster too noisy, whereas too little amount of streets results in a boring picture.
SELECT * FROM "planet_osm_line" WHERE "highway" IS NOT NULL AND ( "highway" IN ('residential', 'living_street', 'secondary', 'primary_link', 'secondary_link', 'primary', 'motorway', 'tertiary', 'tertiary_link') OR "name" IS NOT NULL ) AND "highway" NOT IN ('service', 'motorway') AND "way".ST_IntersectsRectPlanar(ST_GeomFromText('Point(960228.5605 6323500.4656)', 3857), ST_GeomFromText('Point(964083.8531 6325965.5597)', 3857)) = 1 AND "way".ST_Intersects(ST_GeomFromText('POLYGON((8.63155914163766 49.300952507088496,8.637996443273401 49.297370362622594,8.640914686681604 49.29669868154911,8.646236189367151 49.297314389549435,8.651386030675745 49.29658673381358,8.657565840246058 49.29250046745218,8.661599882604456 49.29003734876131,8.665462263585901 49.306045420458254,8.638597258092737 49.31404750656341,8.629928358556604 49.311529507733525,8.63155914163766 49.300952507088496))', 4326).ST_Transform(3857)) = 0
Now that I have the SQL statements to fetch all the required data, I can connect ArcGIS Pro to my SAP HANA instance and fetch the data by adding a query layer. Read on here to learn the details how to do this:
I adjusted the symbology in the two layers (buildings and roads) using John’s Pen&Ink Style:
Last but not least I added a layout in ArcGIS Pro to get the text and the SAP logo on the poster:
That’s it. Next I will order a physical version for the office. I hope you’ll do the same!