[postgis-users] Intersection - No GID

[P Kishor]

On 6/3/08, SenTnel <elvin.pimentel at gmail.com> wrote:
>
>  Hi... again! =^D
>
>  While Im learning about this exciting new area to me Im going thru some SQL
>  tutorials, but so far can't find an answer to this dilema I have:
>
>  My database has a lot of empty "street" fields, meaning that a lot of
>  streets has no name registered, they're just plain blank, so I can't enter
>  the proper "road clasification", so they don't show up in the maps because
>  the SLD has clasifications for "street", "avenue" and "highway".
>
>  What I would like to do is to INSERT something like "N/A" (Not Available) in
>  every field that is empty in the "street" column, but don't know exactly the
>  SQL statemet to tell:
>
>  "In mytable, street column, insert 'N/A' in every empty space"
>

Did you really do any of the tutorials? Most of them will cover
something like...

UPDATE mytable
SET street = 'N/A'
WHERE street = '' OR street IS NULL




>  I owe you one more!
>
>  Thanks!!
>
>
>
>
>  marcuscengland wrote:
>  >
>  > 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:* 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. *
>  >>
>  >> ------------------------------------------------------------------------
>  >>
>  >> * 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
>  > postgis-users at postgis.refractions.net
>  > http://postgis.refractions.net/mailman/listinfo/postgis-users
>  >
>  >
>
>
> --
>  View this message in context: http://www.nabble.com/pgsql2shp-syntax---sentence-construction-tp17613265p17634973.html
>  Sent from the PostGIS - User mailing list archive at Nabble.com.
>
>
>  _______________________________________________
>  postgis-users mailing list
>  postgis-users at postgis.refractions.net
>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>