Postgresql

跨表查询统计

SELECT xcz_item.code,xcz_region."name","type","count"(xcz_item.code) AS count
FROM xcz_item
LEFT JOIN xcz_region
ON xcz_item.code LIKE '330604%' AND xcz_item.code=xcz_region.code
GROUP BY xcz_item.code,"type",xcz_region."name"
ORDER BY xcz_item.code ,"type"

跨表条件统计

SELECT DISTINCT a.code,a.name,
COALESCE(count(b.*),0) AS total,
sum(CASE WHEN b.state = 4 THEN 1 ELSE 0 END) AS finished,
sum(CASE WHEN b.state <>4 OR b.state IS NULL THEN 1 ELSE 0 END) AS unfinished,
st_astext(st_centroid(a.geom)) AS position
FROM xcz_region a left join xcz_item b
ON a.code=b.code
WHERE a.need=1 AND b.type='1001'
GROUP BY a.code,a.name,a.geom
ORDER BY a.code

图形操作

• 空间查询

  SELECT count(*) FROM njq_dk WHERE st_intersects(geom,st_geomfromtext('MULTIPOLYGON(((120.92 29.37,120.93 29.37,120.93 29.38,120.92 29.37,120.92 29.37)))',4326))
  SELECT count(*) FROM njq_dk WHERE st_intersects(geom,st_geomfromtext('POLYGON((120.92 29.37,120.93 29.37,120.93 29.38,120.92 29.37,120.92 29.37))',4326))

• 创建空间索引

  create extension pg_trgm;
  update pg_opclass set opcdefault = true where opcname='gin_trgm_ops';
  CREATE index on njq_dk USING gist (geom);
  CREATE index on njq_dk USING gin (geom gin_trgm_ops);

• 外包多边形

  select ST_AsText(ST_ConvexHull(ST_Collect(geom))) FROM xcz_region

• 外包矩形

  select ST_AsText(st_Envelope(ST_Collect(geom))) FROM xcz_region

• 中心点（面质心）

  select st_astext(st_centroid(geom)) FROM xcz_region

• 面上点（和面lable位置一致）

  select st_astext(st_PointOnSurface(geom)) FROM xcz_region