How to clip from a point layer using postgis?

Question

I've loaded a LIDAR xyz file into a PostGIS2.0 database and created a 3D point geometry column ("tblCLOUD") from the data.

I've also loaded into the database a shapefile that contains six (non-overlapping) polygons ("tblZONES").

What I'm trying to do now is extract the points covered by each polygon to an individual table. So the end result would be six tables containing N points each.

I've hit my limits with PSQL!

Thanks!

EDIT:

My two tables - tblcloud & tblpoly are stuctured as follows:

CREATE TABLE tblcloud
(
  "pointID" integer NOT NULL DEFAULT nextval('"tblcloud_pointID_seq"'::regclass),
  x double precision,
  y double precision,
  z double precision,
  geom geometry(PointZ,2193),
  CONSTRAINT pk_point_id PRIMARY KEY ("pointID" )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tblcloud
  OWNER TO postgres;

-- Index: tblcloud_geom_gist

-- DROP INDEX tblcloud_geom_gist;

CREATE INDEX tblcloud_geom_gist
  ON tblcloud
  USING gist
  (geom );


CREATE TABLE tblpoly
(
  gid serial NOT NULL,
  minx numeric,
  miny numeric,
  maxx numeric,
  maxy numeric,
  cntx numeric,
  cnty numeric,
  area numeric,
  perim numeric,
  height numeric,
  width numeric,
  geom geometry(MultiPolygon,2193),
  CONSTRAINT tblpoly_pkey PRIMARY KEY (gid )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tblpoly
  OWNER TO tom;

-- Index: tblpoly_geom_gist

-- DROP INDEX tblpoly_geom_gist;

CREATE INDEX tblpoly_geom_gist
  ON tblpoly
  USING gist
  (geom );

Answer 1

Given you only have 6 polygons, I wouldn't bother fiddling with a loop to do it, so I would just do 6 seperate queries along the lines of

select tblcloud.* into tblcloudPolygon1 from tblcloud 
INNER JOIN tblpoly on 
st_contains(tblpolygon.geom,tblcloud.geom) 
and tblpoly.gid = 1

for the second one:

select tblcloud.* into tblcloudpolygon2 from tblcloud 
INNER JOIN tblpoly on 
st_contains(tblpoly.geom,tblcloud.geom) 
and tblpoly.gid = 2

While I don't know your exact uses, personally I wouldn't break out into individual tables, I would be creating one table with the polygon ids in it, something like

select tblcloud.*, tblpoly.gid into tblcloudandpolygonid from tblcloud 
INNER JOIN tblpoly 
on st_contains(tblpoly.geometry,tblcloud.geometry)

That way you can query one table to pick up the points in any polygon quickly, rather than have to query individual tables to get the points for each polygon.

Be aware that once you create these new tables, if you are using them in a production environment, it is best to add a primary key and to add the necessary indexes as well. This is especially important on large tables, it will be very slow otherwise.

edit: The queries above assume that your tblcloud and tblpolygon are the same SRID, otherwise they will always return no records.

Answer 2

Right - solved! There was something funny going on when I used shp2pgsql to load my polygons. So - I bit the bullet and went completely postgis. I loaded some points of interest, buffered them, enveloped the buffers and used that as my polygon layer. My code is below - hopefully to help someone else.

CREATE TABLE tblsitepoints (
name    varchar(20),
easting double precision,
northing    double precision);

COPY tblsitepoints(name, easting, northing) from 'filename' CSV HEADER;

ALTER TABLE tblsitepoints ADD COLUMN geom geometry(Point,2193);

UPDATE tblsitepoints SET geom = ST_SetSRID(ST_MakePoint(easting,northing),2193);

CREATE TABLE tbl_sitepoly AS
SELECT name,ST_Envelope(ST_Buffer(geom,200)) AS geom
FROM tblsitepoints;

SELECT tblcloud.*, tbl_sitepoly.name into tbldem from tblcloud INNER JOIN tbl_sitepoly    ON st_contains(tbl_sitepoly.geom,tblcloud.geom);