PostGIS cheat sheet

Table join

I have attributes for taxlots in a separate table. (It came in a DBF file and I loaded it with "shp2pgsql -n") I want to join it so that I only see a single spatial table instead of having to do the join in QGIS or ArcMap.

CREATE VIEW taxlot_rprop
  SELECT *
  FROM taxlot as t LEFT JOIN rprop as r ON t.maptaxlot=r.maptaxlot;

This exposes every attribute in an ugly way. Normally I'd pare it down and normalize it for example

CREATE VIEW or_co_lincoln.taxlot_rprop AS
  SELECT t.gid as gid, t.the_geom as the_geom, t.maptaxlot, r.siteaddnam as situs, r.primaccnum as rpropid
  FROM or_co_lincoln.taxlot as t LEFT JOIN rprop as r ON t.maptaxlot = r.maptaxlot;

Dissolve

I want one multipolygon per state. Note that I can use the MIN function for both the numeric gid field and the text name field.

CREATE VIEW ca_gold.states AS
  SELECT MIN(gid) AS gid, MIN(state) AS name, ST_Multi(ST_Union(ST_Transform(geom,2226)))
  FROM us_national_atlas.states GROUP BY statesp010;

Spatial joins

Update an attribute with a value from a second table

I want to copy the name of a community from 'community' table to 'address_point' table.

The table looks like this. My schema is called "ca_gold".

CREATE TABLE ca_gold.address_points (
  gid SERIAL PRIMARY KEY,
  layer INT,
  streetnum INT4,
  streetname VARCHAR(100),
  displayname VARCHAR(100),
  unit VARCHAR(30),
  geom GEOMETRY
);

The points get copied from some other tables, using INSERT. Not showing that here...

I create a spatial index on the table to speed up the spatial query.

CREATE INDEX address_points_geom_gist ON ca_gold.address_points USING GIST(geom);

Probably totally unnecessary steps:

VACUUM ANALYZE VERBOSE ca_gold.address_points;
VACUUM ANALYZE VERBOSE ca_gold.community;

Now the fun part, add a column to hold the new attribute...

ALTER TABLE ca_gold.address_points ADD COLUMN community VARCHAR(50);

...and do the update. This updates table address_points with the 'name' value from the polygon layer, where the name is the name of the polygon that the point falls into. I found ST_Intersects to be marginally faster than ST_Contains/ST_Within. YMMV.

UPDATE ca_gold.address_points 
 SET community = sa.name FROM ca_gold.community AS sa
 WHERE ST_Intersects(ca_gold.address_points.geom, sa.geom);

-- sanity checks SELECT count(gid) FROM ca_gold.address_points; select distinct(fire_district) from ca_gold.address_points; select distinct(community) from ca_gold.address_points;

Missing PG_GEOMETRY column

This happens when you use ArcGIS to create a new feature class in a PostGIS database. They do a bad job and create broken feature classes in PostGSI. After you fix them you will discover the PostGIS database is still read-only.