Databases Basics and Spacial Matrix - Discussig Geographic Potentials of Databases.

A core introduction to Data Types, Databases around us and the use cases, Integrating GeoSpacial Arrangements to our projects. This include Speaker Notes. and is indented to give an overall idea about things not focusing on a specific focus. This Incudes references of Relational Databases likePostgresql and Mysql, Key Value databases like Redis, Document DB like MongoDB, Search Engines like ElasticSearch, and the second part handles with GeoSpatial Arrangements in Postgis.

1. Databases and Spatial References Discussing Geographic P otentials

2. We Discuss On: Different Databases around us (exp. 10 min) Data Types Purpose and Examples Geospatial Arrangement of Data (exp. 15 mins) Spatial Data Types SQL - PostGIS SRID and other Standards Supporting Databases

3. Different Databases around us Before that, Assume you are a developer at linkedin , and You are assigned to share a user’s post to all his 2nd and third degree connections in a geographical area. ! What will be your “ data structure ” and “ query ” to find those users?

4. Data Types VARCHAR INT DATE ENUM (predefined constants) TEXT FLOAT TIME BLOB (file objects) CHAR BOOLEAN DATETIME JSON / XML CITEXT (case insensitive text) DECIMAL TIMESTAMP SET / ARRAY UUID DOUBLE YEAR BIT As you all know……

5. Data in Storage

6. Relational DB As you know, Tabulated Storage Structure Support Relations Reputed for Structured Datas like ecommerces, B2B e.t.c. Examples Descriptions PostgreSQL Open Source | Stable | Reliable | read-intensive | heavy write loads for Medium-sized databases MySQL Open Source | fast | Lightweight | read-intensive for Small-sized databases Sqlite Development Friendly | File based | Low memory devices

7. Key Value DB (NoSQL) Supports Storing Data against hashed Keys Quick Access Mainly used for Caching Examples Descriptions Redis In-memory storage, persistent on disk, supports various data structures, replication, high availability Etcd Distributed reliable key-value store, ideal for configuration and service discovery with strong consistency guarantees Amazon DynamoDB Managed NoSQL database, key-value and document data models, single-digit millisecond performance at any scale.

8. Graph DB What will be the best structure to share a linkedin post in 3rd level connection? Stores Relations in Graph. Examples Descriptions Neo4j Relationships-focused, flexible schema, Cypher query language, ACID transactions, high-performance graph queries. JanusGraph Scalable graph database optimized for storing and querying large graphs with billions of vertices and edges. ArangoDB Multi-model database, supports graph, document, and key/value data models in one database core.

9. “Initial” Google Maps. 📌 📌 📌 4 KM 3 KM 4 KM 4 KM

10. DocumentDB ( NoSQL) Supports Documents for unstructured Datas Support Relations Reputed for Structured Datas like Blogs, Versioned Datas, Scraping, Storing Catalogues. Examples Descriptions MongoDB Dynamic schema, rich queries, high performance, replication, horizontal scaling. CouchDB JSON-based document storage, RESTful HTTP API, map-reduce views, and replication features Amazon DynamoDB Fully managed, multi-region, durable with built-in security, backup and restore, and in-memory caching

11. Search Database Mainly Used as potential Search Engines. Capable of filtering searching and find complex analytics. Mostly Memory Based Examples Descriptions Elastic Search Full-text search, analytical queries, scalable, distributed nature, real-time indexing. Apache Solr Open-source search platform, powerful full-text search, hit highlighting. Sphinx Full-text search engine, provides text search functionality to client applications.

12. Flipkart Filter Page https://blog.flipkart.tech/

13. Time Series DB Supports Storing Data against hashed Keys Quick Access Mainly used for Caching Examples Descriptions InfluxDB High write loads, time-stamped data, downsampling, retention policies, real-time analysis. TimescaleDB Open-source time-series SQL database optimized for fast ingest and complex queries. Prometheus Open-source monitoring system with a dimensional data model, flexible query language, and autonomous server nodes.

14. Geospatial Arrangement of Data Let’s Take a small problem; , Assume you are a developer at Airbnb , and You are assigned to locate nearby hotels from my location. What will be your query?

15. Everyone know about location coordinates (latitude, longitude, topology). Collectively known as Geographic Information System (GIS) . And the term ` spatial reference `. Different organization follows different standards such as what-three-words for “spatial reference”, Most popular standard is Key Points SRID 4326 - World Geodetic System 1984 (WGS 84) and 3857 - Web Mercator .

