A collection of useful SQL queries

UsefulSQL.md

Get a list of duplicate records and how many duplicates there of that record

SELECT DISTINCT SOME_FIELD, count(*)
FROM SOME_TABLE
GROUP BY SOME_FIELD
HAVING count(*) > 1

Spatial join based that adds polygon values (b) to intersecting point features (a). Be aware that this does not return a value for those points that are outside a polygon feature

Example question: What council district is each park in?

SELECT a.field1, b.field1
FROM a, b
WHERE st_intersects(st_pointonsurface(a.geom), b.geom)
order by council_dist

Alternatively, we can get the things that don't intersect!

SELECT a.field1
FROM a
left join b
on st_intersects(st_pointonsurface(a.geom), b.geom)
where b.field1 is null

Get counts and acreage of park types

select park_type, count(park_type), round(sum(map_acres)::numeric, 2)
from parks
group by park_type