PostGIS Queries

README.md

Useful PostGIS Queries

Raster

Area in square meters for raster between pixels values of 4 and 6

SELECT
  SUM(
    ST_Area(
      ST_Multi(geom)
    )
  ) As area
FROM (
  SELECT (ST_DumpAsPolygons(geom)).*
  FROM raster_table
) As foo
WHERE val BETWEEN 4 and 6;

Result:

area
number

---

Percentage of raster area, between pixels of value 4 and 6, inside a single vector polygon

SELECT ROUND(
  CAST(100.00 * SUM((
    ST_Area(
      ST_Multi(clipped_raster.geom)
    ) /
    ST_Area(
      ST_Multi(polygon_vector_table.geom)
    )
  )) as numeric), 2
) As percentage
FROM
  (
    SELECT (ST_DumpAsPolygons(ST_Clip(raster_table.geom,polygon_vector_table.geom, 20))).*
    FROM
      polygon_vector_table,
      raster_table
    WHERE ST_Intersects(polygon_vector_table.geom,raster_table.geom)
  ) As clipped_raster,
  polygon_vector_table
WHERE clipped_raster.val BETWEEN 4 and 6;

Result:

percentage
number

---

Vector polygons from a table with percentage of intersected raster area between pixels of value 4 and 6

WITH intersected_raster As (
  SELECT 
    polygons_table.unique_id,
    (ST_Intersection(polygons_table.geom, raster.geom)).*
  FROM polygons_table
  LEFT JOIN raster ON ST_Intersects(raster.geom, polygons_table.geom)
)
SELECT 
  polygons_table.*,
  ROUND(
    CAST(100.00 * SUM((
      ST_Area(i_raster.geom) /
      ST_Area(polygons_table.geom)
    )) as numeric), 2
  ) As raster_percentage
FROM polygons_table
LEFT JOIN
  (SELECT * FROM intersected_raster WHERE val BETWEEN 4 and 6) As i_raster
    ON polygons_table.unique_id = i_raster.unique_id
GROUP BY polygons_table.unique_id
ORDER BY polygons_table.unique_id;

Result:

unique_id	geom	other_polygon_column	raster_percentage
1	geometry	some_value	number
2	geometry	some_value	number
...	geometry	some_value	number

---

Vector

Percentage of vector polygon area inside another vector polygon

SELECT ROUND(
  CAST(100.00 * (
    ST_Area(
      ST_Intersection(
        ST_Multi(ST_Union(layer0.geometry)), ST_Multi(ST_Union(layer1.geometry))
      )::geography
    ) /
    ST_Area(
      ST_Multi(ST_Union(layer1.geometry))::geography
    )
  ) as numeric), 2) as percentage
FROM
  area_table as layer0,
  polygon_table as layer1
WHERE ST_Intersects(layer0.geometry, layer1.geometry)
GROUP BY layer1.unique_id;

Result:

percentage
number

---

List of geometry that intersects with a polygon (no geometry transformation)

SELECT layer0.*
FROM
  list_table as layer0,
  polygon_table as layer1
WHERE ST_Intersects(layer0.geometry, layer1.geometry)
GROUP BY layer0.unique_id;

Result will be:

unique_id	geometry	other_column
1	geometry	some_value
2	geometry	some_value
...	geometry	some_value

---