16. SRID defines multiple standards which defines: the metrics to be used (like inc feet cm) what all parameters have to be considered (lat, long, topology, sealevel, ) What is SRID (Spatial Reference System Identifier).

17. Implementation

18. POINT POINT(97.1234 10.1222) Represents a single point on the Earth's surface defined by its longitude and latitude coordinates. LINESTRING LINESTRING(0 0, 1 1, 2 2) Represents a sequence of connected line segments defined by coordinates of its vertices. POLYGON POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)) Represents a closed shape with an arbitrary number of vertices, forming a filled area on the map. GEOMETRY GEOMETRY(POINT, 4326) Represents any geometric object. MULTIPOINT MULTIPOINT((0 0), (1 2), (2 3)) Represents a collection of points. MULTILINESTRING MULTILINESTRING( (0 0, 1 1), (2 2, 3 3)) Represents a collection of LineStrings. MULTIPOLYGON MULTIPOLYGON( ((0 0, 1 0, 1 1, 0 1, 0 0)), ((2 2, 3 2, 3 3, 2 3, 2 2))) Represents a collection of Polygons. GEOMETRYCOLLECTION GEOMETRYCOLLECTION( POINT(1 2), LINESTRING(1 2, 3 4) ) Represents a collection of other geometries.

19. Saving data into GeoLocation Field Let’s create a table hotel with its geolocation; Insert some data into it.

20. Querying With Distance Searching , for 3 nearest hotels which is within 5000 meters radius from my location [POINT(76.2673 9.9312)] .

21. Querying Within a Custom Polygon Drawing a polygon and finding hotels inside it.

22. Features Provided Spatial Queries Proximity Searches : Find features within a specified distance from a point, line, or polygon (e.g., finding hotels within 5 km of a location). Point-in-Polygon : Determine whether a point lies within a given polygon (e.g., identifying whether a specific location falls within a city boundary). Intersection : Identify where two geometries intersect, producing a geometry of the intersection (e.g., finding where a road crosses a river). Spatial Measurements Distance Calculation : Measure the distance between two points, lines, or polygons. Area Calculation : Compute the area of polygons. Length Calculation : Determine the length of a line or the perimeter of a polygon.

23. Features Provided Spatial Analysis Buffering : Create a buffer area around a point, line, or polygon at a specified distance (e.g., creating a 10 km buffer zone around a park). Overlay Analysis : Combine two or more datasets to create a new set of geometries and attributes based on their spatial relationship (e.g., overlaying land use and flood zones to identify at-risk areas). Spatial Join : Linking records from two sets of spatial data based on their spatial relationship (e.g., assigning census data to electoral districts based on location). Spatial Aggregation Grouping by Area : Aggregate data based on spatial boundaries (e.g., summing population by districts). Spatial Binning : Aggregate points into larger areas or bins for analysis (e.g., spacial heatmap for density analysis).

24. Features Provided Data Management Geocoding : Converting addresses into geographic coordinates. Reverse Geocoding : Converting geographic coordinates into readable addresses or place names. Data Import/Export : Importing and exporting geospatial data in various formats (e.g., Shapefile, GeoJSON, KML). Indexing and Optimization Spatial Indexing : Creating spatial indexes to improve the performance of spatial queries. Partitioning : Dividing large datasets into smaller, more manageable pieces based on spatial criteria.

25. Features Provided Visualization Mapping : Creating visual representations of geospatial data on a map. Thematic Mapping : Displaying data based on themes or categories, such as heat maps, choropleth maps, or symbol maps. Modification and Editing Geometry Editing : Altering the shape or position of geometric features (e.g., moving a point, editing a polygon's boundary). Topology Editing : Managing spatial relationships between features (e.g., ensuring road networks connect without overlaps or gaps).

26. Geospatial support for Other Databases Database Description Extension (if needed) PostgreSQL As PostGIS extension PostGIS MySQL Spatial Data Types and Functions built-in SQLite Spatial Data Support through extension SpatiaLite MongoDB Geospatial Indexing and Queries built-in Neo4j Spatial Types and Functions for Geocoding and Routing built-in Elasticsearch Geo-point and geo-shape data types for geographic search built-in Redis Geospatial indexing and querying Redis Geo (built-in module) InfluxDB Geotemporal data support via Flux built-in

27. Thank You! (For No Questioning 😜 )