Take the 2-minute tour ×

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!



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

  "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" )
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 );

  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 )
  OWNER TO tom;

-- Index: tblpoly_geom_gist

-- DROP INDEX tblpoly_geom_gist;

CREATE INDEX tblpoly_geom_gist
  ON tblpoly
  USING gist
  (geom );
share|improve this question

2 Answers 2

up vote 2 down vote accepted

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 
and tblpoly.gid = 1

for the second one:

select tblcloud.* into tblcloudpolygon2 from tblcloud 
INNER JOIN tblpoly on 
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.

share|improve this answer
Thanks for the help @Kelso. The SQL makes sense, but postgis doesn't like it. I must be doing something obviously wrong, but just can't see it. –  tomtomnz Dec 13 '12 at 3:39
My SQL is: SELECT tblcloud.*, tblpoly.gid into tblpolyoverpoint from tblcloud INNER JOIN tblpoly ON st_contains(tblpoly.geom,tblcloud.geom); –  tomtomnz Dec 13 '12 at 4:09
I created empty tables based on your queries above (except I didn't create as 3d geometry), no issues with your query running. I did notice that user postgres created the tblcloud, user tom created the tblpoly, are you running under user tom, and if so, does that user have read rights to the tblcloud table? –  Kelso Dec 13 '12 at 4:52
I tried recreating my tables using 2D point geometry rather than 3d. user tom as owner of both tables and running the query as tom. The query runs but still returns nothing. SELECT tblcloud.*, tblpoly.gid into tblpolyoverpoint from tblcloud INNER JOIN tblpoly ON st_contains(tblpoly.geom,tblcloud.geom); returns: Query returned successfully: 0 rows affected, 31074 ms execution time. –  tomtomnz Dec 14 '12 at 0:18
Are the SRIDs still identical? Are you sure there are actually overlapping points with the polygons? How are you importing this data? Is it projecting the data correctly? Your polygon layer isn't a GEOMETRYCOLLECTION is it? –  Kelso Dec 14 '12 at 5:49

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);
share|improve this answer

Your Answer


