[postgis-users] Select polygons within polygon with PostGIS 2.0
Astrid Bjørnerød
aeb at skogoglandskap.no
Fri Oct 12 03:54:16 PDT 2012
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.
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);
Then I got a number of topogeoms in topologysummary.
What is the difference between ST_CreateTopogeo, CreateTopoGeom and toTopoGeom?
Which of these methods should I use for finally query finding new values for polygons where b is null ?
In the png-attachment the small slivers polygons are the polygons with b = null.
Regards
Astrid Bjørnerød
-----Opprinnelig melding-----
Fra: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] På vegne av Sandro Santilli
Sendt: 11. oktober 2012 19:11
Til: PostGIS Users Discussion
Emne: Re: [postgis-users] Select polygons within polygon with PostGIS 2.0
On Thu, Oct 11, 2012 at 01:21:25PM +0200, Michal Zimmermann wrote:
> Yep, I have TopoGeometry fields.
> -- counties
> "Topology topo_kraje (12), SRID 4326, precision 0
> 36 nodes, 48 edges, 14 faces, 14 topogeoms in 1 layers Layer 1, type
> Polygonal (3), 14 topogeoms
> Deploy: edpp.kraje.topo
> "
> -- districts
> "Topology topo_orp (13), SRID 4326, precision 0
> 962 nodes, 1611 edges, 651 faces, 206 topogeoms in 1 layers Layer 1,
> type Polygonal (3), 206 topogeoms
> Deploy: edpp.orp.topo
> "
Those are 2 distinct topologies.
What I meant was a single topology with 2 layers.
First layer (simple) would be the districts and second layer (hierarchical) would be the counties, defined by the districts that make them up.
Building the TopoGeometry in the second layer won't be possible with the toTopoGeom function, but you'll have to use the CreateTopogeom function, using the first layer id as the "element_type" and the TopoGeometry.id as the "element_id" of the topoelementarray.
Note that you will still need ST_Covers to find which TopoGeometry objects of the first layer will define those of the second layer. But only the first time.
Mind you: all of this is very new and experimental, so only move further if you're ready to get your hands dirty and try something new. Beside, I just realized that the documentation for CreateTopoGeom (the function you would use to construct the hierarchical TopoGeometry objects) doesn't contain any example about building hierarchical TopoGeometries. It would be something like this:
UPDATE countytable SET topogeom =
CreateTopoGeom('topo_orp', 3, -- areal type
2, -- hierarchical layer id
TopoElementArray_agg(ARRAY[id(d.topogeom), 1])
FROM edpp.orp.topo d;
The above code is untested
--strk;
http://www.cartodb.com - Map, analyze and build applications with your data
~~ http://strk.keybit.net
>
> On 10/11/12, Sandro Santilli <strk at keybit.net> wrote:
> > On Thu, Oct 11, 2012 at 12:10:34PM +0200, Michal Zimmermann wrote:
> >> Sandro,
> >> earlier you said
> >> >One benefit is making the spatial relationships explicit, so that
> >> >you could encode your counties as being composed by the districts,
> >> >and the districts would be composed by faces, which would be
> >> >defined by (shared) edges.
> >> > At that point querying for "all districts in a county" would just
> >> >be a matter of listing the components of a "county" TopoGeometry.
> >>
> >> Does this mean it is better approach to have only one topology
> >> enabled layer (in my case districts) with features having some id
> >> of which county they belong to? In the end, I need to have county >
> >> districts > cadastral areas chain (all of them being represented as polygons).
> >> Right now I have both counties and districts layer with topology,
> >> but I can't see a way to join them (e. g. to "select all districts
> >> from counties where county.id = 1").
> >
> > You mean you have TopoGeometry fields ?
> > Show the output of TopologySummary(<your_topology_name>)
> >
> > --strk;
> >
> > http://www.cartodb.com - Map, analyze and build applications with
> > your data
> >
> > ~~ http://strk.keybit.net
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> --
> Michal Zimmermann (zimmi)
> WWW: http://www.zimmi.cz
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: topo.png
Type: image/png
Size: 329891 bytes
Desc: topo.png
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121012/4ed3ef0e/attachment.png>
More information about the postgis-users
mailing list