On 6/3/08, SenTnel <[EMAIL PROTECTED]> 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:* [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 > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
