Fortgeschrittene Geodaten (PostGIS)

Welcome to Advanced Geospatial Data (PostGIS)! In this lesson, we'll explore how to handle location-based data directly within your Supabase database.

Geospatial data is crucial for apps that deal with maps, locations, navigation, or proximity services.

PostGIS is a powerful, open-source extension for PostgreSQL that adds support for geographic objects.

• It transforms your database into a spatial database.
• It allows you to store, query, and analyze location data (points, lines, polygons).
• Supabase, being built on PostgreSQL, fully supports PostGIS.

Traditional databases store locations as separate latitude/longitude columns, making complex queries difficult.

PostGIS provides specialized functions to:

• Calculate distances between points.
• Find locations within a specific radius.
• Determine if points are inside an area.
• Perform complex spatial joins and analyses.

Before using PostGIS, you need to enable the extension in your Supabase project. This is a one-time step done via the SQL Editor in your Supabase dashboard.

Simply run the following command:

PostGIS introduces new data types to store spatial information:

• GEOMETRY: Stores data on a 2D Cartesian plane (flat earth model). Good for small-scale, local areas.
• GEOGRAPHY: Stores data on a spherical globe (real-world coordinates). Best for large-scale, global applications.

We'll primarily use GEOMETRY for simplicity in examples, but GEOGRAPHY is often preferred for real-world distances.

Let's create a table to store points of interest, like coffee shops, using a GEOMETRY column. The SRID (Spatial Reference ID) 4326 is standard for latitude/longitude.

To insert a point, we use the ST_GeomFromText() function, which converts a Well-Known Text (WKT) representation into a PostGIS geometry object. The format is 'POINT(longitude latitude)'.

One common task is finding locations near a specific point. We can use ST_Distance() to calculate the distance between two geometry points. The unit of distance depends on the SRID (e.g., meters for 4326 on GEOGRAPHY type, degrees for GEOMETRY type).

Here, we find shops near a reference point (e.g., user's current location).

For more efficient proximity searches, especially when working with GEOGRAPHY types for real-world distances, ST_DWithin() is excellent. It checks if two geometries are within a specified distance.

Let's find all coffee shops within 1000 meters of a specific point using GEOGRAPHY for accurate distance calculation.

Great job! You've taken your first steps into the world of geospatial data with PostGIS and Supabase.

• We enabled the PostGIS extension.
• Learned about GEOMETRY and GEOGRAPHY types.
• Created tables and inserted data using WKT.
• Performed basic spatial queries like distance calculation and proximity searches with ST_Distance() and ST_DWithin().

PostGIS opens up a vast array of possibilities for location-aware applications!