[postgis-users] PostGIS-PostgreSQL

[Chris Hermansen]

Yes BUT you should never have a changeable value like street_name as the 
primary / foreign key.

Instead you should have a street_name_lu table which has street_name_id 
and street_name, with street_name_id as the PK.  Then all tables that FK 
into that will use the street_name_id which does not change, and the 
street_name can be changed as needed, with all referencing tables "just 
getting" the change.

Most particularly, to use the whole cascading delete / update mechanism, 
or triggers for that matter, to deal with changes to primary key values, 
is really a shame when it's so simple to solve by a better key design 
strategy.

Mike Toews wrote:
> Nenad Milasinovic wrote:
>> Hello,
>>
>> I have some questions relating to PostGIS and PostgreSQL.
>> I am interested how GEO-data from PostGIS are connected to data in 
>> PostgreSQL.
>> Say that we have vector layer of all city streets in PostGIS table, 
>> with some street attributes (e.g. street name).
>> Say also that we have all data about locations in PostgreSQL table. 
>> That data contains information about location street also.
>> What i want to accomplish is that when user change street name is 
>> PostGIS table, that street name should automatically be changed in
>> all locations which belongs to that street. I am interested is there 
>> any connection between tables in PostGIS and PostgreSQL
>> that could automatize this process, or i need to update all locations 
>> with new street data by myself.
>>
>> Thanks.
>
> Hi Nenad,
>
> PostGIS and PostgreSQL tables are pretty much the same. A "GIS" table 
> is one that has 1 or more column with type "geometry" and metadata in 
> the geometry_columns table.
>
> It was mentioned previously, but to update references to a street name 
> in other tables you should investigate into a unique and foreign key. 
> So, for example, if you reference unique names of a street in one 
> table, give it a unique constraint:
>
> ALTER TABLE street
>  ADD CONSTRAINT street_name_unique UNIQUE(street_name);
>
> This means, however you can only have 1 "Broadway Street" in your 
> vacinity. If you have more than 1 distinctly named streets per city of 
> a metropolitan area, you may need to make a unique constraint using 
> two columns where it is unique, e.g.:
>
> ALTER TABLE street
>  ADD CONSTRAINT city_street_name_unique UNIQUE(city, street_name);
>
>
> Then the magic happens when you reference "Broadway Street" in another 
> table zero or more times using a foreign key with rules for "ON 
> UPDATE" and "ON DELETE" events to the unique reference. So, for 
> example, if you want the references to update themselves when the name 
> is updated, you need "ON UPDATE CASCADE" and when you want the 
> referenced rows to be deleted when the row with the unique reference 
> is deleted, you need "ON DELETE CASCADE". There are many options, 
> depending on what your are capturing and what you want to archive.
>
>
> ALTER TABLE street_attributes
>  ADD CONSTRAINT street_attributes_street_name_fkey FOREIGN KEY 
> (street_name)
>      REFERENCES street (street_name) MATCH SIMPLE
>      ON UPDATE CASCADE ON DELETE CASCADE;
>
> -- or for (city, street_name) --
>
> ALTER TABLE street_attributes
>  ADD CONSTRAINT street_attributes_city_street_name_fkey FOREIGN KEY 
> (city, street_name)
>      REFERENCES street (city, street_name) MATCH SIMPLE
>      ON UPDATE CASCADE ON DELETE CASCADE;
>
>
> If you have pgAdmin III you can create and explore the use and 
> behaviour of foreign key configurations using GUI dialogs, which is a 
> great way to learn.
>
> -Mike
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 
Regards,  (please note new mobile number below)

Chris Hermansen         mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5