[postgis-users] basic functionalities of PostGIS

[Nicklas Avén]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110103/f696de64/attachment.html>