[postgis-users] basic functionalities of PostGIS
Birgit Laggner
birgit.laggner at vti.bund.de
Mon Jan 10 03:51:30 PST 2011
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>
More information about the postgis-users
mailing list