[postgis-users] Select polygons within polygon with PostGIS 2.0

[Sandro Santilli]

On Fri, Oct 12, 2012 at 10:54:16AM +0000, Astrid Bjørnerød wrote:
> I have problems understanding the difference between ST_CreateTopogeo, CreateTopoGeom and toTopoGeom.
> I have a dataset made by intersection between A and B. 
> generated by sql: 
> CREATE TABLE sammen.ab AS 
>   SELECT nextval('sammen_gid_seq') as gid,
>   a.code as a,
>   b.code as b,
>   0 as newvalue,
>   (ST_Dump(ST_Intersection(b.geo, a.geo))).geom AS geo
>   FROM sammen.b b,sammen.a a
>   WHERE ST_Intersects(a.geo, b.geo);
> 
> I want to find polygons where a is not null and b is null 
> and calculate newvalue to leftface b-value if rightface b-value is null  or opposite, or if both leftface and rightface value is not null then b-value of the bigest polygon.
> 
> So I created topology.
>   
> SELECT topology.CreateTopology('ab_topo',25832, 0.01);
> SELECT topology.AddTopoGeometryColumn('ab_topo', 'sammen', 'ab', 'topo', 'POLYGON');
> UPDATE sammen.ab set topo = (SELECT topology.toTopoGeom(geo, 'ab_topo', 1, 0.01));  
> 
> A little confused I tried this sql too:
> SELECT topology.ST_CreateTopoGeo('ab_topo',  St_collect(geo)) from sammen.ab;
> SELECT topology.topologysummary('ab_topo'); has result with lots of node, edges and faces, but null topogeoms.

I guess you did this _after_ re-creating 'ab_topo' ?
Because otherwise ST_CreateTopoGeo should error out...

ST_CreateTopoGeo precondition is to have an _empty_ topology.
It's a function defined by SQL/MM standard, and it has nothing
to do with TopoGeometry objects (note the missing 'm' suffix).
So it is expected to have no topogeoms as a result.

> Next sql I tried:
> 
> INSERT INTO sammen.abc(id, topo)
> SELECT edge_id, topology.CreateTopoGeom('ab_topo',2,3, ('{{' || edge_id::text ||',2}}')::topology.topoelementarray)
> FROM
> (SELECT DISTINCT e.edge_id FROM sammen.ab AS m
> INNER JOIN ab_topo.edge As e
> ON m.gid = e.edge_id);

This query is suspicious.
You're comparing edge ids with feature identifiers, which are two completely
different things.

> Then I got a number of topogeoms in  topologysummary.

Did you look at them ? Qgis should support visualizing them.
I guess they would be pretty random...

> What is the difference between ST_CreateTopogeo, CreateTopoGeom and toTopoGeom? 

ST_CreateTopogeo only populates the primitive tables ("node","edge","face")
This is defined by SQL/MM.

CreateTopoGeom creates a value you can store into a TopoGeometry column and which
refers to a set of primitives that compose your feature's shape (a set of faces,
in your case). This function will _never_ modify the primitive tables, but rather
act on the "relation" table. 

toTopoGeom creates a TopoGeometry which is "as close as possible" in shape to
the Geometry you pass as input argument. Such TopoGeometry would either use 
existing primitives or it would use primitives which would be added ad-hoc to
allow representability of the output TopoGeometry. So this function _could_
affect the primitive tables ("node","edge","face") and will affect the "relation"
table. The "as close as possible" part here refers to the snapping (for tolerance)
and noding operations performed by the function. When the input is fully noded
and topologically valid there should be no difference in shape between the
topologically-defined object (TopoGeometry) and the simple object (Geometry).

> Which of these methods should I use for finally query finding new values for polygons where b is null ?

I didn't really understand what you are after, but hopefully the description
above (sorry if too dense) will help you figure out which method is more
appropriate for your case.

--strk;

 http://www.cartodb.com - Map, analyze and build applications with your data

                                       ~~ http://strk.keybit.net