PostGIS raster summation (map algebra)

PostGIS raster summation (map algebra)

I have a table of polygons representing travel time isochrones on particular days. For each origin point, there are five isochrone geometries (stored in separate rows). For each origin point, I want to rasterise the five isochrones (a binary NULL or 1), and then combine them into a single raster layer. This raster layer requires a simple map algebra: sum/5, so that each origin will in the end be associated with a single raster layer that has values in [NULL, 0.2, 0.4, 0.6, 0.8, 1] depending on how many of the constituent layers overlap. It is a probability surface.

My data is all stored in Postgres 9.3 (with PostGIS). My problem is that while I want to learn to use PostGIS raster, it seems to have a really steep learning curve, and all of the examples I can find deal with a single raster layer. In the examples, this layer is used as part of a polygon overlay, perhaps averaging the value of the raster for each polygon. I haven't found a replicable example for combining: a) vector --> raster b) map algebra; and c) GROUP BY attribute as per my first paragraph.

I'm OK using GDAL or GRASS if I have to in order to perform this task, but this seems like something PostGIS should be able to handle; it would be convenient to do so given my input data is already PostGIS geometry; and I really want to come to terms with PostGIS raster.

Some sample data structure:

areaid time date isogeom (polygon) 1000 07:15:00 2014-05-05 xxx 1000 07:15:00 2014-05-06 xxy… 1006 07:15:00 2014-05-05 zzz

I want to rasterise, group by areaid, and then perform the map algebra to come to:

areaid isorast (raster) 1000 aaa 1006 bbb

I have not been successful containing this to PostGIS. My approach has been to convert the vector to raster, dump the rasters to arrays, and perform the combination with numpy arrays via psycopg2, before writing them to a GeoTIFF (to maybe be put back in PostGIS). Not ideal, but do-able.

You will need to write your own aggregate function:

CREATE OR REPLACE function sum_raster_state(raster, raster) returns raster language sql as $f$ SELECT CASE $1 WHEN NULL THEN $2 ELSE ST_MapAlgebra( $1, $2, '([rast1] + [rast2])', NULL, 'UNION', '[rast2]', '[rast1]', NULL) END; $f$; CREATE OR REPLACE FUNCTION sum_raster_final(raster) returns raster language sql as $f$ SELECT $1 $f$; create aggregate sum_raster(raster) ( SFUNC = sum_raster_state, STYPE = raster, FINALFUNC = sum_raster_final );

afterwards you can call it like this

SELECT areaid, sum_raster(st_asraster(isogeom,… )) FROM isochrone GROUP BY areaid

