[postgis-users] Intersection - No GID

[Obe, Regina]

Marcus,
 
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
 SELECT
   ST_Intersection(f.the_geom, c.the_geom) AS the_geom,
   f.attr1,
   f.attr2,
   c.clu_name
 FROM
   fields f,
   clu c
 WHERE
    ST_Intersects(f.the_geom, c.the_geom)
 
Hope that hleps,
Regina

________________________________

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



Marcus,
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.

HTH,
Robert

-----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
Cc:    
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
 SELECT
   Intersection(f.the_geom, c.the_geom) AS the_geom,
   f.attr1,
   f.attr2,
   c.clu_name
 FROM
   fields f,
   clu c
 WHERE
   f.the_geom && c.the_geom
 AND
   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?

thanks,

Marcus
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users






-----------------------------------------
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>