Empowering Grid Resilience with Spatial SQL

Leveraging PostGIS for Advanced Spatial Analytics in Electric Utilities

Who Am I

Rhys Stewart

You don't need a GIS

What is

http://switchfromshapefile.org/

S

P

O

T

S

P

O

T

Single

Point

Of

Truth

SQL

  • Lingua Franca for Databases
  • Mature
  • Proven
  • Known by developers
  • Easy to integrate

Not SQL

  • May need to train staff on this access pattern
  • non-standard
  • It is just not SQL

1.

Integration with 3rd Party apps

  • Desktop
  • Middleware
  • Scripting/langugages

2.

Baseline Functionality

  • Spatial Types
  • Spatial Functions
  • Spatial Indices

3.

Enterprise Stuff

  • Scalability
  • Security
  • Support
  • Reliability

3rd

Desktop

  • QGIS
  • ArcGIS
  • Intergraph
  • AutoCAD

Party

Middleware

  • Geoserver
  • Mapnik
  • ArcGIS Server
  • FME
  • MapServer

Integration

Languages

  • Python
  • Java
  • JS
  • Go
  • Rust
  • C

〞

What about baseline functionality?

You get a function!!

You get a function!!

You get a function!!

You get a function!!

You get a function!!

You get a function!!

You get a function!!

You get a function!!

You get a function!!

EVERYBODY GETS A FUNCTION

Security

Scalability

Support

Reliability

  • Point
  • LineString
  • LinearRing
  • Polygon
  • MultiPoint
  • MultiLineString

  • MultiPolygon

  • GeometryCollection
  • PolyhedralSurface
  • Triangle
  • CompoundCurve
  • CircularString
  • Tin
  • CurvePolygon
  • MultiCurve
  • MultiSurface
  • Rasters

Geometry

Geography

How to install

?

CREATE EXTENSION postgis;

Which customers are within 1,000m of the fire?

?

SELECT phone_number FROM parcels
WHERE st_dwithin(geom, 'Point()', 1000);

What is the total length of phase AB on a given feeder?

?

SELECT sum(st_length(geom)) FROM primary
WHERE phase = 'AB' AND fdr = 'Stims - A';

Which truck is closest to the transformer?

?

SELECT truck_id FROM trucks t
ORDER BY t.geom <-> (
	SELECT geom FROM transformers
    WHERE trans_id = 42
    )
LIMIT 1;

Which bucket trucks are not currently despatched?

?

SELECT truck_id FROM trucks t
JOIN depots d ON st_contains(d.geom,t.geom);

How many bucket trucks are in each depot?

?

SELECT depot_name, count(truck_id) FROM trucks t
JOIN depots d ON st_contains(d.geom,t.geom)
GROUP BY depot_name;

5 common GIS questions asked.

No GIS software opened.

PostGIS         Functions

Linear Referencing

  • ST_LineInterpolatePoint
  • ST_3DLineInterpolatePoint
  • ST_LineInterpolatePoints
  • ST_LineLocatePoint
  • ST_LineSubstring
  • ST_LocateAlong
  • ST_LocateBetween
  • ST_LocateBetweenElevations
  • ST_InterpolatePoint
  • ST_AddMeasure

Clustering

  • ST_ClusterDBSCAN
  • ST_ClusterIntersecting
  • ST_ClusterIntersectingWin
  • ST_ClusterKMeans
  • ST_ClusterWithin
  • ST_ClusterWithinWin

How to create a spatial table?

?

CREATE TABLE streetlamps(
	polenumber text NOT NULL,
    network_id int PRIMARY KEY,
    wattage int NOT NULL,
    location_desc text,
    geom geometry(Point, 3448)
    );

Indexing is important

!

CREATE INDEX ON streetlamps USING GIST (geom);

How to create a spatial table?

?

CREATE TABLE streetlamps(
	polenumber text NOT NULL,
    network_id int PRIMARY KEY,
    wattage int NOT NULL,
    location_desc text,
    geom geometry(Point, 3448)
    );

Common Patterns.

Common Queries.

How to do a spatial join?

?

SELECT parish, streetlamp_id FROM streetlamps sl
JOIN parishes p ON st_intersects(sl.geom,p.geom);

How to do a spatial join?

?

SELECT parish, streetlamp_id FROM streetlamps sl
JOIN parishes p ON st_overlaps(sl.geom,p.geom);

How to do a spatial join?

?

SELECT parish, streetlamp_id FROM streetlamps sl
JOIN parishes p ON st_contains(sl.geom,p.geom);

/* Other spatial predicates:
st_overlaps()
st_touches()
st_covers()
st_crosses()
st_disjoint()
*/

How do i relate to that?

?

