[postgis-users] basic functionalities of PostGIS

[Birgit Laggner]

  Hi Apostolis,

as Andreas mentioned, I wrote a pl/pgsql function for the arcgis union 
which is still far from being perfect. I attach the sql file to this 
mail and you can try if it works for your data. If you want to use the 
function, then first, execute the create or replace function parts (I 
splitted the function into 6 parts: start(pgunion), intersection, no 
intersection, difference_a, difference_b and merge) and then call the 
functions with

select _pgunion('/schema name/', '/table1/', '/id table1/', '/table2/', 
'/id table2/');
select _pgintersection('/schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/intsec_table1_table2/');
select _pgnointersection('/schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/intsec_table1_table2/', 
'/nointsec_table1/', '/nointsec_table2/');
select _pgdifference_a(/'schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/diff_table1/');
select _pgdifference_b('/schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/diff_table2/');
select _pgmerge('/schema name/', '/id table1/', '/id table2/', 
'/intsec_table1_table2/', '/nointsec_table1/', '/nointsec_table2/', 
'/diff_table1/', '/diff_table2/', '/pgunion_table1_table2/');

(italic letters mean that you should replace these words by the names of 
your tables and id columns)

Your data should have
-gid column (column name is required) with data type integer or serial 
and btree index,
-id column (any name, name is passed with function call) with data type 
integer or serial and btree index,
-geometry column named the_geom with gist index

Both tables should be in the same schema (Certainly, this is changeable, 
but since you said you are a novice in postgis, I suppose you would 
rather use the functions in their current state).

If you don't want to have problems with polygon duplication, it would be 
wise to first make sure that you data does not contain duplicates. But 
my tests did show me, that it's very unlikely that my functions create 
more duplicates than there were before.

The functions will create a series of tables:
-intsec_/table1/_/table2/
-nointsec_/table1/
-nointsec_/table2/
-diff_/table1/
-/table1/_/table2/_diff_exceptions (contains polygons where the 
difference of table1-table2 threw an error)
-diff_/table2/
-/table2/_/table1/_diff_exceptions (contains polygons where the 
difference of table2-table1 threw an error)
-pgunion_/table1/_/table2/ (this is the result table!!)


If you need some more explanations, please ask.

I would be happy if somebody else (you??) would test my functions. I 
tested them for instance with tables of about 400.000 rows against 
15.000 rows and it ran for about 1-2 days.

Regards and success,

Birgit.



On 07.01.2011 22:11, ????????? ????????? wrote:
> Andreas thank you for your reply!!
> I am novice in postgis, and i would appreciate if you give me some 
> useful advice..
> Also, I am really greatful, if you send me the code to union two 
> shapefiles!!
> I don't want to use any more the arcgis desktop, and i understand that 
> postgis is a very good solution for data collection and manipulation 
> with many spatial functio
>
> Apostolis
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110110/a08f04d2/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: laggner_pgunion.sql
Type: text/x-sql
Size: 14395 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110110/a08f04d2/attachment.bin>