[postgis-users] combine serveral parts of the country

[Mike Toews]

It seems to me that you only need to redefine a unique key. I can't
get a clear sense of your ideal table/layer from your descriptions,
but here is what you should have for your combined region:

CREATE TABLE all_polys (
  gid serial NOT NULL,
  geom geometry, -- SELECT
AddGeometryColumn('public','everything','geom',4326,'MULTIPOLYGON',2);
  region character varying(50) NOT NULL,
  id character varying(50) NOT NULL,
  --  ... other columns
  CONSTRAINT all_polys_pkey PRIMARY KEY (gid),
  CONSTRAINT all_polys_unique PRIMARY KEY (region, id)
);

Then for each set from a region, just assign it a code and use their
IDs. The critical thing here is that all_polys_unique is a two-piece
key, so import each set into it's own table at first (e.g., import1,
import2), then combine them in all_polys:

INSERT INTO all_polys(region, id, geom)
SELECT 'region1' as region, id, geom FROM import1;

INSERT INTO all_polys(region, id, geom)
SELECT 'region2' as region, id, geom FROM import2;

This way you can have:
SELECT region, id FROM all_polys;

region1 | 123
region1 | abc
region2 | abc
region2 | foo

etc.

Was this what you were after?

-Mike


On 22 September 2010 04:33, Thomas Andres <andres85thomas at gmail.com> wrote:
> Hi Ralf,
>
> yes the second dataset was imported with -append.
>
> I didn't explain well:
>
> Ogr2ogr creates the ogc_fid by incrementing, there aren't double ogc_fid s.
>
> But the Interlis himself tells ogr2ogr to create the linking attributes and
> fill them with the numbers written in the ITF-File.
> ('Parcelle_von' is in the right table and '_tid' in the left table). And
> it's here, where I have the collision when I'm importing the second
> dataset... it's normal, because
> The ITF-Files were written be different persons. They re-used the same
> links...
>
> Gr
> Thomas, aus dem Wallis
>
> -----Ursprüngliche Nachricht-----
> Von: Ralf Suhr [mailto:Ralf.Suhr at itc-halle.de]
> Gesendet: Mittwoch, 22. September 2010 11:28
> An: PostGIS Users Discussion
> Cc: Thomas Andres
> Betreff: Re: [postgis-users] combine serveral parts of the country
>
> Hi Thomas,
>
> ogc_fid can't exists twice. It is the primary key from ogr2ogr. Did you
> import
> your secound dataset with ogr2ogr -append?
>
> Gr
> Ralf
>
> Am Mittwoch 22 September 2010 09:15:49 schrieb Thomas Andres:
>> Salü Ralf,
>>
>>
>>
>> Thank you for your answer.
>>
>>
>>
>> -    When I use ogc_fid as primary key, I can’t import a second village,
>> because the IDs of their features were created by different offices,
>> different applications, so it’s possible that the same feature-id exists
>> twice.
>>
>>
>>
>> -    When I use shemas, I have to rewrite all SQL Querys.  Select blabla
>> from village1.parcelles Union all select blabla from village2.parcelles
>> Union all etc… This can become very long J
>>
>>
>>
>> If it’s possible, I would like that postgres sees, that the feature-id
>>  which ogr2ogr is importing now, already exists,… so give to the new
>>  feature a new feature-id, and at the same time he checks all foreign keys
>>  and adjust them to the new feature-id to maintain the link. (J
>>  complicated)
>>
>>
>>
>> Is there a easy solution?
>>
>>
>>
>> Thank you for your help.
>>
>>
>>
>> Salutation from “Chuchichäschtli”
>>
>>
>>
>> P.S.: Chuchichäschtli is a box in the kitchen
>>
>>
>>
>> ------------------------------
>>
>>
>>
>> Hi Chuchichäschtli,
>>
>>
>>
>> you can use ogc_fid as primary key from your tables or create on schema
> for
>>
>> one village.
>>
>>
>>
>> What "Chuchichäschtli" mean in german?
>>
>>
>>
>> Gr
>>
>> Ralf
>>
>> Am Montag 20 September 2010 09:34:11 schrieb Thomas Andres:
>> > Dear List,
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > i’m Swiss and my English isn’t the most best. Even, I will try the
>> > explain
>> >
>> > You my problem. Perhaps You will understand J
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > In Switzerland the parts of the country were measured by different
>>
>> offices.
>>
>> > The informations of the terrain (parcelles, rows, lakes, jungle etc
>>
>> ) are
>>
>> > exchanged by Interlis Files (*.itf).
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > With ogr2ogr this files can be imported into a Postgres Database.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > When You import only one file, it’s not a problem. Every Line, Point,
>> >
>> > Polygone and so on, has its own Feature-Id, which can be linked with
>> >
>> >  another feature.
>> >
>> >
>> >
>> > I.e.: The geometry of a parcelle is linked with his number.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > Now, when I would like add a second village to my GIS, in the same
>> >
>> >  Database, to be loaded in Mapserver with a mapfile layer
>>
>>  the parcelle
>>
>> >  with the same Feature ID will appear twice
>>
>>  so the link to the number of
>>
>> >  the parcelle is wrong
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > What I made in a first time: after have imported the first village, I
> add
>> >
>> >  to the primary and foreign keys, which I really I’m going to use in my
>>
>> GIS
>>
>> >  a Char (‘a’ in example)
>>
>>  by using PostgreSQL
>>
>>  only after I imported the
>>
>> >  second file. This is a solution which works, but not for long time J
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > I asked me, or better: I ask You, if there isn’t a mechanism in PostGIS
>> > or
>> >
>> > PostgreSQL which is specially implemented for this kind of collage
>>
>>  Some
>>
>> > kind of namespaces or
>>
>>  I don’t know
>>
>>  You understand =-) ?
>>
>> > Thänk You very much for Your help.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > Salutation from “Chuchichäschtli”
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>