Home

PostGIS cheatsheet

Get NODATA value:

select st_bandnodatavalue(rast, 1), st_bandnodatavalue(rast, 2) from file_jelbrqhpickujrbtqwyp;

Get info

select st_pixelwidth(A.rast) as pxWa, st_pixelheight(A.rast) as pxHa, st_pixelwidth(B.rast) as pxWb, st_pixelheight(B.rast) as pxHb, st_width(A.rast) as wA, st_height(A.rast) as hA, st_width(B.rast) as wB, st_height(B.rast) as hB, st_scalex(A.rast) as scalexA, st_scalex(B.rast) as scalexB, st_skewx(A.rast) as skewxA, st_skewx(B.rast) as skewxB from file_kguekgquebpafblewhne as A, file_vxrelgsabnyisfreftdh as B;

Check alignment

SELECT
    ST_SameAlignment(
        A.rast, B.rast
    ),
    ST_NotSameAlignmentReason(
        A.rast, B.rast
    )

    from file_kguekgquebpafblewhne as A, file_dquamjioaefmnspnpzah as B
;

DUMPS

mask dataset = file_caolvkddzzlgmvyeofrh


snow raster dataset = file_xbwnbydpmwesokzmrcht


WITH first AS (
   SELECT id, (ST_Intersection(file_caolvkddzzlgmvyeofrh.rast, file_xbwnbydpmwesokzmrcht.rast, 0)).val
   FROM file_caolvkddzzlgmvyeofrh
   INNER JOIN file_xbwnbydpmwesokzmrcht
)
SELECT id, COUNT(val), SUM(val), AVG(val), stddev(val), MIN(val), MAX(val)
FROM first
GROUP BY id;


WITH first AS (
   SELECT id, (ST_Intersection(file_caolvkddzzlgmvyeofrh.rast, file_xbwnbydpmwesokzmrcht.rast, 0)).val
   FROM file_caolvkddzzlgmvyeofrh
   INNER JOIN file_xbwnbydpmwesokzmrcht
)
SELECT id, COUNT(val), SUM(val), AVG(val), stddev(val), MIN(val), MAX(val)
FROM first
GROUP BY id;

var query = "select row_to_json(t) from (SELECT rid, pvc FROM " + dataset.table_name + ", ST_ValueCount(rast,1) AS pvc WHERE st_intersects(, rast)) as t;"

SELECT rid, pvc FROM " + dataset.table_name + ", ST_ValueCount(rast,1) AS pvc WHERE st_intersects(, rast)

# "working"
select row_to_json(t) from (
    SELECT A.rid, B.rid, pvc
    FROM file_xbwnbydpmwesokzmrcht A 
    JOIN file_caolvkddzzlgmvyeofrh B 
    ON ST_Intersects(A.rast, B.rast), 
       ST_ValueCount(A.rast,1) AS pvc
) as t;





# proves that mask is not active

select row_to_json(t) from (
    SELECT A.rid, B.rid, pvc, mask
    FROM file_xbwnbydpmwesokzmrcht A 
    JOIN file_caolvkddzzlgmvyeofrh B 
    ON ST_Intersects(A.rast, B.rast), 
       ST_ValueCount(A.rast,1) AS pvc,
       ST_ValueCount(B.rast,1) AS mask
) as t;

select row_to_json(t) from (
    SELECT rid, pvc 
    FROM file_xbwnbydpmwesokzmrcht A 
    JOIN file_caolvkddzzlgmvyeofrh B 
    ON 

) as t;



select row_to_json(t) from (
    SELECT A.rid, B.rid, pvc
    FROM file_xbwnbydpmwesokzmrcht A 
    JOIN file_caolvkddzzlgmvyeofrh B 
    ON ST_Intersects(A.rast, B.rast), 
       ST_ValueCount(ST_Intersection(A.rast,1, B.rast, 1)) AS pvc
) as t;


    select row_to_json(t) from (
        SELECT A.rid, B.rid, pvc
        FROM file_xbwnbydpmwesokzmrcht A 
        JOIN file_caolvkddzzlgmvyeofrh B 
        ON ST_Intersects(A.rast, B.rast), 
           ST_ValueCount(ST_Intersection(A.rast,1, B.rast, 1)) AS pvc
    ) as t;

SELECT ST_SameAlignment(A.rast,b.rast)
 FROM file_xbwnbydpmwesokzmrcht AS A CROSS JOIN file_caolvkddzzlgmvyeofrh AS B;


select st_scale(rast, 2) FROM file_xbwnbydpmwesokzmrcht;

