[postgis-users] Intersection - No GID

Obe, Regina robe.dnd at cityofboston.gov
Tue Jun 3 12:31:53 PDT 2008

Just to add to some things that Robert said.
1) You can also add the serial after the fact with a 
ALTER TABLE new_fields ADD COLUMN gid serial;
ALTER TABLE new_fields  ADD CONSTRAINT pk_new_fields PRIMARY KEY(gid);
3) ST_Intersects is different from Intersects although ST_Intersection
is the same as Intersection.  It includes the && operator so you can
leave out the && in it
CREATE TABLE new_fields AS
   ST_Intersection(f.the_geom, c.the_geom) AS the_geom,
   fields f,
   clu c
    ST_Intersects(f.the_geom, c.the_geom)
Hope that hleps,


From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Sent: Tuesday, June 03, 2008 3:07 PM
To: marcuscengland at gmail.com; PostGIS Users Discussion; PostGIS Users
Subject: RE: [postgis-users] Intersection - No GID

1) You could create your table prior to populating it, and include a
column that is type "SERIAL", this will create an autogenerated integer
identifier that you could name "gid" if you so desired.

2) select area2d(Intersection(f.the_geom, c.the_geom)) ...
NOTE: You will have to convert this area2d result from the units of your
map projection into acres

3) ST_intersection and intersection are the same, the new (ST_*) naming
convention was recently added, for reasons that I do not know but assume
that they were good ones.


-----Original Message-----
From:   postgis-users-bounces at postgis.refractions.net on behalf of
Marcus C. England
Sent:   Tue 6/3/2008 2:57 PM
To:     PostGIS Users Discussion
Subject:        [postgis-users] Intersection - No GID

Hi all,

I figured out how to use Intersection and get a resulting table with
apparently correct results using the example from the PostGIS website:

CREATE TABLE new_fields AS
   Intersection(f.the_geom, c.the_geom) AS the_geom,
   fields f,
   clu c
   f.the_geom && c.the_geom
   Intersects(f.the_geom, c.the_geom)

However, this example does not create a gid column and I therefore can
not view the results in a GIS program. Is there a way to create a gid
column after-the-fact?

Optional questions for those feeling super-helpful:

(1) How should the above SQL be rewritten to include gid in the
resulting table?

(2) I know that eliminating the first line will simply give me a table
spit onto the SQL results in pgAdmin. How do I alter the SQL above to
give me the acreages rather then a list of the geometries? I can get the
acreages from the table resulting from the above, but I can't figure out
how to skip the step of creating a table.

(3) What is the syntax using ST_Intersection rather than Intersection?


postgis-users mailing list
postgis-users at postgis.refractions.net

The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080603/484f666d/attachment.html>

More information about the postgis-users mailing list