-
Notifications
You must be signed in to change notification settings - Fork 20
/
postgis_addons.sql
3968 lines (3786 loc) · 199 KB
/
postgis_addons.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-------------------------------------------------------------------------------
-- PostGIS PL/pgSQL Add-ons - Main installation file
-- Version 1.38 for PostGIS 2.1.x and PostgreSQL 9.x
-- http://github.com/pedrogit/postgisaddons
--
-- This is free software; you can redistribute and/or modify it under
-- the terms of the GNU General Public Licence. See the COPYING file.
--
-- Copyright (C) 2013-2017 Pierre Racine <[email protected]>.
--
-------------------------------------------------------------------------------
--
-- The PostGIS add-ons attempt to gather, in a single .sql file, useful and
-- generic user contributed PL/pgSQL functions and to provide a fast and Agile
-- release cycle. Files will be tagged with an incremental version number
-- for every significant change or addition. They should ALWAYS be left in a
-- stable, installable and tested state.
--
-- Signatures and return values for existing functions should not change from
-- minor revision to minor revision. New functions might be added though.
--
-- PostGIS PL/pgSQL Add-ons tries to make life as easy as possible for users
-- wishing to contribute their functions. This is why it limits itself to
-- only three files: the main function executable file, a test file and an
-- unsinstall file. All functions are documented inside the main function file
-- (this file).
--
-- To be included, a function:
--
-- - must be written in pure PL/pgSQL or SQL code (no C or any compilable
-- code),
-- - must be generic enough to be useful to other PostGIS users,
-- - must follow functions and variables naming and indentation conventions
-- already in use in the files,
-- - must be documented according to the rules defined below in this file,
-- - must be accompagned by a series of test in the postgis_addons_test.sql
-- file,
-- - must be accompagned by the appropriate DROP statements in the
-- expostgis_addons_uninstall.sql file.
--
-- You must also accept to release your work under the same licence already
-- in use for this product.
--
-------------------------------------------------------------------------------
--
-- File description
--
-- - postgis_addons.sql Main redistributable file containing all
-- the functions.
-- - postgis_addons_uninstall.sql Uninstallation file.
-- - postgis_addons_test.sql Self contained test file to be executed
-- after installation and before any commit
-- of the mainfile.
--
-------------------------------------------------------------------------------
--
-- Documentation
--
-- Each function must be documented directly in the postgis_addons.sql file
-- just before the definition of the function.
--
-- Mandatory documentation elements for each function:
--
-- - Function name,
-- - Parameters listing and description of each parameter,
-- - Description,
-- - A self contained example,
-- - A typical, not necessarily self contained, example,
-- - Links to more examples on the web (blog post, etc...),
-- - Authors names with emails,
-- - Date and version of availability (date of inclusion in PostGIS Add-ons).
--
-- A short description of each new function should also be provided at the
-- beginning of the file (in the section below).
--
-------------------------------------------------------------------------------
-- Function list
--
-- ST_DeleteBand - Removes a band from a raster. Band number starts at 1.
--
-- ST_CreateIndexRaster - Creates a new raster as an index grid.
--
-- ST_RandomPoints - Generates points located randomly inside a geometry.
--
-- ST_ColumnExists - Returns true if a column exist in a table.
--
-- ST_HasBasicIndex - Returns true if a table column has at least one index
-- defined.
--
-- ST_AddUniqueID - Adds a column to a table and fill it with a unique
-- integer starting at 1.
--
-- ST_AreaWeightedSummaryStats - Aggregate function computing statistics
-- on a series of intersected values weighted
-- by the area of the corresponding geometry.
--
-- ST_ExtractToRaster - Compute a raster band by extracting values for the
-- centroid or the footprint of each pixel from a global
-- geometry coverage using different methods like count,
-- min, max, mean, value of biggest geometry or area
-- weighted mean of values.
--
-- ST_GlobalRasterUnion - Build a new raster by extracting all the pixel
-- different values from a global raster coverage
-- using methods like count, min, max, mean, stddev
-- and range. Similar and slower but more flexible
-- than ST_Union.
--
-- ST_BufferedUnion - Alternative to ST_Union making a buffer around each
-- geometry before unioning and removing it afterward.
-- Used when ST_Union leaves internal undesirable vertexes
-- after a complex union or when wanting to remove holes
-- from the resulting union.
--
-- ST_NBiggestExteriorRings - Returns the n biggest exterior rings of the
-- provided geometry based on their area or their
-- number of vertex.
--
-- ST_BufferedSmooth - Returns a smoothed version of the geometry. The
-- smoothing is done by making a buffer around the
-- geometry and removing it afterward.
--
-- ST_DifferenceAgg - Returns the first geometry after having removed all the
-- subsequent geometries in the aggregate. Used to remove
-- overlaps in a geometry table.
--
-- ST_TrimMulti - Returns a multigeometry from which simple geometries having
-- an area smaller than the tolerance parameter have been
-- removed.
--
-- ST_SplitAgg - Returns the first geometry as a set of geometries after
-- being split by all the second geometries being part of the
-- aggregate.
--
-- ST_ColumnIsUnique - Returns true if all the values in this column are
-- unique.
--
-- ST_GeoTableSummary - Returns a table summarysing a geometry table. Helps
-- identify anomalies in geometry tables like duplicates,
-- overlaps and very complex or very small geometries.
--
-- ST_SplitByGrid - Set function returning a geometry splitted in multiple
-- parts by a specified grid.
--
-- ST_Histogram - Set function returnings a table representing an histogram
-- of the values for the specifed column.
--
-- ST_RemoveOverlaps - Remove overlaps among an array or an aggregate of
-- polygons.
--
-------------------------------------------------------------------------------
-- Begin Types Definitions...
----------------------------------------------------------
-- New type to pass extra initial parameters to final aggregate functions
CREATE TYPE geomvaltxt AS (
geom geometry,
val double precision,
txt text
);
-------------------------------------------------------------------------------
-- Begin Function Definitions...
-------------------------------------------------------------------------------
-- ST_DeleteBand
--
-- rast raster - Raster in which to remove a band.
-- band int - Number of the band to remove.
--
-- RETURNS raster
--
-- Removes a band from a raster. Band number starts at 1.
-----------------------------------------------------------
-- Self contained example:
--
-- SELECT ST_NumBands(ST_DeleteBand(rast, 2)) nb1, ST_NumBands(rast) nb2
-- FROM (SELECT ST_AddBand(ST_MakeEmptyRaster(10, 10, 0, 0, 1),
-- ARRAY[ROW(NULL, '8BUI', 255, 0),
-- ROW(NULL, '16BUI', 1, 2)]::addbandarg[]) rast
-- ) foo;
--
-- Typical example removing a band from an existing raster table:
--
-- UPDATE rastertable SET rast = ST_DeleteBand(rast, 2);
-----------------------------------------------------------
-- Pierre Racine ([email protected])
-- 26/09/2013 v. 1.4
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION ST_DeleteBand(
rast raster,
band int
)
RETURNS raster AS $$
DECLARE
numband int := ST_NumBands(rast);
bandarray int[];
BEGIN
IF rast IS NULL THEN
RETURN NULL;
END IF;
IF band IS NULL OR band < 1 OR band > numband THEN
RETURN rast;
END IF;
IF band = 1 AND numband = 1 THEN
RETURN ST_MakeEmptyRaster(rast);
END IF;
-- Construct the array of band to extract skipping the band to delete
SELECT array_agg(i) INTO bandarray
FROM generate_series(1, numband) i
WHERE i != band;
RETURN ST_Band(rast, bandarray);
END;
$$ LANGUAGE plpgsql VOLATILE;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- ST_CreateIndexRaster
--
-- rast raster - Raster from which are copied the metadata to build
-- the new, index raster. Generally created from
-- scratch with ST_MakeEmptyRaster().
-- pixeltype text - Pixel type of the new index raster. The default is
-- 32BUI.
-- startvalue int - The first value assigned to the index raster. The
-- default is 0.
-- incwithx boolean - When true (default), indexes increase with the x
-- raster coordinate of the pixel.
-- incwithy boolean - When true (default), indexes increase with the y
-- raster coordinate of the pixel. (When scaley is
-- negative, indexes decrease with y.)
-- rowsfirst boolean - When true (default), indexes increase vertically
-- first, and then horizontally.
-- rowscanorder boolean - When true (default), indexes increase always in
-- the same direction (row scan). When false indexes
-- increase alternatively in direction and then in
-- the other direction (row-prime scan).
-- colinc int - Colums increment value. Must be greater than
-- rowinc * (ST_Height() - 1) when columnfirst is true.
-- rowinc int - Row increment value. Must be greater than colinc *
-- (ST_Width() - 1) when columnfirst is false.
--
-- RETURNS raster
--
-- Creates a new raster as an index grid.
-----------------------------------------------------------
-- Self contained example:
--
-- SELECT (gvxy).geom, (gvxy).val
-- FROM ST_PixelAsPolygons(ST_CreateIndexRaster(ST_MakeEmptyRaster(10, 10, 0, 0, 1, 1, 0, 0), '8BUI')) gvxy;
--
-- Typical example creating a z scanned raster with rows incrementing by 10 and columns incrementing by 1000:
--
-- CREATE TABLE newraster AS
-- SELECT ST_CreateIndexRaster(ST_MakeEmptyRaster(10, 10, 0, 0, 1, 1, 0, 0), '32BUI', 0, true, true, true, false, 1000, 10) rast;
-----------------------------------------------------------
-- Pierre Racine ([email protected])
-- 27/09/2013 added in v1.5
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION ST_CreateIndexRaster(
rast raster,
pixeltype text DEFAULT '32BUI',
startvalue int DEFAULT 0,
incwithx boolean DEFAULT true,
incwithy boolean DEFAULT true,
rowsfirst boolean DEFAULT true,
rowscanorder boolean DEFAULT true,
colinc int DEFAULT NULL,
rowinc int DEFAULT NULL
)
RETURNS raster AS $$
DECLARE
newraster raster := ST_AddBand(ST_MakeEmptyRaster(rast), pixeltype);
x int;
y int;
w int := ST_Width(newraster);
h int := ST_Height(newraster);
rowincx int := Coalesce(rowinc, w);
colincx int := Coalesce(colinc, h);
rowincy int := Coalesce(rowinc, 1);
colincy int := Coalesce(colinc, 1);
xdir int := CASE WHEN Coalesce(incwithx, true) THEN 1 ELSE w END;
ydir int := CASE WHEN Coalesce(incwithy, true) THEN 1 ELSE h END;
xdflag int := Coalesce(incwithx::int, 1);
ydflag int := Coalesce(incwithy::int, 1);
rsflag int := Coalesce(rowscanorder::int, 1);
newstartvalue int := Coalesce(startvalue, 0);
newrowsfirst boolean := Coalesce(rowsfirst, true);
BEGIN
IF newrowsfirst THEN
IF colincx <= (h - 1) * rowincy THEN
RAISE EXCEPTION 'Column increment (now %) must be greater than the number of index on one column (now % pixel x % = %)...', colincx, h - 1, rowincy, (h - 1) * rowincy;
END IF;
--RAISE NOTICE 'abs([rast.x] - %) * % + abs([rast.y] - (% ^ ((abs([rast.x] - % + 1) % 2) | % # ))::int) * % + %', xdir::text, colincx::text, h::text, xdir::text, rsflag::text, ydflag::text, rowincy::text, newstartvalue::text;
newraster = ST_SetBandNodataValue(
ST_MapAlgebra(newraster,
pixeltype,
'abs([rast.x] - ' || xdir::text || ') * ' || colincx::text ||
' + abs([rast.y] - (' || h::text || ' ^ ((abs([rast.x] - ' ||
xdir::text || ' + 1) % 2) | ' || rsflag::text || ' # ' ||
ydflag::text || '))::int) * ' || rowincy::text || ' + ' || newstartvalue::text),
ST_BandNodataValue(newraster)
);
ELSE
IF rowincx <= (w - 1) * colincy THEN
RAISE EXCEPTION 'Row increment (now %) must be greater than the number of index on one row (now % pixel x % = %)...', rowincx, w - 1, colincy, (w - 1) * colincy;
END IF;
newraster = ST_SetBandNodataValue(
ST_MapAlgebra(newraster,
pixeltype,
'abs([rast.x] - (' || w::text || ' ^ ((abs([rast.y] - ' ||
ydir::text || ' + 1) % 2) | ' || rsflag::text || ' # ' ||
xdflag::text || '))::int) * ' || colincy::text || ' + abs([rast.y] - ' ||
ydir::text || ') * ' || rowincx::text || ' + ' || newstartvalue::text),
ST_BandNodataValue(newraster)
);
END IF;
RETURN newraster;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- ST_RandomPoints
--
-- geom geometry - Geometry in which to create the random points. Should be a
-- polygon or a multipolygon.
-- nb int - Number of random points to create.
-- seed numeric - Value between -1.0 and 1.0, inclusive, setting the seek if
-- repeatable results are desired. Default to NULL.
--
-- RETURNS SET OF geometry(point)
--
-- Generates points located randomly inside a geometry.
-----------------------------------------------------------
-- Self contained example creating 100 points:
--
-- SELECT ST_RandomPoints(ST_GeomFromText('POLYGON((-73 48,-72 49,-71 48,-69 49,-69 48,-71 47,-73 48))'), 1000, 0.5) geom;
--
-- Typical example creating a table of 1000 points inside the union of all the
-- geometries of a table:
--
-- CREATE TABLE random_points AS
-- SELECT ST_RandomPoints(ST_Union(geom), 1000) geom FROM geomtable;
-----------------------------------------------------------
-- Pierre Racine ([email protected])
-- Mathieu Basille <[email protected]>
-- 10/01/2013 added in v1.6
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION ST_RandomPoints(
geom geometry,
nb integer,
seed numeric DEFAULT NULL
)
RETURNS SETOF geometry AS $$
DECLARE
pt geometry;
xmin float8;
xmax float8;
ymin float8;
ymax float8;
xrange float8;
yrange float8;
srid int;
count integer := 0;
gtype text;
BEGIN
SELECT ST_GeometryType(geom) INTO gtype;
-- Make sure the geometry is some kind of polygon
IF (gtype IS NULL OR (gtype != 'ST_Polygon') AND (gtype != 'ST_MultiPolygon')) THEN
RAISE NOTICE 'Attempting to get random points in a non polygon geometry';
RETURN NEXT NULL;
RETURN;
END IF;
-- Compute the extent
SELECT ST_XMin(geom), ST_XMax(geom), ST_YMin(geom), ST_YMax(geom), ST_SRID(geom)
INTO xmin, xmax, ymin, ymax, srid;
-- and the range of the extent
SELECT xmax - xmin, ymax - ymin
INTO xrange, yrange;
-- Set the seed if provided
IF seed IS NOT NULL THEN
PERFORM setseed(seed);
END IF;
-- Find valid points one after the other checking if they are inside the polygon
WHILE count < nb LOOP
SELECT ST_SetSRID(ST_MakePoint(xmin + xrange * random(), ymin + yrange * random()), srid)
INTO pt;
IF ST_Contains(geom, pt) THEN
count := count + 1;
RETURN NEXT pt;
END IF;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- ST_ColumnExists
--
-- schemaname name - Name of the schema containing the table in which to
-- check for the existance of a column.
-- tablename name - Name of the table in which to check for the existance of
-- a column.
-- columnname name - Name of the column to check for the existence of.
--
-- RETURNS boolean
--
-- Returns true if a column exist in a table. Mainly defined to be used by
-- ST_AddUniqueID().
-----------------------------------------------------------
-- Self contained example:
--
-- SELECT ST_ColumnExists('public', 'spatial_ref_sys', 'srid') ;
-----------------------------------------------------------
-- Pierre Racine ([email protected])
-- 10/02/2013 added in v1.7
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION ST_ColumnExists(
schemaname name,
tablename name,
columnname name
)
RETURNS BOOLEAN AS $$
DECLARE
BEGIN
PERFORM 1 FROM information_schema.COLUMNS
WHERE lower(table_schema) = lower(schemaname) AND lower(table_name) = lower(tablename) AND lower(column_name) = lower(columnname);
RETURN FOUND;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;
-----------------------------------------------------------
-- ST_ColumnExists variant defaulting to the 'public' schemaname
CREATE OR REPLACE FUNCTION ST_ColumnExists(
tablename name,
columnname name
)
RETURNS BOOLEAN AS $$
SELECT ST_ColumnExists('public', $1, $2)
$$ LANGUAGE sql VOLATILE STRICT;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- ST_HasBasicIndex
--
-- schemaname name - Name of the schema containing the table for which to
-- check for the existance of an index.
-- tablename name - Name of the table for which to check for the existance
-- of an index.
-- columnname name - Name of the column to check for the existence of an
-- index.
-- idxstring - Search for indexes containing idxstring in their names.
-- Default to NULL.
--
-- RETURNS boolean
--
-- Returns true if a table column has an index defined.
-----------------------------------------------------------
-- Self contained example:
--
-- SELECT ST_HasBasicIndex('public', 'spatial_ref_sys', 'srid') ;
-----------------------------------------------------------
-- Pierre Racine ([email protected])
-- 08/06/2017 added in v1.25
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION ST_HasBasicIndex(
schemaname name,
tablename name,
columnname name,
idxstring text
)
RETURNS boolean AS $$
DECLARE
query text;
coltype text;
hasindex boolean := FALSE;
BEGIN
IF schemaname IS NULL OR schemaname = '' OR tablename IS NULL OR tablename = '' THEN
RETURN NULL;
END IF;
-- Check if schemaname is not actually a table name and idxstring actually a column name.
-- That's the only way to support a three parameters variant taking a schemaname, a tablename and a columnname
IF ST_ColumnExists(tablename, columnname, idxstring) THEN
schemaname = tablename;
tablename = columnname;
columnname = idxstring;
idxstring = NULL;
END IF;
IF (columnname IS NULL OR columnname = '') AND (idxstring IS NULL OR idxstring = '') THEN
RETURN NULL;
END IF;
IF NOT columnname IS NULL AND columnname != '' AND ST_ColumnExists(schemaname, tablename, columnname) THEN
-- Determine the type of the column
query := 'SELECT typname
FROM pg_namespace
LEFT JOIN pg_class ON (pg_namespace.oid = pg_class.relnamespace)
LEFT JOIN pg_attribute ON (pg_attribute.attrelid = pg_class.oid)
LEFT JOIN pg_type ON (pg_type.oid = pg_attribute.atttypid)
WHERE lower(nspname) = lower(''' || schemaname || ''') AND lower(relname) = lower(''' || tablename || ''') AND lower(attname) = lower(''' || columnname || ''');';
EXECUTE QUERY query INTO coltype;
END IF;
IF coltype IS NULL AND (idxstring IS NULL OR idxstring = '') THEN
RETURN NULL;
ELSIF coltype = 'raster' THEN
-- When column type is RASTER we ignore the column name and
-- only check if the type of the index is gist since it is a functional
-- index and it would be hard to check on which column it is applied
query := 'SELECT TRUE
FROM pg_index
LEFT OUTER JOIN pg_class relclass ON (relclass.oid = pg_index.indrelid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = relclass.relnamespace)
LEFT OUTER JOIN pg_class idxclass ON (idxclass.oid = pg_index.indexrelid)
LEFT OUTER JOIN pg_am ON (pg_am.oid = idxclass.relam)
WHERE relclass.relkind = ''r'' AND amname = ''gist''
AND lower(nspname) = lower(''' || schemaname || ''') AND lower(relclass.relname) = lower(''' || tablename || ''')';
IF NOT idxstring IS NULL THEN
query := query || ' AND lower(idxclass.relname) LIKE lower(''%' || idxstring || '%'');';
END IF;
EXECUTE QUERY query INTO hasindex;
ELSE
-- Otherwise we check for an index on the right column
query := 'SELECT TRUE
FROM pg_index
LEFT OUTER JOIN pg_class relclass ON (relclass.oid = pg_index.indrelid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = relclass.relnamespace)
LEFT OUTER JOIN pg_class idxclass ON (idxclass.oid = pg_index.indexrelid)
--LEFT OUTER JOIN pg_am ON (pg_am.oid = idxclass.relam)
LEFT OUTER JOIN pg_attribute ON (pg_attribute.attrelid = relclass.oid AND indkey[0] = attnum)
WHERE relclass.relkind = ''r''
AND lower(nspname) = lower(''' || schemaname || ''') AND lower(relclass.relname) = lower(''' || tablename || ''')';
IF NOT idxstring IS NULL THEN
query := query || ' AND lower(idxclass.relname) LIKE lower(''%' || idxstring || '%'')';
END IF;
IF NOT columnname IS NULL THEN
query := query || ' AND indkey[0] != 0 AND lower(attname) = lower(''' || columnname || ''')';
END IF;
--RAISE NOTICE 'query = %', query;
EXECUTE QUERY query INTO hasindex;
END IF;
IF hasindex IS NULL THEN
hasindex = FALSE;
END IF;
RETURN hasindex;
END;
$$ LANGUAGE plpgsql VOLATILE;
-----------------------------------------------------------
-- ST_HasBasicIndex variant defaulting to the 'public' schemaname
-- or taking a schemaname, a tablename and a columnname
CREATE OR REPLACE FUNCTION ST_HasBasicIndex(
tablename name,
columnname name,
idxstring text
)
RETURNS BOOLEAN AS $$
SELECT ST_HasBasicIndex('public', $1, $2, $3)
$$ LANGUAGE sql VOLATILE;
-----------------------------------------------------------
-- ST_HasBasicIndex variant defaulting to the 'public' schemaname
CREATE OR REPLACE FUNCTION ST_HasBasicIndex(
tablename name,
columnname name
)
RETURNS BOOLEAN AS $$
SELECT ST_HasBasicIndex('public', $1, $2, NULL)
$$ LANGUAGE sql VOLATILE;
-----------------------------------------------------------
-------------------------------------------------------------------------------
-- ST_AddUniqueID
--
-- schemaname name - Name of the schema containing the table in which
-- to check for the existance of a column.
-- tablename name - Name of the table in which to check for the
-- existance of a column.
-- columnname name - Name of the new id column to check for the
-- existence of.
-- replacecolumn boolean - If set to true, drop and replace the new id column
-- if it already exists. Default to false.
-- indexit boolean - If set to true, create an index on the new id
-- column. Default to true.
--
-- RETURNS boolean
--
-- Adds a column to a table and fill it with a unique integer starting at 1.
-- Returns true if the operation succeeded, false otherwise.
--
-- This is useful when you don't want to create a new table for whatever
-- reason. If you want to create a new table instead of using this function
-- just:
--
-- CREATE SEQUENCE foo_id_seq;
-- CREATE TABLE newtable AS
-- SELECT *, nextval('foo_id_seq') id
-- FROM oldtable;
-----------------------------------------------------------
-- Self contained example:
--
-- SELECT ST_AddUniqueID('spatial_ref_sys', 'id', true, true);
-- ALTER TABLE spatial_ref_sys DROP COLUMN id;
-----------------------------------------------------------
-- Pierre Racine ([email protected])
-- 10/02/2013 added in v1.7
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION ST_AddUniqueID(
schemaname name,
tablename name,
columnname name,
replacecolumn boolean DEFAULT false,
indexit boolean DEFAULT true
)
RETURNS boolean AS $$
DECLARE
seqname text;
fqtn text;
BEGIN
IF replacecolumn IS NULL THEN
replacecolumn = false;
END IF;
IF indexit IS NULL THEN
indexit = true;
END IF;
-- Determine the complete name of the table
fqtn := '';
IF length(schemaname) > 0 THEN
fqtn := quote_ident(schemaname) || '.';
END IF;
fqtn := fqtn || quote_ident(tablename);
-- Check if the requested column name already exists
IF ST_ColumnExists(schemaname, tablename, columnname) THEN
IF replacecolumn THEN
EXECUTE 'ALTER TABLE ' || fqtn || ' DROP COLUMN ' || columnname;
ELSE
RAISE NOTICE 'Column already exist. Set the ''replacecolumn'' argument to ''true'' if you want to replace the column.';
RETURN false;
END IF;
END IF;
-- Create a new sequence
seqname = schemaname || '_' || tablename || '_seq';
EXECUTE 'DROP SEQUENCE IF EXISTS ' || quote_ident(seqname);
EXECUTE 'CREATE SEQUENCE ' || quote_ident(seqname);
-- Add the new column and update it with nextval('sequence')
EXECUTE 'ALTER TABLE ' || fqtn || ' ADD COLUMN ' || columnname || ' INTEGER';
EXECUTE 'UPDATE ' || fqtn || ' SET ' || columnname || ' = nextval(''' || seqname || ''')';
IF indexit THEN
EXECUTE 'CREATE INDEX ' || tablename || '_' || columnname || '_idx ON ' || fqtn || ' USING btree(' || columnname || ');';
END IF;
RETURN true;
END;
$$ LANGUAGE plpgsql VOLATILE;
-----------------------------------------------------------
-- ST_AddUniqueID variant defaulting to the 'public' schemaname
CREATE OR REPLACE FUNCTION ST_AddUniqueID(
tablename name,
columnname name,
replacecolumn boolean DEFAULT false,
indexit boolean DEFAULT true
)
RETURNS BOOLEAN AS $$
SELECT ST_AddUniqueID('public', $1, $2, $3, $4)
$$ LANGUAGE sql VOLATILE;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- ST_AreaWeightedSummaryStats
--
-- geomval - A set of geomval couple (geometry, double precision) resulting
-- from ST_Intersection(raster, geometry).
-- A variant taking a geometry and a value also exist.
--
-- Aggregate function computing statistics on a series of intersected
-- values weighted by the area of the corresponding geometry.
--
-- Statictics computed are:
--
-- - count - Total number of values in the aggregate.
-- - distinctcount - Number of different values in the aggregate.
-- - geom - Geometric union of all the geometries involved in the
-- aggregate.
-- - totalarea - Total area of all the geometries involved in the
-- aggregate (might be greater than the area of the
-- unioned geometry if there are overlapping geometries).
-- - meanarea - Mean area of the geometries involved in the aggregate.
-- - totalperimeter - Total perimeter of all the geometries involved in the
-- aggregate.
-- - meanperimeter - Mean perimeter of the geometries involved in the
-- aggregate.
-- - weightedsum - Sum of all the values involved in the aggregate
-- multiplied by (weighted by) the area of each geometry.
-- - weightedmean - Weighted sum divided by the total area.
-- - maxareavalue - Value of the geometry having the greatest area.
-- - minareavalue - Value of the geometry having the smallest area.
-- - maxcombinedareavalue - Value of the geometry having the greatest area
-- after geometries with the same value have been
-- unioned.
-- - mincombinedareavalue - Value of the geometry having the smallest area
-- after geometries with the same value have been
-- unioned.
-- - sum - Simple sum of all the values in the aggregate.
-- - man - Simple mean of all the values in the aggregate.
-- - max - Simple max of all the values in the aggregate.
-- - min - Simple min of all the values in the aggregate.
--
-- This function aggregates the geometries and associated values when
-- extracting values from one table with a table of polygons using
-- ST_Intersection. It was specially written to be used with
-- ST_Intersection(raster, geometry) which returns a set of (geometry, value)
-- which have to be aggregated considering the relative importance of the
-- area intersecting with each pixel of the raster. The function is provided
-- only to avoid having to write the correct, often tricky, syntax to
-- aggregate those values.
--
-- Since ST_AreaWeightedSummaryStats is an aggregate, you always have to
-- add a GROUP BY clause to tell which column to use to group the polygons
-- parts and aggregate the corresponding values.
--
-- Note that you will always get better performance by writing yourself the
-- right code to aggregate any of the values computed by
-- ST_AreaWeightedSummaryStats. But for relatively small datasets, it will
-- often be faster to use this function than to try to write the proper code.
--
-- Sometimes, for tricky reasons, the function might fail when it tries to
-- recreate the original geometry by ST_Unioning the intersected parts. When
-- ST_Union fails, the whole ST_AreaWeightedSummaryStats function fails. If
-- this happens, you will probably have to write your own aggregating code
-- to avoid the unioning.
--
-- This function can also be used when intersecting two geometry tables
-- where geometries are split in multiple parts.
-----------------------------------------------------------
-- Self contained example:
--
-- SELECT id,
-- (aws).count,
-- (aws).distinctcount,
-- (aws).geom,
-- (aws).totalarea,
-- (aws).meanarea,
-- (aws).totalperimeter,
-- (aws).meanperimeter,
-- (aws).weightedsum,
-- (aws).weightedmean,
-- (aws).maxareavalue,
-- (aws).minareavalue,
-- (aws).maxcombinedareavalue,
-- (aws).mincombinedareavalue,
-- (aws).sum,
-- (aws).mean,
-- (aws).max,
-- (aws).min
-- FROM (SELECT id, ST_AreaWeightedSummaryStats((geom, val)::geomval) as aws
-- FROM (SELECT ST_GeomFromEWKT('POLYGON((0 0,0 10, 10 10, 10 0, 0 0))') as geom, 'a' as id, 100 as val
-- UNION ALL
-- SELECT ST_GeomFromEWKT('POLYGON((12 0,12 1, 13 1, 13 0, 12 0))') as geom, 'a' as id, 1 as val
-- UNION ALL
-- SELECT ST_GeomFromEWKT('POLYGON((10 0, 10 2, 12 2, 12 0, 10 0))') as geom, 'b' as id, 4 as val
-- UNION ALL
-- SELECT ST_GeomFromEWKT('POLYGON((10 2, 10 3, 12 3, 12 2, 10 2))') as geom, 'b' as id, 2 as val
-- UNION ALL
-- SELECT ST_GeomFromEWKT('POLYGON((10 3, 10 4, 12 4, 12 3, 10 3))') as geom, 'b' as id, 2 as val
-- UNION ALL
-- SELECT ST_GeomFromEWKT('POLYGON((10 4, 10 5, 12 5, 12 4, 10 4))') as geom, 'b' as id, 2 as val
-- ) foo1
-- GROUP BY id
-- ) foo2;
--
-- Typical exemple:
--
-- SELECT gt.id,
-- (aws).geom,
-- (aws).totalarea,
-- (aws).weightedmean,
-- FROM (SELECT id, ST_AreaWeightedSummaryStats(gv) aws
-- FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv
-- FROM rasttable rt, geomtable gt
-- WHERE ST_Intersects(rt.rast, gt.geom)
-- ) foo1
-- GROUP BY gt.id
-- ) foo2;
-----------------------------------------------------------
-- Pierre Racine ([email protected])
-- 10/02/2013 added in v1.8
-----------------------------------------------------------
-----------------------------------------------------------
-- Type returned by the final _ST_AreaWeightedSummaryStats_FinalFN state function
CREATE TYPE agg_areaweightedstats AS (
count int,
distinctcount int,
geom geometry,
totalarea double precision,
meanarea double precision,
totalperimeter double precision,
meanperimeter double precision,
weightedsum double precision,
weightedmean double precision,
maxareavalue double precision,
minareavalue double precision,
maxcombinedareavalue double precision,
mincombinedareavalue double precision,
sum double precision,
mean double precision,
max double precision,
min double precision
);
-----------------------------------------------------------
-- Type returned by the _ST_AreaWeightedSummaryStats_StateFN state function
CREATE TYPE agg_areaweightedstatsstate AS (
count int,
distinctvalues double precision[],
unionedgeom geometry,
totalarea double precision,
totalperimeter double precision,
weightedsum double precision,
maxareavalue double precision[],
minareavalue double precision[],
combinedweightedareas double precision[],
sum double precision,
max double precision,
min double precision
);
-----------------------------------------------------------
-- ST_AreaWeightedSummaryStats aggregate state function
CREATE OR REPLACE FUNCTION _ST_AreaWeightedSummaryStats_StateFN(
aws agg_areaweightedstatsstate,
gv geomval
)
RETURNS agg_areaweightedstatsstate AS $$
DECLARE
i int;
ret agg_areaweightedstatsstate;
newcombinedweightedareas double precision[] := ($1).combinedweightedareas;
newgeom geometry := ($2).geom;
geomtype text := GeometryType(($2).geom);
BEGIN
-- If the geometry is a GEOMETRYCOLLECTION extract the polygon part
IF geomtype = 'GEOMETRYCOLLECTION' THEN
newgeom := ST_CollectionExtract(newgeom, 3);
END IF;
-- Skip anything that is not a polygon
IF newgeom IS NULL OR ST_IsEmpty(newgeom) OR geomtype = 'POINT' OR geomtype = 'LINESTRING' OR geomtype = 'MULTIPOINT' OR geomtype = 'MULTILINESTRING' THEN
ret := aws;
-- At the first iteration the state parameter is always NULL
ELSEIF $1 IS NULL THEN
ret := (1, -- count
ARRAY[($2).val], -- distinctvalues
newgeom, -- unionedgeom
ST_Area(newgeom), -- totalarea
ST_Perimeter(newgeom), -- totalperimeter
($2).val * ST_Area(newgeom), -- weightedsum
ARRAY[ST_Area(newgeom), ($2).val], -- maxareavalue
ARRAY[ST_Area(newgeom), ($2).val], -- minareavalue
ARRAY[ST_Area(newgeom)], -- combinedweightedareas
($2).val, -- sum
($2).val, -- max
($2).val -- min
)::agg_areaweightedstatsstate;
ELSE
-- Search for the new value in the array of distinct values
SELECT n
FROM generate_series(1, array_length(($1).distinctvalues, 1)) n
WHERE (($1).distinctvalues)[n] = ($2).val
INTO i;
-- If the value already exists, increment the corresponding area with the new area
IF NOT i IS NULL THEN
newcombinedweightedareas[i] := newcombinedweightedareas[i] + ST_Area(newgeom);
END IF;
ret := (($1).count + 1, -- count
CASE WHEN i IS NULL -- distinctvalues
THEN array_append(($1).distinctvalues, ($2).val)
ELSE ($1).distinctvalues
END,
ST_Union(($1).unionedgeom, newgeom), -- unionedgeom
($1).totalarea + ST_Area(newgeom), -- totalarea
($1).totalperimeter + ST_Perimeter(newgeom), -- totalperimeter
($1).weightedsum + ($2).val * ST_Area(newgeom), -- weightedsum
CASE WHEN ST_Area(newgeom) > (($1).maxareavalue)[1] -- maxareavalue
THEN ARRAY[ST_Area(newgeom), ($2).val]
ELSE ($1).maxareavalue
END,
CASE WHEN ST_Area(newgeom) < (($1).minareavalue)[1] -- minareavalue
THEN ARRAY[ST_Area(newgeom), ($2).val]
ELSE ($1).minareavalue
END,
CASE WHEN i IS NULL -- combinedweightedareas
THEN array_append(($1).combinedweightedareas, ST_Area(newgeom))
ELSE newcombinedweightedareas
END,
($1).sum + ($2).val, -- sum
greatest(($1).max, ($2).val), -- max
least(($1).min, ($2).val) -- min
)::agg_areaweightedstatsstate;
END IF;
RETURN ret;
END;
$$ LANGUAGE plpgsql;
-----------------------------------------------------------
-- _ST_AreaWeightedSummaryStats_StateFN state function variant taking a
-- geometry and a value, converting them to a geomval
CREATE OR REPLACE FUNCTION _ST_AreaWeightedSummaryStats_StateFN(
aws agg_areaweightedstatsstate,
geom geometry,
val double precision
)
RETURNS agg_areaweightedstatsstate AS $$
SELECT _ST_AreaWeightedSummaryStats_StateFN($1, ($2, $3)::geomval);
$$ LANGUAGE sql;
-----------------------------------------------------------
-- _ST_AreaWeightedSummaryStats_StateFN state function variant defaulting
-- the value to 1 and creating a geomval
CREATE OR REPLACE FUNCTION _ST_AreaWeightedSummaryStats_StateFN(
aws agg_areaweightedstatsstate,
geom geometry
)
RETURNS agg_areaweightedstatsstate AS $$
SELECT _ST_AreaWeightedSummaryStats_StateFN($1, ($2, 1)::geomval);
$$ LANGUAGE sql;
-----------------------------------------------------------
-- ST_AreaWeightedSummaryStats aggregate final function
CREATE OR REPLACE FUNCTION _ST_AreaWeightedSummaryStats_FinalFN(
aws agg_areaweightedstatsstate
)
RETURNS agg_areaweightedstats AS $$
DECLARE
a RECORD;
maxarea double precision = 0.0;
minarea double precision = (($1).combinedweightedareas)[1];
imax int := 1;
imin int := 1;
ret agg_areaweightedstats;
BEGIN
-- Search for the max and the min areas in the array of all distinct values
FOR a IN SELECT n, (($1).combinedweightedareas)[n] warea
FROM generate_series(1, array_length(($1).combinedweightedareas, 1)) n LOOP
IF a.warea > maxarea THEN
imax := a.n;
maxarea = a.warea;
END IF;
IF a.warea < minarea THEN
imin := a.n;
minarea = a.warea;
END IF;
END LOOP;
ret := (($1).count,
array_length(($1).distinctvalues, 1),
($1).unionedgeom,
($1).totalarea,
($1).totalarea / ($1).count,
($1).totalperimeter,
($1).totalperimeter / ($1).count,
($1).weightedsum,
($1).weightedsum / ($1).totalarea,
(($1).maxareavalue)[2],
(($1).minareavalue)[2],
(($1).distinctvalues)[imax],
(($1).distinctvalues)[imin],
($1).sum,
($1).sum / ($1).count,
($1).max,
($1).min
)::agg_areaweightedstats;
RETURN ret;
END;
$$ LANGUAGE plpgsql;
-----------------------------------------------------------
-- ST_AreaWeightedSummaryStats aggregate definition
CREATE AGGREGATE ST_AreaWeightedSummaryStats(geomval)
(
SFUNC=_ST_AreaWeightedSummaryStats_StateFN,
STYPE=agg_areaweightedstatsstate,
FINALFUNC=_ST_AreaWeightedSummaryStats_FinalFN
);
-----------------------------------------------------------
-- ST_AreaWeightedSummaryStats aggregate variant taking a
-- geometry and a value. Useful when used with two geometry tables.
CREATE AGGREGATE ST_AreaWeightedSummaryStats(geometry, double precision)