select st_rescale(A.rast, st_scalex(B.rast)) from file_caolvkddzzlgmvyeofrh as A CROSS JOIN file_xbwnbydpmwesokzmrcht as B;


    select row_to_json(t) from (
        SELECT A.rid, B.rid, pvc
        FROM (
            select st_rescale(
                C.rast, st_scalex(D.rast)
            ) from file_caolvkddzzlgmvyeofrh as C CROSS JOIN file_xbwnbydpmwesokzmrcht as D
        ) as A
        JOIN file_caolvkddzzlgmvyeofrh B 
        ON ST_Intersects(A.rast, B.rast), 
           ST_ValueCount(ST_Intersection(A.rast,1, B.rast, 1)) AS pvc
    ) as t;







select row_to_json(t) from (SELECT A.rid, pvc FROM file_tctbkhuifvcfmeusjzov AS A, file_dquamjioaefmnspnpzah AS B, ST_ValueCount(A.rast,1) AS pvc WHERE st_intersects(A.rast, B.rast) AND (B.rast).val > 0) as t;





select row_to_json(t) from (SELECT A.rid, pvc FROM file_tctbkhuifvcfmeusjzov AS A, file_dquamjioaefmnspnpzah AS B, ST_ValueCount(A.rast,1) AS pvc WHERE st_intersects(A.rast, B.rast) AND (B.rast).val > 0) as t;









TESTING SCF CALCS
=================

db: jusbvxawxw
dataset: file_kguekgquebpafblewhne
date: 9. mai 2016
mask: file_dquamjioaefmnspnpzah



select row_to_json(t) from (SELECT A.rid, pvc FROM file_kguekgquebpafblewhne AS A, file_dquamjioaefmnspnpzah AS B, ST_ValueCount(A.rast,1) AS pvc WHERE st_intersects(A.rast, B.rast) AND (B.rast).val > 0) as t;


select row_to_json(t) from (SELECT A.rid, pvc FROM file_kguekgquebpafblewhne AS A, file_dquamjioaefmnspnpzah AS B, ST_ValueCount(A.rast,1) AS pvc WHERE st_intersects(A.rast, 1, B.rast, 1)) as t;




## stackoverflow

SELECT A.rid, pvc 
  FROM file_kguekgquebpafblewhne AS A INNER JOIN file_dquamjioaefmnspnpzah AS B ON 
      ST_Intersects(A.rast, B.rast)   
       , LATERAL 
         ST_ValueCount(ST_Intersection(A.rast,B.rast, 'BAND1') ,1) AS pvc;


SELECT
    ST_SameAlignment(
        A.rast, B.rast
    ),
    ST_NotSameAlignmentReason(
        A.rast, B.rast
    )

    from file_kguekgquebpafblewhne as A, file_dquamjioaefmnspnpzah as B
;



select st_pixelwidth(A.rast) as pxWa, st_pixelheight(A.rast) as pxHa, st_pixelwidth(B.rast) as pxWb, st_pixelheight(B.rast) as pxHb, st_width(A.rast) as wA, st_height(A.rast) as hA, st_width(B.rast) as wB, st_height(B.rast) as hB, st_scalex(A.rast) as scalexA, st_scalex(B.rast) as scalexB, st_skewx(A.rast) as skewxA, st_skewx(B.rast) as skewxB, st_srid(A.rast) as srid_a, st_srid(B.rast) as srid_b from file_kguekgquebpafblewhne as A, file_vxrelgsabnyisfreftdh as B;


select st_georeference(A.rast) as geo_a, st_georeference(B.rast) as geo_b from file_kguekgquebpafblewhne as A, file_dquamjioaefmnspnpzah as B;


select St_Transform(A.rast, B.rast) from file_kguekgquebpafblewhne as A, file_vxrelgsabnyisfreftdh as B;



SELECT A.rid, pvc 
  FROM file_kguekgquebpafblewhne AS A INNER JOIN file_vxrelgsabnyisfreftdh AS B ON 
      ST_Intersects(A.rast, B.rast)   
       , LATERAL 
         ST_ValueCount(ST_Intersection(A.rast,B.rast, 'BAND1') ,1) AS pvc;



SELECT A.rid, pvc 
  FROM file_kguekgquebpafblewhne AS A INNER JOIN file_vxrelgsabnyisfreftdh AS B ON 
      ST_Intersects(A.rast, B.rast)   
       , LATERAL 
         ST_ValueCount(ST_Clip(A.rast,ST_Polygon(B.rast) ) ,1) AS pvc;