Thank you... this did the trick.
Obe, Regina wrote:
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:* [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] *On Behalf Of
*Burgholzer,Robert
*Sent:* Tuesday, June 03, 2008 3:07 PM
*To:* [EMAIL PROTECTED]; 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: [EMAIL PROTECTED] 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
[email protected]
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. *
------------------------------------------------------------------------
* Help make the earth a greener place. If at all possible resist
printing this email and join us in saving paper. *
* *
* *
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