This gives you the sum of all your rasters with the same area ID. You will still need to divide raster values by the number of observations for each area ID. (I didn't include it in the aggregate function. You can either do it here or afterwards using MapAlegbra)

Make sure your input rasters are all aligned, otherwise this won't work.

PostGIS raster summation (map algebra) - Geographic Information Systems

2020 SSDBM conf/ssdbm/2020 db/conf/ssdbm/ssdbm2020.html#HewasinghageAVZ20

Ralf-Detlef Kutsche Esteban Zimányi Big Data Management and Analytics - 9th European Summer School, eBISS 2019, Berlin, Germany, June 30 - July 5, 2019, Revised Selected Papers eBISS

2020 Lecture Notes in Business Information Processing 390 978-3-030-61626-7 978-3-030-61627-4 db/conf/ebiss/ebiss2019.html

2019 ADBIS (Short Papers and Workshops) conf/adbis/2019w db/conf/adbis/adbis2019w.html#KhalidZ19 Judith Awiti Esteban Zimányi An XML Interchange Format for ETL Models.

2019 ADBIS (Short Papers and Workshops) conf/adbis/2019w db/conf/adbis/adbis2019w.html#AwitiZ19 Jam Jahanzeb Khan Behan Oscar Romero 0001 Esteban Zimányi Multidimensional Integration of RDF Datasets.

2018 ER conf/er/2018 db/conf/er/er2018.html#HewasinghageVAZ18

Esteban Zimányi Business Intelligence and Big Data - 7th European Summer School, eBISS 2017, Bruxelles, Belgium, July 2-7, 2017, Tutorial Lectures eBISS

2018 Lecture Notes in Business Information Processing 324 978-3-319-96654-0 978-3-319-96655-7 db/conf/ebiss/ebiss2017.html

2017 PoEM conf/ifip8-1/2017 db/conf/ifip8-1/poem2017.html#NasiriNY0WZ17

Patrick Marcel Esteban Zimányi Business Intelligence - 6th European Summer School, eBISS 2016, Tours, France, July 3-8, 2016, Tutorial Lectures eBISS

2017 Lecture Notes in Business Information Processing 280 978-3-319-61163-1 978-3-319-61164-8 db/conf/ebiss/ebiss2016.html

2016 International Semantic Web Conference (1) conf/semweb/2016-1 db/conf/semweb/iswc2016-1.html#IbragimovHPZ16

Esteban Zimányi Alberto Abelló Business Intelligence - 5th European Summer School, eBISS 2015, Barcelona, Spain, July 5-10, 2015, Tutorial Lectures eBISS

2016 Lecture Notes in Business Information Processing 253 978-3-319-39242-4 db/conf/ebiss/ebiss2015.html

2015 ADBIS conf/adbis/2015 db/conf/adbis/adbis2015.html#GhrabRSVZ15 Waqas Ahmed 0003 Esteban Zimányi Querying Multiversion Data Warehouses.

2015 ADBIS (Short Papers and Workshops) conf/adbis/2015w db/conf/adbis/adbis2015w.html#AhmedZ15 Alberto Sabaini Esteban Zimányi Carlo Combi Extending the Multidimensional Model for Linking Cubes.

2015 JIST conf/jist/2015 db/conf/jist/jist2015.html#GurHPZ15

Esteban Zimányi Ralf-Detlef Kutsche Business Intelligence - 4th European Summer School, eBISS 2014, Berlin, Germany, July 6-11, 2014, Tutorial Lectures eBISS

2015 Lecture Notes in Business Information Processing 205 978-3-319-17550-8 db/conf/ebiss/ebiss2014.html

Esteban Zimányi Stijn Vansummeren Toon Calders Actes des 11es journées francophones sur les Entrepôts de Données et l'Analyse en Ligne, EDA 2015, Bruxelles, Belgique, 2-3 avril 2015

2015 RNTI B-11 EDA db/conf/eda/eda2015.html

2014 BIRTE conf/birte/2014 db/conf/birte/birte2014.html#IbragimovHPZ14 Waqas Ahmed 0003 Esteban Zimányi Robert Wrembel A Logical Model for Multiversion Data Warehouses.

2014 DaWaK conf/dawak/2014 db/conf/dawak/dawak2014.html#SabainiZC14

Esteban Zimányi Business Intelligence - Third European Summer School, eBISS 2013, Dagstuhl Castle, Germany, July 7-12, 2013, Tutorial Lectures 2014

Lecture Notes in Business Information Processing 172 978-3-319-05460-5 eBISS db/conf/ebiss/ebiss2013.html

2013 DaWaK conf/dawak/2013 db/conf/dawak/dawak2013.html#GhrabSJZ13 Alejandro A. Vaisman Esteban Zimányi Trajectory Data Warehouses.

2013 Mobility Data books/cu/mobility2013 db/books/collections/mobility2013.html#VaismanZ13 Chiara Renso Stefano Spaccapietra Esteban Zimányi Conclusions.

2013 Mobility Data books/cu/mobility2013 db/books/collections/mobility2013.html#RensoSZ13 Chiara Renso Stefano Spaccapietra Esteban Zimányi Mobility Data: Modeling, Management, and Understanding Mobility Data 978-1-107-02171-6

Cambridge University Press

2013 db/books/collections/mobility2013.html

Marie-Aude Aufaure Esteban Zimányi Business Intelligence - Second European Summer School, eBISS 2012, Brussels, Belgium, July 15-21, 2012, Tutorial Lectures 2013

Lecture Notes in Business Information Processing 138 978-3-642-36317-7 eBISS db/conf/ebiss/ebiss2012.html

César Sánchez Kristen Brent Venable Esteban Zimányi 2013 20th International Symposium on Temporal Representation and Reasoning, Pensacola, FL, USA, September 26-28, 2013

2013 978-1-4799-2240-6 TIME db/conf/time/time2013.html

2012 CloudCom conf/cloudcom/2012 db/conf/cloudcom/cloudcom2012.html#TranSLZ12 Zineb El Akkaoui Jose-Norberto Mazón Alejandro A. Vaisman Esteban Zimányi BPMN-Based Conceptual Modeling of ETL Processes.

2012 DaWaK conf/dawak/2012 db/conf/dawak/dawak2012.html#AkkaouiMVZ12 Ivan Jureta Stéphane Faulkner Esteban Zimányi Preface to MORE-BI 2012.

2012 TIME conf/time/2012 db/conf/time/time2012.html#Zimanyi12

Marie-Aude Aufaure Esteban Zimányi Business Intelligence - First European Summer School, eBISS 2011, Paris, France, July 3-8, 2011, Tutorial Lectures 96 2012

Lecture Notes in Business Information Processing 978-3-642-27357-5 eBISS db/conf/ebiss/ebiss2011.html

2011 CloudCom conf/cloudcom/2011 db/conf/cloudcom/cloudcom2011.html#TranSZ11 Zineb El Akkaoui Esteban Zimányi Jose-Norberto Mazón Juan Trujillo 0001 A model-driven framework for ETL process development.

2011 DOLAP conf/dolap/2011 db/conf/dolap/dolap2011.html#AkkaouiZMT11 Alejandro A. Vaisman Esteban Zimányi Data Warehouses: Next Challenges.

2011 eBISS conf/ebiss/2011 db/conf/ebiss/ebiss2011.html#VaismanZ11 Ivan Jureta Stéphane Faulkner Esteban Zimányi Preface to MORE-BI 2011.

2011 ER Workshops conf/er/2011w db/conf/er/erw2011.html#JuretaFZ11 Esteban Zimányi Roland Billen Pierre Hallot Preface to SeCoGIS 2011.

2011 1 Dagstuhl Reports 9 db/journals/dagstuhl-reports/dagstuhl-reports1.html#SchneiderVZ11 Esteban Zimányi Preface: semantic and conceptual issues in geographic information systems.

2010 14 GeoInformatica 3 db/journals/geoinformatica/geoinformatica14.html#Zimanyi10 Gabriele Pozzani Esteban Zimányi Defining Spatio-Temporal Granularities for Raster Data.

2010 EGC conf/f-egc/2010 db/conf/f-egc/egc2010.html#VerhaegenBZ10

Florian Daniel Lois M. L. Delcambre Farshad Fotouhi Irene Garrigós Giovanna Guerrini Jose-Norberto Mazón Marco Mesiti Sascha Müller 0004 Juan Trujillo 0001 Traian Marius Truta Bernhard Volz Emmanuel Waller Li Xiong 0001 Esteban Zimányi Proceedings of the 2010 EDBT/ICDT Workshops, Lausanne, Switzerland, March 22-26, 2010 978-1-60558-990-9 2010 EDBT/ICDT Workshops ACM International Conference Proceeding Series

Juan Trujillo 0001 Gillian Dobbie Hannu Kangassalo Sven Hartmann Markus Kirchberg Matti Rossi Iris Reinhartz-Berger Esteban Zimányi Flavius Frasincar Advances in Conceptual Modeling - Applications and Challenges, ER 2010 Workshops ACM-L, CMLSA, CMS, [email protected], FP-UML, SeCoGIS, WISM, Vancouver, BC, Canada, November 1-4, 2010. Proceedings 6413 2010 978-3-642-16384-5 ER Workshops Lecture Notes in Computer Science

2009 DaWaK conf/dawak/2009 db/conf/dawak/dawak2009.html#VaismanZ09 Zineb El Akkaoui Esteban Zimányi Defining ETL worfklows using BPMN and BPEL.

2009 DOLAP conf/dolap/2009 db/conf/dolap/dolap2009.html#AkkaouiZ09 Claudia Bauzer Medeiros Esteban Zimányi Preface to SeCoGIS 2009.

2009 IV conf/iv/2009 db/conf/iv/iv2009.html#LidouhSZ09 Serge Boucher Esteban Zimányi Leveraging OWL for GIS interoperability: rewards and pitfalls.

2009 Cases on Semantic Interoperability for Information Systems Integration books/igi/09/KA2009 db/books/collections/KA2009.html#BoucherZ09 Christine Parent Stefano Spaccapietra Esteban Zimányi Modularity in Databases.

2009 Modular Ontologies series/lncs/5445 db/series/lncs/lncs5445.html#ParentSZ09

Il-Yeol Song Esteban Zimányi DOLAP 2009, ACM 12th International Workshop on Data Warehousing and OLAP, Hong Kong, China, November 6, 2009, Proceedings DOLAP

978-1-60558-801-8 2009 db/conf/dolap/dolap2009.html

Stefano Spaccapietra Esteban Zimányi Il-Yeol Song Journal on Data Semantics XIII 5530 2009 978-3-642-03097-0 Journal on Data Semantics XIII Lecture Notes in Computer Science

2009 Encyclopedia of Data Warehousing and Mining reference/dataware/2009 db/reference/dataware/dataware2009.html#MalinowskiZ09 Elzbieta Malinowski Esteban Zimányi Extending a Conceptual Multidimensional Model for Representing Spatial Data.

2009 Encyclopedia of Data Warehousing and Mining reference/dataware/2009 db/reference/dataware/dataware2009.html#MalinowskiZ09a Elzbieta Malinowski Esteban Zimányi Temporal Extension for a Conceptual Multidimensional Model.

2009 Encyclopedia of Data Warehousing and Mining reference/dataware/2009 db/reference/dataware/dataware2009.html#MalinowskiZ09b Christine Parent Stefano Spaccapietra Christelle Vangenot Esteban Zimányi Multiple Representation Modeling.

2008 64 Data Knowl. Eng. 1 db/journals/dke/dke64.html#MalinowskiZ08 Esteban Zimányi Christophe Claramunt Preface to SeCoGIS 2008.

2008 ER Workshops conf/er/2008w db/conf/er/erw2008.html#ZimanyiC08

Il-Yeol Song Mario Piattini Yi-Ping Phoebe Chen Sven Hartmann Fabio Grandi 0001 Juan Trujillo 0001 Andreas L. Opdahl Fernando Ferri Patrizia Grifoni Maria Chiara Caschera Colette Rolland Carson Woo Camille Salinesi Esteban Zimányi Christophe Claramunt Flavius Frasincar Geert-Jan Houben Philippe Thiran Advances in Conceptual Modeling - Challenges and Opportunities, ER 2008 Workshops CMLSA, ECDM, FP-UML, M2AS, RIGiM, SeCoGIS, WISM, Barcelona Spain, October 20-23, 2008. Proceedings 5232 2008 978-3-540-87990-9 ER Workshops Lecture Notes in Computer Science

2007 11 GeoInformatica 4 db/journals/geoinformatica/geoinformatica11.html#MalinowskiZ07 Esteban Zimányi Michela Bertolotto Preface to SeCoGIS 2007.

2007 conf/iceis/2007-1 ICEIS (1) db/conf/iceis/iceis2007-1.html#MalinowskiZ07

Jean-Luc Hainaut Elke A. Rundensteiner Markus Kirchberg Michela Bertolotto Mathias Brochhausen Yi-Ping Phoebe Chen Samira Si-Said Cherfi Martin Doerr Hyoil Han Sven Hartmann Jeffrey Parsons Geert Poels Colette Rolland Juan Trujillo 0001 Eric S. K. Yu Esteban Zimányi Advances in Conceptual Modeling - Foundations and Applications, ER 2007 Workshops CMLSA, FP-UML, ONISW, QoIS, RIGiM,SeCoGIS, Auckland, New Zealand, November 5-9, 2007, Proceedings ER Workshops Lecture Notes in Computer Science 4802

2007 978-3-540-76291-1 db/conf/er/erw2007.html

2006 Active Conceptual Modeling of Learning conf/er/2006acml db/conf/er/acml2006.html#SpaccapietraPZ06 Elzbieta Malinowski Esteban Zimányi Inclusion of Time-Varying Measures in Temporal Data Warehouses.

2006 conf/nldb/2006 NLDB db/conf/nldb/nldb2006.html#NorguetTBZ06 Esteban Zimányi SeBGIS 2006 PC Co-chairs' Message.

2005 conf/cimca/2005 CIMCA/IAWTIC db/conf/cimca/cimca2005-2.html#NorguetZ05 Esteban Zimányi Emmanuel Stefanakis SeBGIS 2005 PC Co-chairs' Message.

2005 conf/otm/2005-1 OTM Workshops db/conf/otm/otm2005-1.html#ZimanyiM05

Robert Meersman Zahir Tari Pilar Herrero Gonzalo Méndez 0001 Lawrence Cavedon David B. Martin Annika Hinze George Buchanan María S. Pérez Víctor Robles Jan Humble Antonia Albani Jan L. G. Dietz Hervé Panetto Monica Scannapieco Terry A. Halpin Peter Spyns Johannes Maria Zaha Esteban Zimányi Emmanuel Stefanakis Tharam S. Dillon Ling Feng Mustafa Jarrar Jos Lehmann Aldo de Moor Erik Duval Lora Aroyo On the Move to Meaningful Internet Systems 2005: OTM 2005 Workshops, OTM Confederated International Workshops and Posters, AWeSOMe, CAMS, GADA, MIOS+INTEROP, ORM, PhDS, SeBGIS, SWWS, and WOSE 2005, Agia Napa, Cyprus, October 31 - November 4, 2005, Proceedings 3762 2005 3-540-29739-1 OTM Workshops Lecture Notes in Computer Science

Stefano Spaccapietra Esteban Zimányi Journal on Data Semantics III 3534 2005 3-540-26225-3 Journal on Data Semantics III Lecture Notes in Computer Science

2002 14 IEEE Trans. Knowl. Data Eng. 5 db/journals/tkde/tkde14.html#DahchourPZ02 Mohamed Dahchour Alain Pirotte 0001 Esteban Zimányi A Generic Role Model for Dynamic Objects.

1999 conf/dante/1999 DANTE db/conf/dante/dante1999.html#SpaccapietraVPZ99 Christine Parent Stefano Spaccapietra Esteban Zimányi Spatio-Temporal Conceptual Models: Data Structures + Space + Time.

CIKM1998/P432.pdf 1998 conf/cikm/98 CIKM db/conf/cikm/cikm98.html#SpaccapietraPZ98 Esteban Zimányi Query Evaluation in Probabilistic Relational Databases.

1996 books/kl/motroS96/MS1996 Uncertainty Management in Information Systems db/books/collections/MotroS96.html#ZimanyiP96 Manuel Kolp Esteban Zimányi Relational Database Design Using an ER Approach and Prolog.