Hallo Apostolis and welcome
If you look at your query the result you get is the only possible.
When you do:
..... FROM table1 AS a, table2 AS b ;
without any where-clause you will get all possible combinations.
This is the great thing when handling spatial data with sql, that you have
complete freedom and there is no code in the background making any decisions
for you.
So, what you have to do is find out what you really want to do. How do you want
your polygons to be unioned. If you for example want to union all polygons that
intersect you should do:
select st_union(a.the_geom, b.the_geom) from table1 a, table2 b where
st_intersects(a.the_geom, b.the_geom)
or the same thing like:
select st_union(a.the_geom, b.the_geom) from table1 a inner join table2 b on
st_intersects(a.the_geom, b.the_geom)
>From the result you maybe want to union your polygons so you only get one big
>polygon per row in table 1, then you could instead do for example:
select st_union(st_union(a.the_geom, b.the_geom)) from table1 a inner join
table2 b on st_intersects(a.the_geom, b.the_geom) group by id_in_table1
then you use st_union vertically outside your original union.
The thing is to get to know how the rows from the both tables are mixed in
different situations and get to know how the spatial functions can handle that.
Many of the functions in PostGIS can work both in the form
ST_function(a.the_geom, b.the_geom) and as aggregate functions like
st_function(the_geom) .... group by some_field
About populating the geometry_columns table that is not done automatically.
Often you don't need your new table represented like if you are just going to
use your table for further calculations in PostGIS. Then it would be just
annoying overhead.
But when you need it, for example if you are going to show your data in
geoserver, then you can easily populate the geometry_columns table with the
function Populate_Geometry_Columns:
http://postgis.org/documentation/manual-svn/Populate_Geometry_Columns.html
Once you see the pattern, how to combine sql with spatial functionality, you
will find it quite annoying waiting for "tools" in other softwares to execute
:-)
Regards
Nicklas
2011-01-03 skrev ÁÐÏÓÔÏËÏÓ ËÅËÅÍÔÆÇÓ :
I try to execute the basic functionalities of GIS (union, sym_difference,
buffer, intersect...) with PostGIS 1.5, but i encounter some problems.
>I create a new table to store results of overlay analysis, and i observe that
>every entity of new thematic layer is triplicate!!
>Specifically, i want to union two layers which include 15 and 3 entities
>respectively, and i use the following command:
> CREATE TABLE tablename AS
> SELECT ST_UNION (a.the_geom, b.the_geom)
> FROM table1 AS a, table2 AS b ;
>and i note that the above SQL query relate each entity from one layer with
>each entity from other, like a cross join!!!
>What happened??
>Also, i find that the new thematic layer isn't add to the table
>"geometry_columns"!!!Why??
>
>Thanks,
>Apostolis
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
