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" 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:* [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 > > -- 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
