rights reserved. 1 Public The World in a Database: Geospatial Analytics for the rest of us Vitaliy @Sygyzmundovych Rudnytskiy GeekWeekWro, Wrocław, 2018/04
rights reserved. 2 Public Let me introduce myself - Vitaliy Rudnytskiy (aka Witalij Rudnicki) @Sygyzmundovych - SAP’s Developers Center (developers.sap.com) - IoT, Data, Analytics - Based in Wrocław, Poland 51°04'40.3"N 16°57'48.8"E (WGS84)
rights reserved. 6 Public Disclaimer The information in this presentation is confidential and proprietary to SAP and may not be disclosed without the permission of SAP. Except for your obligation to protect confidential information, this presentation is not subject to your license agreement or any other service or subscription agreement with SAP. SAP has no obligation to pursue any course of business outlined in this presentation or any related document, or to develop or release any functionality mentioned therein. This presentation, or any related document and SAP's strategy and possible future developments, products and or platforms irections and functionality are all subject to change and may be changed by SAP at any time for any reason without notice. The information in this presentation is not a commitment, promise or legal obligation to deliver any material, code or functionality. This presentation is provided without a warranty of any kind, either express or implied, including but not limited to, the implied warranties of merchantability, fitness for a particular purpose, or non-infringement. This presentation is for informational purposes and may not be incorporated into a contract. SAP assumes no responsibility for errors or omissions in this presentation, except if such damages were caused by SAP’s intentional or gross negligence. All forward-looking statements are subject to various risks and uncertainties that could cause actual results to differ materially from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, which speak only as of their dates, and they should not be relied upon in making purchasing decisions.
rights reserved. 9 Public Greater Business Insight & Action with Geographic Data Customer Examples Improve situational awareness with location intelligence to keep travelers safe Track thoughts of flights per day using rich 3D mapping interface with spatial and temporal coordinate to improve flight operations Predict impact of natural disasters, analyze cluster maps to assess critical infrastructure (roads, hospitals etc.).
rights reserved. 10 Public Greater Business Insight & Action with Geographic Data Customer Examples NSW Fire Rescue Identify & visualize emergency call trends RTSA Storm Tracker: Track storms in real-time to estimate damage based on predicted wind speeds City of Boston Manage roads with real- time analysis of snow fall
rights reserved. 11 Public How I got into the geospatial for the first time https://blogs.sap.com/2016/06/24/from-wroclaw-to-warsaw-and-back-iot-analysis-with-sap-hana-sql
rights reserved. 12 Public Why SQL? The main tools for Analytics/Data Science by popularity (2017) 1. Python 2. R language 3. SQL 4. Java 5. Unix shell/awk/gawk 6. C/C++ 7. Perl 8. Julia https://www.kdnuggets.com/2017/05/poll-analytics- data-science-machine-learning-software-leaders.html
rights reserved. 15 Public The Forrester Wave™: Translytical Data Platforms, Q4 2017 “…Translytical data platforms, an emerging technology, deliver faster access to business data to support various workloads and use cases. EA pros can use them to drive new business initiatives…” source: https://reprints.forrester.com/#/assets/2/308/'RES134282'/reports “SAP crushes translytical workloads. SAP HANA is a shared-nothing, in-memory data platform, the core of SAP's translytical platform, which supports many use cases, including real- time applications, analytics, translytical apps, systems of insight, and advanced analytics…”
rights reserved. 16 Public SAP HANA, Express edition developers.sap.com/sap-hana-express • targeted to run in resource-constrained environments and contains a rich set of capabilities for a developer to work with, including geospatial data types and algorithms • free productive use up to 32 GB of RAM, • comes as a binary installer, as a pre-configured virtual machine image (ova file), as a docker or cloud image Database management Web server JavaScript Graphic modeler Data virtualization Extract, load, transform and replication Columnar store – Transaction and analytical processing Multicore and parallelization Advanced compression Multitenancy Multitier storage Graph Predictive Search Series data Business functions Apache Hadoop and Apache Spark integration Streaming analytics Application lifecycle management High availability and disaster recovery Openness Data modeling Administration and security Spatial Text analytics SAP Fiori® user experience(UX) Application development Data integration and quality Advanced analytical processing Data quality Remote data sync
rights reserved. 17 Public Spatial Data Types Vector, raster data, point cloud Vector data Raster data Point LineString Polygon CircularString MultiPoint MultiLineString MultiPolygon GeometryCollection 14 35 25 17 39 59 16 15 17 X Y Z Complex types
rights reserved. 19 Public Spatial: data types in SAP HANA and in SQL/MM standard Geometries Point – a single location in space MultiPoint – a collection of points LineString – a geometry with a length MultiLineString – a collection of LineStrings Polygon – a region of space MultiPolygon – a collection of polygons CircularString – a sequence of circular arc strings https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/latest/en-US/7a2ef60e787c10148e86fd0f4c60cb29.html
rights reserved. 22 Public Using spatial predicates in SQL SAP HANA Spatial In the SELECT clause SELECT "LOCATION".ST_Within([geometry]), "NAME" FROM "CUSTOMERS"; In the WHERE clause SELECT "NAME" FROM "CUSTOMERS" WHERE "LOCATION".ST_Within([geometry]) = 1; As JOIN condition SELECT * FROM "STORES" AS S LEFT JOIN "CUSTOMERS" AS C ON C."LOCATION".ST_Within(S."TRADING_AREA") = 1;
rights reserved. 23 Public Trying it out: number π by throwing virtual darts https://blogs.sap.com/2016/12/14/calculating-number-%CF%80-by-throwing-darts-digitally-in-sap-hana/ https://www.youtube.com/watch?v=M34TO71SKGk
rights reserved. 24 Public Transformation functions SAP HANA Spatial SQL methods to transform geometries "GEOM".ST_Boundary() "GEOM".ST_Buffer([distance]) "GEOM".ST_ConvexHull() "GEOM".ST_Envelope() Buffer Convex Hull Envelope Boundary
rights reserved. 25 Public Trying it out: ASCII world map from open data shape files https://blogs.sap.com/2017/02/13/ascii-art-map-of-the-world-using-sap-hana-geospatial-processing/
rights reserved. 26 Public Spatial data exchange formats SAP HANA Spatial Well-Known Text (WKT) 'POINT(1 1)' Well-Known Binary (WKB) 0101000000000000000000F03F000000000000F03F ESRI Shapefiles Popular format for geospatial vector data GeoJSON {"x" : 8.6, "y" : 49.3, "spatialReference" : {"wkid" : 4326}} Scalable Vector Graphic (SVG) <rect width="1" height="1" fill="deepskyblue" stroke="black" strokewidth=" 1" x="1" y="-1"/> SAP HANA Data sources Analysis / Apps files input output
rights reserved. 27 Public Examples of popular sources of geo data shape files OpenStreetMap: Coastlines, Lands and Waterbodies http://openstreetmapdata.com/data Natural Earth (public domain map dataset available at 1:10m, 1:50m, and 1:110m scales) http://www.naturalearthdata.com/downloads/ World Borders from Thematic Mapping http://thematicmapping.org/downloads/ GeoNames http://www.geonames.org
rights reserved. 28 Public Spatial aggregation SAP HANA Spatial ST_UnionAggr ("SHAPE") Returns the spatial union of all of the geometries ST_EnvelopeAggr ("SHAPE") Returns the bounding rectangle for all of the geometries in a group ST_ConvexHullAggr ("SHAPE") Returns the convex hull for all of the geometries in a group ST_IntersectionAggr ("SHAPE") Returns the spatial intersection of all of the geometries in a group ST_AsSVGAggr("SHAPE") Returns a complete or partial SVG document that renders the geometries in a group
rights reserved. 31 Public Set operations SAP HANA Spatial "SHAPE_A".ST_Union("SHAPE_B") "SHAPE_A".ST_Intersection("SHAPE_B") "SHAPE_A".ST_Difference("SHAPE_B") "SHAPE_A".ST_SymDifference("SHAPE_B") Union Intersection Difference Symmetric Difference
rights reserved. 32 Public A neighbor with the longest border? https://blogs.sap.com/2018/02/20/get-to-know-your-neighbors-with-sap-hana-geospatialtuesday/ For Germany:
rights reserved. 33 Public The neighbor with the longest border? https://blogs.sap.com/2018/02/20/get-to-know-your-neighbors-with-sap-hana-geospatialtuesday/ source: https://en.wikipedia.org/wiki/Geography_of_Germany
rights reserved. 36 Public Spatial Data Types and Sources Interior, exterior, and boundary Interior All points that are part of the geometry – except for the boundary Exterior (all white) All points that are not part of the geometry
rights reserved. 42 Public Computation functions SAP HANA Spatial SQL methods to compute geometry properties "LINE".ST_Length() "GEOM".ST_Perimeter() "GEOM".ST_Area() "GEOM".ST_Centroid() "GEOM".ST_Distance("GEOM") "GEOM".ST_isValid() "GEOM".ST_isEmpty() "LINE".ST_isClosed() "GEOM".ST_isSimple() Length Perimeter Area Centroid Distance
rights reserved. 43 Public SAP HANA, tell me where the center of Europe is https://blogs.sap.com/2017/04/11/sap-hana-tell-me-where-the-center-of-europe-is/
rights reserved. 54 Public Round Earth and Planar World Projections to Planar World ▪ distance/area calculations with (acceptable?) distortion ▪ projections: cylindrical, conical, azimuthal, hybrid, … r Round Earth Models ▪ accurate results in specific UoM ▪ more complex calculations Projection Transformation
rights reserved. 56 Public Where do you put the reference line? A meridian, based at the Royal Observatory, Greenwich, in London, was established in 1851. In October of 1884, 41 delegates from 25 nations met in Washington, D.C. for the International Meridian Conference. This conference selected the meridian passing through Greenwich as the official prime meridian due to its popularity.
rights reserved. 58 Public Wrocław Meridian(s) Source: http://www.fakt.pl/wydarzenia/polska/wroclaw/dlaczego-we-wroclawiu-mamy-az-dwa-poludniki/6qjq2ef
rights reserved. 61 Public Trying it out: Different points of view on ASCII world maps https://blogs.sap.com/2017/02/28/ascii-art-map-of-the-world-different-point-of-view/
rights reserved. 65 Public Trying it out: ASCII world map seen from the space ;) https://blogs.sap.com/2017/03/06/ascii-art-map-of-the-world-the-polar-azimuthal-equidistant-projection/
rights reserved. 68 Public Round Earth Models Sphere is a simplified Earth model radius of earth = = 6371 km (constant) equator r Spheroid as proper earth model (here: WGS84) semi major axis = equatorial radius: = 6378.137 km semi minor axis = polar radius: = 6356.752 km flattening: = ( − ) / equator er pr
rights reserved. 70 Public Spatial Reference Systems The Earth is not flat – and it is not a perfect sphere either Due to its rotation, its approximate shape is more like that of an oblate spheroid (ellipsoid) The basic structure of an SRS consists of three parts: Reference ellipsoid – what is the shape of the Earth? Geometric datum – where is the Earth's center? Coordinate system There are global fitting ellipsoids and many ‟locally optimized” ellipsoids A few of the “old” ellipsoids are still in use, e.g. the Airy ellipsoid (from 1830) or the Bessel ellipsoid (from 1841) Mass distribution around the earth. Source: Wikipedia
rights reserved. 73 Public Spatial Reference Systems GPS: WGS 1984, aka EPSG:4326: http://epsg.io/4326 Another one in the everyday use: OpenLayers:900913, aka EPSG:3857: http://epsg.io/3857 In Europe: http://www.crs-geo.eu In the US: https://www.nga.mil In Poland: http://gisplay.pl/geodezja/uklady-wspolrzednych.html Master EPSG Dataset (online and downloadable): http://www.epsg.org/EPSGhome.aspx There are 3000+ in total existing
rights reserved. 79 Public What is the distance between two points? select new st_point('POINT (0 0)').st_distance(new st_point('POINT (0 1)')) as distance from dummy; -- The result is „1” But on Earth between the two GPS points? select new st_point('POINT (0 0)',4326).st_distance(new st_point('POINT (0 1)',4326)) as distance from dummy; -- Now the result is „110574.38855796008”, i.e. 110.57 km https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/latest/en-US/7a2ea357787c101488ecd1b725836f07.html WGS84 - SRID 4326 „The WGS84 standard provides a spheroidal reference surface for the Earth. It is the spatial reference system used by the Global Positioning System (GPS). The coordinate origin of WGS 84 is the Earth's center, and is considered accurate up to ±1 meter. WGS stands for World Geodetic System.”
rights reserved. 91 Public Access Functions SAP HANA Spatial Methods to access geometry properties "POINT".ST_X(), "POINT".ST_Y() "GEOM".ST_XMax(), "GEOM".ST_XMin() "LINE".ST_NumPoints(), "LINE".ST_PointN([n]) "GEOM".ST_Dimension() "LINE".ST_StartPoint(), "LINE".ST_EndPoint() "GEOM".ST_GeometryType() "GEOM".ST_Srid() "GEOM".ST_ExteriorRing() "GEOM".ST_InteriorRingN([n]) Geometry Type = ST_Polygon NumPoints = 3 Start End 2nd Point ST_YMin() ST_YMax() Exterior Ring
rights reserved. 92 Public Is CET time zone in the middle of Europe? https://blogs.sap.com/2017/09/18/sap-hana-is-cet-time-zone-in-the-middle-of-europe/
rights reserved. 94 Public And what are central points of Europe’s mainland? https://blogs.sap.com/2018/01/16/finding-the-central-point-of-continents-mainlands-with-sqlscript-spatial- support/
rights reserved. 96 Public Clustering SAP HANA Spatial You use clustering to divide/separate points into “meaningful” groups. SAP HANA supports three clustering algorithms ▪ Grid ▪ K-means ▪ DBSCAN When clustering, you can ▪ Associate each data point with a cluster ▪ Derive cluster properties, like centroid and envelope ▪ Aggregate data of the cluster’s data points spherical clusters non-spherical clusters
rights reserved. 100 Public 4*3 grid clustering Spatial Clustering Grid Quick and easy way to use clustering Methods ▪ ST_ClusterID() ▪ ST_ClusterEnvelope() Cells tessellate the grid. Every cell has a unique identifier. SELECT ST_ClusterID(), ST_ClusterEnvelope(), COUNT(*) FROM "CUSTOMERS" GROUP CLUSTER BY "LOCATION" USING GRID X CELLS 10 Y CELLS 10;
rights reserved. 102 Public Spatial Clustering DBSCAN Density-based clustering method Methods ▪ ST_ClusterID() Suitable for non-spherical clusters. Parameters: EPS (distance), MINPTS (min nr of points) SELECT ST_ClusterID(), COUNT(*) AS C FROM "CUSTOMERS" GROUP CLUSTER BY "LOCATION" USING DBSCAN EPS 40 MINPTS 50; DBSCAN clustering
rights reserved. 103 Public Clustering abandoned places in Poland https://blogs.sap.com/2018/02/28/geonames.org-in-sap-hana-opendataday-meets-geospatialtuesday
rights reserved. 108 Public openSAP course (archived): Spatial Analysis with SAP HANA Course Characteristics Duration: 3 weeks (4-6 hours per week) Course Content Week 1: Introduction to SAP HANA Spatial Week 2: Spatial Analysis Week 3: Development of Spatial Applications Week 4: Final Exam https://open.sap.com/courses/hsgs1
rights reserved. 109 Public openSAP course (archived): Maps and 3D Made Easy with SAPUI5 Course Characteristics Duration: 3 weeks (4-6 hours per week) Course Content Week 1: Visualize Geospatial Data Week 2: Connect Your Data to 3D Content Week 3: Beyond the Basics Week 4: Final Exam Target Audience • Application developers • Application architects • User interface designers https://open.sap.com/courses/3dmv1
rights reserved. 115 Public Basics you have learned today Spatial data types Spatial predicates Spatial functions Access Computations Transformations Spatial sets and aggregations Data exchange formats Spatial Reference Systems (SRS)
sygyzmundovych
cer
tomoya0x00
mikik0
qnighy
seike460
0si43
ymtdzzz
yuisakamoto
ivargrimstad
marokanatani
righttouch
mizoguchicoji
mongodb
geeforr
lara
kastner
aarron
danielanewman
afnizarnur
bryan
philhawksworth
holman
destraynor
andyhume
