[postgis-users] PostGIS-PostgreSQL

[Mike Toews]

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