[postgis-users] Intersection - No GID
SenTnel
elvin.pimentel at gmail.com
Tue Jun 3 18:04:27 PDT 2008
Thanks Kishor!
Im really into it, today Ived spent several hours going thru couple of
different tutorials and I must say that Im getting better at the
understanding, I even tryed couple of ways, but still I was missing
something, here are some of my failed trials (so you can see Im working on
it):
UPDATE sky.callesnac
SET street = 'N/A'
WHERE street = 'DEFAULT'
UPDATE sky.callesnac
SET street = 'N/A'
WHERE street = 'NULL'
UPDATE sky.callesnac
SET street = 'N/A'
WHERE street = ''
now i see what i was missing, want to thank you sincerely for your help,
because tutorials are incredibly good, but unfurtunately they cannot cover
every possible situation and without guys like you and the others is not
possible fully understand the topics.
Thanks again !
P Kishor-3 wrote:
>
> 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
>>
> _______________________________________________
> 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-tp17613265p17637120.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
More information about the postgis-users
mailing list