[postgis-users] topology example
Denis Rouzaud
Is there somewhere on the web a basic tutorial on how to deal with topology?
I am looking for a small step-by-step introduction.
I looked for a while: chapter 11 of current doc, the wiki, some
presentations, strk's blog...
Basically I have a table of pipes, and I would like to enable the
topology in it...
Thanks a lot,
Denis
_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Sandro Santilli
> Hi all,
>
> Is there somewhere on the web a basic tutorial on how to deal with topology?
> I am looking for a small step-by-step introduction.
>
> I looked for a while: chapter 11 of current doc, the wiki, some
> presentations, strk's blog...
>
> Basically I have a table of pipes, and I would like to enable the
> topology in it...
SELECT CreateTopology('pipes_topo', find_srid('public', 'pipes', 'geom));
SELECT AddTopoGeometryColumn('pipes_topo', 'pipes', 'topogeom', 'LINE');
UPDATE pipes SET topogeom = toTopoGeom(geom, 'pipes_topo', 1);
Good luck!
--strk;
,------o-.
| __/ | Delivering high quality PostGIS 2.0 !
| / 2.0 | http://strk.keybit.net - http://vizzuality.com
`-o------'
Denis Rouzaud
Thanks a lot!
Can you also give me hint on what to do when I have an error:
psql:topology.sql:14: ERROR: SQL/MM Spatial exception - point not on edge
Basically, I wanted to create the topology to check it...
I think I cannot do anything with validatetopology until toTopoGeom is
achieved right?
Thanks again!!!
Sandro Santilli
> Hi Sandro,
>
> Thanks a lot!
> Can you also give me hint on what to do when I have an error:
> psql:topology.sql:14: ERROR: SQL/MM Spatial exception - point not on edge
Upgrade to latest GEOS and PostGIS
(don't forget to load the topology_upgrade.sql file!)
If the problem persists split your update calls in smaller
ranges until you hit the exception. Further reduce the chunk
until you get (if ever) to the offending geometry.
Note that order in which geometries are added into the topology
matters so you may even NOT seeing the problem anymore when changing
such order.
> Basically, I wanted to create the topology to check it...
> I think I cannot do anything with validatetopology until toTopoGeom
> is achieved right?
Well, ValidateTopology only checks the primitives, not the higher
level features. If you know GRASS higher level features validation
is the presence polygon-0 and polygon-2 objects.
If all you want is primitives validation you could use ST_CreateTopoGeo
passing it a collection of all your pipes.
Denis Rouzaud
Thanks again.
I have a fresh installed for 2 days now.
Postgis 2.0 a6 (it is a 7 now)
Geos 3.3.2
Is this ok?
I did not do anything with topology_upgrade.sql, there is nothing
written in the installation doc.
What should I do with it?
Would you perform a loop to update the topogeom field, so one entry at once?
Thanks a lot for your valuable help!
Greetings
Denis
Sandro Santilli
> Hi Sandro,
>
>
> I have a fresh installed for 2 days now.
> Postgis 2.0 a6 (it is a 7 now)
> Geos 3.3.2
> Is this ok?
Should be, yes.
> I did not do anything with topology_upgrade.sql, there is nothing
> written in the installation doc.
That's a bug in the documentation, could you file it on
http://trac.osgeo.org please ?
> What should I do with it?
The same you do with postgis_upgrade.sql everytime you do upgrade
from one version to another... load it.
> Would you perform a loop to update the topogeom field, so one entry at once?
A loop would indeed be preferrable. I saw a noticable speedup
when looping from outside the database.
> Thanks a lot for your valuable help!
Thank you for your feedback, keep me posted ! :)
Denis Rouzaud
As I have installed from scratch the upgrade files are useless for me,
right?
(I am getting lost...)
Ticket #1611 has been filled
I'll try to do the loop today and check.
I'll let you know!
Sandro Santilli
> As I have installed from scratch the upgrade files are useless for
> me, right?
Yes. Sorry I assumed you were upgrading.
For a new install you just needed to load topology.sql,
which you evidently did.
Denis Rouzaud
So I am looping over my 25000 pipes and soon as there is a topology
error, the
UPDATE distribution.pipes SET topogeom = toTopoGeom(wkb_geometry,
''distrib_topo'', layer) WHERE id = pi.id;
throws an error:
ERROR: SQL/MM Spatial exception - point not on edge
I would like to list all the errors by raising the ids, what is the
exception to catch?
Thanks......
Sandro Santilli
> Okay,
>
> So I am looping over my 25000 pipes and soon as there is a topology
> error, the
> UPDATE distribution.pipes SET topogeom = toTopoGeom(wkb_geometry,
> ''distrib_topo'', layer) WHERE id = pi.id;
> throws an error:
> ERROR: SQL/MM Spatial exception - point not on edge
Excellent. So a _single_ geometry is enough to reproduce the error.
Save both the new geometry (WHERE id = pi.id) and the topology
status (dump the schema).
If you can, attach the dump and the new input into a ticket.
May be worth checking the topology built so far for correctness
with ValidateTopology. Any invalidity would be a bug in PostGIS.
But not all invalidities are cought by ValidateTopology so there
may be a need to do more eyebased checking (linking isn't checked).
Usually the invalidity, if any, is within the bounding box
of the geometry that fails being installed.
> I would like to list all the errors by raising the ids, what is the
> exception to catch?
A loop _outside_ the database wouldn't need to catch.
If you're looping inside, with pl/pgsql, catch OTHERS.
Denis Rouzaud
https://github.com/3nids/qWat/blob/7bc65b5a43ffb9ae5947ede683ff1f2274807972/sql/topology.sql
I know I have errors in topology in my pipes. So, is this correct than I can use toTopoGeom for geometries that have incorrect topology?
Or should I check them first?
Here is the result of validate topology when a part of my pipes is in the topology column:
SELECT topology.ValidateTopology('distrib_topo');
validatetopology
---------------------------
("face without edges",0,)
(1 row)
I can dump the db, this will be a few megas, but I would like to be sure I am not doing anything stupid, first....
Sandro Santilli
> To be sure, we understand each others, here is the few lines of SQL
> I am using:
> https://github.com/3nids/qWat/blob/7bc65b5a43ffb9ae5947ede683ff1f2274807972/sql/topology.sql
It seems good to me. Do I read it correctly that it exits
the loop on first error ?
May I suggest you ORDER BY id in the loop ?
Or the order would not be predictable.
> I know I have errors in topology in my pipes. So, is this correct
> than I can use toTopoGeom for geometries that have incorrect
> topology?
Well, they should be fine for the population of primitives,
may give problems in the mapping between original features
and topology primitives. In any case the exception you got
doesn't reveal such problem.
> Here is the result of validate topology when a part of my pipes is
> in the topology column:
> SELECT topology.ValidateTopology('distrib_topo');
> validatetopology
> ---------------------------
> ("face without edges",0,)
> (1 row)
Uhm, that's a false positive, I tought I fixed it.
Evidently I didn't. Want to file a ticket about it ?
> I can dump the db, this will be a few megas, but I would like to be
> sure I am not doing anything stupid, first....
It would help me as well if you reduce it.
The important thing is that insertion of your offending geometry
keeps hitting the exception.
You could for example try dropping edges and then nodes
which do not intersect the offending geometry's bounding box.
And see if the problem still occurs.
Best to backup the whole set first.
Denis Rouzaud
https://github.com/3nids/qWat/blob/3c0c6728b940dd4ab2921dee104cd054a711d60f/sql/topology.sql
You effectively read correctly that the loop is ended as soon as an exception is raised, since I still didn't manage to do it properly.
I filled ticket 1611, http://trac.osgeo.org/postgis/ticket/1612 Sorry, for the lack of info in, but I don't really understand the matter.
Also, I noticed something strange. Nothing is filled into the topogeom column. When ordered, first error is raised at id 102, but the first 100 lines have empty topogeom. In the function, if an error is raised nothing is committed?
I wrote a small SQL with two geometries where I have the error.
https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql
I putted a some " !!! " in front of drop schema to prevent any harm to some of your schemas....just in case!
Greetings,
Denis
Sandro Santilli
> Ok, I added ORDER BY
> https://github.com/3nids/qWat/blob/3c0c6728b940dd4ab2921dee104cd054a711d60f/sql/topology.sql
>
> You effectively read correctly that the loop is ended as soon as an
> exception is raised, since I still didn't manage to do it properly.
That's actually good. If the goal is finding the bug :)
The smaller topology you have at time of exception, the better.
> Also, I noticed something strange. Nothing is filled into the
> topogeom column. When ordered, first error is raised at id 102, but
> the first 100 lines have empty topogeom. In the function, if an
> error is raised nothing is committed?
The version I saw didn't look like in need to rollback, no.
But I could have misread it.
> I wrote a small SQL with two geometries where I have the error.
> https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql
> I putted a some " !!! " in front of drop schema to prevent any harm
> to some of your schemas....just in case!
That's great ! Could you put in another ticket please ?
I was actually out of topology tickets for 2.0 ;)
Not sure when I'll have time to look at it but it's really an excellent
work you've done so far. If you feel like doing more you may try
adding the following line by the start of topology/topology.sql.in.c,
running 'make' again and loading the topology/topology_upgrade.sql:
#define POSTGIS_TOPOLOGY_DEBUG 1
By the way, did "make check" completed cleanly for you ?
Denis Rouzaud
The version I saw didn't look like in need to rollback, no.
But I could have misread it.
That's great ! Could you put in another ticket please ?
> I wrote a small SQL with two geometries where I have the error.
> https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql
> I putted a some " !!! " in front of drop schema to prevent any harm
> to some of your schemas....just in case!
I was actually out of topology tickets for 2.0 ;)
Not sure when I'll have time to look at it but it's really an excellent
work you've done so far. If you feel like doing more you may try
adding the following line by the start of topology/topology.sql.in.c,
running 'make' again and loading the topology/topology_upgrade.sql:
#define POSTGIS_TOPOLOGY_DEBUG 1
I'll try this on monday.
By the way, did "make check" completed cleanly for you ?
I don't remember what went out. I'll check this on monday morning and give you feedback.
Thanks a lot for your time and work!
Maybe, everything will autofix during the weekend ...
Greetings,
Denis
Stephen Woodbridge
>
> The version I saw didn't look like in need to rollback, no.
> But I could have misread it.
>
>
> Agree, so I do not understand why there nothing filled.
If your are running a stored procedure and you error out of it, the
whole stored procedure is always run as a transaction, an on error it
does an implicit rollback.
If you want to verify that the stored procedure is run in an
transaction, just try and do a vacuum in the stored procedure -- you
can't. Or write a stored procedure that creates a table inserts a couple
of rows, then does a RAISE EXCEPTION. You should see that the table does
not exist after this.
-Steve
> > I wrote a small SQL with two geometries where I have the error.
> >
> https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql
> > I putted a some " !!! " in front of drop schema to prevent any harm
> > to some of your schemas....just in case!
>
> That's great ! Could you put in another ticket please ?
> I was actually out of topology tickets for 2.0 ;)
>
>
> Three in a row ;)
> http://trac.osgeo.org/postgis/ticket/1613
>
>
> Not sure when I'll have time to look at it but it's really an excellent
> work you've done so far. If you feel like doing more you may try
> adding the following line by the start of topology/topology.sql.in.c,
> running 'make' again and loading the topology/topology_upgrade.sql:
>
> #define POSTGIS_TOPOLOGY_DEBUG 1
>
>
> I'll try this on monday.
>
>
> By the way, did "make check" completed cleanly for you ?
>
>
> I don't remember what went out. I'll check this on monday morning and
> give you feedback.
>
> Thanks a lot for your time and work!
> Maybe, everything will autofix during the weekend ...
>
> Greetings,
>
> Denis
>
>
>
>
Sandro Santilli
> On 2/24/2012 10:44 AM, Denis Rouzaud wrote:
> >
> > The version I saw didn't look like in need to rollback, no.
> > But I could have misread it.
> >
> >
> >Agree, so I do not understand why there nothing filled.
>
> If your are running a stored procedure and you error out of it, the
> whole stored procedure is always run as a transaction, an on error
> it does an implicit rollback.
But he's supposedly catching the exception from within the function.
--strk;
Sandro Santilli
> Thanks a lot for your time and work!
> Maybe, everything will autofix during the weekend ...
Denis, this mail just to tell you that the ticket [1] has been worked
a bit upon, but needs much more work. It's a robustness issue which
may or may not be worked-around of by adding a tolerance parameter
to your toTopoGeom calls. Let me know if it does.
[1] http://trac.osgeo.org/postgis/ticket/1613
Denis Rouzaud
Thanks a lot for your attention.
I did not had so much time to look deeply into it these two days.
So far, I could not perform make check correctly due to some passwords
problems.
Basically, I have a database in oracle (non spatial) in topobase
(autocad) with pipes. I am migrating the whole thing to use Qgis to
manage the network.
So I collected all the given errors when using toTopoGeom to first
correct the geometry in autocad, then re-importing and re-creating the
topology again.
I don't really understand how the topology is handled in postgis and
what is for example written in the topogeom column.
There is still one main point which I don't understand. Why is there any
control on the topology if there is validateTopology function?
Sandro Santilli
> Hi Sandro,
>
> Thanks a lot for your attention.
> I did not had so much time to look deeply into it these two days.
>
> So far, I could not perform make check correctly due to some
> passwords problems.
You might need to play with environment variables for setting password
NOTE that I just committed a fix for the problem you were having.
> Basically, I have a database in oracle (non spatial) in topobase
> (autocad) with pipes. I am migrating the whole thing to use Qgis to
> manage the network.
You're probably looking for network topology then, which is not
the kind supported by PostGIS core. We do planar topology for now.
> So I collected all the given errors when using toTopoGeom to first
> correct the geometry in autocad, then re-importing and re-creating
> the topology again.
The idea of toTopoGeom is it never reports errors...
> I don't really understand how the topology is handled in postgis and
> what is for example written in the topogeom column.
You get a TopoGeometry value, which you may think of as a special kind
of Geometry and threat as such (it casts to Geometry). The postgis wiki
contains more informations about the details.
> There is still one main point which I don't understand. Why is there
> any control on the topology if there is validateTopology function?
There are more and less expensive controls, at different levels.
What you did hit was an internal consistency check that revealed a
conceptual bug in the code.
Denis Rouzaud
Thanks a lot for these explanations, things are getting clearer.
The difference between planar and network topology is that edges can
cross without being connected, right?
I will rebuild postgis and go further!!!
Again, many thanks for your work!
Denis
Sandro Santilli
> The difference between planar and network topology is that edges can
> cross without being connected, right?
Right.
> I will rebuild postgis and go further!!!
Welcome !
--strk;
,------o-.
| __/ | Delivering high quality PostGIS 2.0 !
| / 2.0 | http://strk.keybit.net - http://vizzuality.com
`-o------'
_______________________________________________