PostGIS Spatial Extension for PostgreSQL
Database Integration by PostGIS Project / OSGeo
Description
PostGIS extends Supabase PostgreSQL with geospatial data types and spatial indexing, enabling efficient geographic queries for the peer mentor map view. Used to store mentor location data, perform proximity searches for assignment matching, and support Norges Blindeforbund's requirement for geographic distribution of mentors across large fylker (counties) for optimal assignment.
Detailed Analysis
PostGIS extends the Supabase PostgreSQL database with geospatial capabilities, enabling the geographic mentor map view that is central to Norges Blindeforbund's large-fylke assignment workflow. Without spatial indexing and proximity queries, efficient matching of blind and visually impaired individuals with the nearest available certified peer mentor across Norway's large counties would require manual geographic reasoning by coordinators. PostGIS is a free extension included in the Supabase subscription — there is no additional licensing cost, only modest additional database storage for spatial indexes. Peer mentor location data is handled with full GDPR compliance: location is collected only with explicit user consent, precision is reduced to municipality level to avoid exact GPS tracking, and Row Level Security restricts visibility so mentors see only their own location while coordinators see only mentors in their chapter.
A Data Processing Agreement is required given that location data constitutes personal data under GDPR. The fallback to a manual region/fylke filter ensures the assignment workflow remains functional even when location permission is not granted.
PostGIS integration requires one setup step — enabling the extension on the Supabase project via CREATE EXTENSION postgis — and appropriate database schema design including a geography column type and spatial index on the location table. Six components depend on the spatial layer: the PostGISSpatialAdapter, MentorLocationRepository, MentorLocationService, LocationConsentService, GeographicDistributionService, and LocationPrivacyConfig. Development must include RLS policy design to enforce the three-tier access model (mentor: own location only; coordinator: chapter mentors; admin: all). The location consent flow must be implemented and tested before any location data is stored, and the GDPR DPA must be in place before production launch.
Testing should cover proximity search latency (< 300ms target for 100km radius), spatial index performance under expected data volumes, and the fallback list view when location permission is denied. Monitoring includes spatial query latency metrics and an alert if p99 exceeds 1 second. Ongoing maintenance is low — PostGIS is managed by Supabase and requires no manual updates. The main operational risk is location data coverage: map usefulness depends on mentors actively sharing location, so consent UX quality directly affects feature value.
PostGIS 3.x is enabled as a managed extension on the Supabase PostgreSQL instance. Location data is stored using the geography column type (SRID 4326) with a GIST spatial index for efficient proximity queries. The ST_DWithin or ST_Distance functions are used for proximity search, targeting < 300ms for a 100km radius query. Authentication uses the standard Supabase JWT with RLS policies enforcing three access tiers: peer mentors may read/write their own location row only; coordinators may read all mentor locations within their chapter; admins have full read access.
The supabase_flutter SDK handles connection and JWT propagation automatically. The PostGISSpatialAdapter abstracts raw spatial SQL, with the MentorLocationRepository exposing typed Dart interfaces. Location precision is intentionally rounded to municipality level before storage — the LocationPrivacyConfig component manages this rounding. Spatial query timeouts fall back to the non-spatial contact list via the MentorListFallback component.
The LocationConsentService gates all location writes behind explicit user consent, with consent state persisted in the database. Spatial index degradation is monitored with an alert threshold, and location query p99 latency is tracked as a key performance metric. No rate limits apply at the PostGIS layer — throughput is bounded by Supabase compute tier.
Using Components (6)
Dependencies (3)
Authentication
| Type | Api Key |
| Requirements | PostGIS extension enabled on Supabase project, Authenticated Supabase session JWT, RLS policies on location table to restrict access by role |
| Scopes | read (peer mentors own location)write (peer mentors own location)read all (coordinators and above) |
Configuration
Error Handling
Monitoring
Performance
| Latency | < 300ms for proximity search within 100km radius |
| Availability | Same as Supabase PostgreSQL — 99.9% |
Cost Implications
| Pricing Model | Included in Supabase subscription — PostGIS is a free extension |