PostGIS related extensions

Regina Obe

Buy our books! http://postgis.us/page_buy_book

Our latest books

(50% discount mlobe3)

(35% all books from Locate Press)

PostGIS related extensions

pgRouting

Uses graph algorithms: edges have costs, nodes connect edges.

• Find shortest path consider costs (road network, one way, toll price etc).
• Maximum service area
• Decision trees - compute most favorable path given where you are in the tree.

MobilityDb

Analysis and managing spatial movement in a compact way. Follows the OGC Moving Features spec. Extends postgis geometry and geography types with time elements. Raw data often comes in General Transit Feed Specification (GTFS) format and GPX (that stuff from your GPS device). Extends postgis geometry and geography types to form temporal spatial types: tgeompoint, stbox, geodstbox, periodset and several others.

• Store massive amounts of movement data in a compact way.
• What's the average speed for X part of the path of a trip.
• Integration with QGIS for viewing movement data.

ogr_fdw

Links external (sometimes spatial) data in PostGIS/PostgreSQL format. It is an abstraction: this data over there is a table in my database.

• If you have spatial data it can link it.
• If you have non-spatial data, it can link it.
• If you have no clue what this random file someone gave you is, it can probably link it.

PostGIS models space in many ways

Flat, Round, Matrix, Declarative

PostGIS geometry type (Flat)

The flat space model. Space is a cartesian grid. Supports drawing of linestrings, polygons, 3D polygons, points, 3d points, collections of polygons, points, linestrings, Polyhedral Surfaces, and TINS

Basic geometric types

Polyhedral Surface

Triangulated Irregular Network (TIN)

PostGIS geography type (round)

Model of space as spheroid. Takes into consideration the earth or any given planet whose spatial reference is defined in spatial_ref_sys table.

Geodetic (Geography) 4326 (WGS 84 Lon Lat) in geography

PostGIS raster type (matrix)

Model of space as a flat numeric matrix (with cells (called pixels) that have values (on) or don't have values (off))

• Elevation
• Soil
• Weather
• Fire
• Aerial

PostGIS topogeometry type (declarative)

Defined in SQL/MM Topology-Geometry specs. Spatialite has it too. Topology partitions space into edges, nodes, and faces. Then it says this thing called topogeometry is space composed of these edges, nodes, faces and other topogeometries (which are again just a bunch of edges, nodes,and faces). If two topogeometries have the same set of (edges,nodes,faces) then they are the same.

(1,1,2,3) -> topology_id, layer_id, id, type

SELECT topo::geometry AS geom, (topo).* 
FROM  some_topo_table;

Installing

CREATE SCHEMA postgis;
ALTER DATABASE gisdb set search_path=public,postgis;
-- included with postgis
CREATE EXTENSION postgis SCHEMA postgis; --only vector in 3.0
CREATE EXTENSION postgis_raster SCHEMA postgis; -- raster separate in 3.0
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch; -- needed by geocoder
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_sfcgal SCHEMA postgis;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us; -- lex and rules

-- packaged separately
CREATE EXTENSION ogr_fdw SCHEMA postgis;
CREATE EXTENSION pgrouting SCHEMA postgis;
CREATE EXTENSION mobilitydb SCHEMA postgis;
CREATE EXTENSION pointcloud SCHEMA postgis;
CREATE EXTENSION pointcloud_postgis SCHEMA postgis;

Live demo in words

• postgis_topology
• pgrouting
• postgis_raster
• postgis

ALTER DATABASE gisdb SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';
DROP TABLE IF EXISTS happy_raster;

WITH the_words AS
        (SELECT pos, ST_Translate(
			ST_LettersAsGeometry(word, 'kankin'), 0, 
				-pos*100) As geom
          FROM  (VALUES 
			(0,'HAPPY'),
			(1,'POSTGIS'),
			(2,'DAY')  ) AS my(pos, word)
        )
 , canvas
    AS (SELECT ST_MakeEmptyRaster((ST_XMAX(ext) - ST_XMin(ext))::integer, 
        (ST_YMAx(ext) - ST_YMin(ext))::integer,0, 0, 1, -1, 0, 0, 0) As rast
        FROM (SELECT ST_Extent(geom) As ext FROM the_words) As foo
            )
SELECT 1 AS id, ST_Union(ST_AsRaster(geom
     , canvas.rast
     , ARRAY['8BUI','8BUI', '8BUI']::text[]
     , ARRAY[pos*75,6,0], ARRAY[0,0,0]) 
        ) AS rast
    INTO happy_raster
     FROM  the_words CROSS JOIN canvas;

Copy and paste in address bar of browser to see the image

SELECT 'data:image/png;base64,' || encode(ST_AsPNG(
    ST_Resize(rast, 0.3,0.3)),'base64')
     FROM  happy_raster;
					
				

FIN

Buy our books https://postgis.us