[postgis-users] Intersection - No GID

[SenTnel]

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:* 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.