SELECT parish, streetlamp_id FROM streetlamps sl
JOIN parishes p ON st_relate(sl.geom,p.geom, '102101FF2');

Making stuff up?

?

CREATE TABLE new_xfmrs (
	polenumber text,
    x float, y float,
    geom geometry(Point, 3448));
COPY new_xfmrs(polenumber, x, y) FROM 
	'/xfmr_file.csv' WITH (FORMAT 'CSV', HEADER);
UPDATE new_xfmrs SET geom = st_setsrid(st_makepoint(x, y), 3448);

Making stuff up?

?

SELECT st_length(st_makeline(geom)) FROM truck_gps
WHERE truck_id = 42 
AND gpstime::date >= '2024-10-01'::date
ORDER BY gpstime;

Making stuff up?

?

SELECT 
	truck_id, 
    st_length(st_makeline(geom ORDER BY gpstime)) 
FROM truck_gps
WHERE gpstime::date > now() - '7 days'::interval
GROUP BY truck_id;

How does it measure up?

?

SELECT line_id FROM primaryline p
WHERE st_length(geom) < 0.02;

How does it measure up?

?

SELECT region, sum(st_area(geom)) * 9.25 
FROM yards -- $9.25 per sqm 
GROUP BY region;

How (3)does it measure up?

?

SELECT trans_id, st_3dlength(geom) FROM UHV 
ORDER BY st_3dlength(geom) DESC
LIMIT 10;

How far away is it?

?

SELECT st_distance(geom, 'Point(1 2') FROM poles
WHERE polenumber = '998457';

How far away is it...at its farthest?

?

SELECT st_maxdistance(geom, 'Point(7621 3111') FROM depots
WHERE depot = 'Filip';

How far away is it...at its farthest?

?

SELECT st_maxdistance(geom, 'Point(7621 3111') FROM depots
WHERE depot = 'Filip';

/* 
st_perimeter
st_3dperimeter
st_azimuth
st_closestpoint
st_3dclosestpoint
*/

How far away is it...at its farthest?

?

SELECT st_maxdistance(geom, 'Point(7621 3111') FROM depots
WHERE depot = 'Filip';

/* 
st_perimeter
st_3dperimeter
st_azimuth
st_closestpoint
st_3dclosestpoint
*/

United we stand

?

SELECT parish, st_union(geom) FROM communities
GROUP BY parish;

Intersection

?

SELECT 
  sum(st_length(st_intersection(pl.geom, r.geom))) 
FROM primaryline pl
JOIN regions ON st_intersects(pl.geom,r.geom)
WHERE region = 'Weyland' AND feeder = 'Palm - A';

The st_difference of two squares

?

SELECT 
  sum(st_area(st_difference(r.geom, l.geom))) 
FROM lakes
JOIN regions ON st_intersects(pl.geom,r.geom)
WHERE region = 'Weyland' AND lake = 'Miriam';

The st_symdifference of two squares

?

SELECT 
  sum(st_area(st_symdifference(r.geom, l.geom))) 
FROM lakes
JOIN regions ON st_intersects(pl.geom,r.geom)
WHERE region = 'Weyland' AND lake = 'Miriam';

Access granted.

?

SELECT st_x(geom) FROM poles
WHERE polenumber = '08431';

Access granted.

?

SELECT st_x(geom), st_y(geom) FROM poles
WHERE polenumber = '08431';

What exactly is your point?

?

SELECT depot, st_npoints(geom) FROM depots;

Where shall I start?

?

SELECT s1.line_id, xfmr_id FROM secondary s1
LEFT JOIN transformers t ON 
	st_intersects(t.geom, st_startpoint(s1.geom))
WHERE t.xfmr_id IS NULL;

Continued access

?

SELECT s1.line_id, xfmr_id FROM secondary s1
LEFT JOIN transformers t ON 
	st_intersects(t.geom, st_startpoint(s1.geom))
WHERE t.xfmr_id IS NULL;
/*
ST_M 
ST_MemSize 
ST_NRings
ST_NumGeometries 
ST_NumInteriorRings 
*/

New Edition

?

CREATE TABLE good_truck_geoms AS
SELECT truck_id, gps_time, st_force2d(geom) FROM truck_gps_data;

Keeping it snappy.

?

UPDATE TABLE primarylines
SET geom = st_snaptogrid(geom,0.1);

Keeping it snappy.

?

UPDATE TABLE primarylines
SET geom = st_snaptogrid(geom,0.1);

PostgreSQL/PostGIS and its plethora of functions can revolutionize spatial analytics in the electric utility sector. It has the ability to empower us to move beyond basic analytical GIS functions and delve into advanced spatial analysis in a scalable, robust and performant manner.

 

Bento

By rhysallister

Made with Slides.com

Bento

  • 116

More from rhysallister