0

With PostGIS, will migrating polygons into topologies using toTopoGeom() provide any benefit with regard to point-in-polygon search?

I have a lot of data, roughly 15 sets of data. And, I am wondering if there would be a benefit to migrating all of it to topology. Currently, 100% of my queries are point-in-polygon. However, most of these queries could benefit from some kind of subdividing and simplification.

Take for instance the TIGER States file. If 100% of your workload included finding out what state a point was in, what would be the benefit of migrating those polygons into topologies? What I'm thinking is I can avoid overlaps in the event of simplification, and that syntactically I'm concerned only with which state a user in in.

I'm just not sure I understand topology and its benefit to polygons, but it's appealing to me to know that by defining the edge any form of movement of that edge results in maintaining two faces.

geometry based simplification

topology based simplification

This is demonstrated in section 13.23. and 13.24 of PostGIS in Action, 2nd Edition by Regina O. Obe and Leo S. Hsu. The pictures above are taken from the text.

  • 1
    Sorry, if you have covered this in the comments, but you are talking about fundamentally different use cases. TopoJSON (and cousins) I believe is more useful for data compression, when you are sending large amounts of data down the wire. For, backend Postgres/Postgis queries involving point in polygon, etc, I don't think there is any advantage at all -- in fact it might be a disadvantage, as it would require rewritting algorithms to use different data structures that reference parts of a polygon by a linestring id rather than using a complete geometry. – John Powell Feb 9 '17 at 20:11
  • Isn't this what GetFaceByPoint does? – Evan Carroll Feb 9 '17 at 20:13
  • 1
    Uf, fair point on the underlying implemetation of GetFaceByPoint. Fortunately, I'm working a bit on road networks at the moment, so might get a chance to try that out. Thanks for bring to my attention. – John Powell Feb 9 '17 at 20:17
  • 1
    However, I would still go with "if it ain't broke don't fix it" response. Topology in Postgis is for routing and other graph type queries. TopoJSON is essentially about data compression. The existing Postgis spatial relate operators work as expected, and it is hard to see how they could be better implemented using topologies over geometies. – John Powell Feb 9 '17 at 20:18
  • 1
    I wasn't one of those who voted to close this, and,in fact, I would vote to reopen it. Agreed, I think topology is very unexplored in Postgis, and has a lot of potential uses. Also, I just upvoted one of you other answers, which was excellent, as I know how nice it is to arrive to 2K. Your contributions on this site are well appreciated. – John Powell Feb 9 '17 at 20:25
4

To optimize point-in-polygon (PIP) queries, you're better off focusing on reducing the size and number of vertices in your geometries (using ST_Subdivide) so that:

  • your index becomes more selective, reducing the number of actual point-in-polygons tests that need to be run. (For example, you'll no longer have half of West Virginia in Virginia's bounding box)
  • the individual PIP tests become faster, because they have fewer vertices. This comes into play both in geometry deserialization and the PIP test itself. Even if you heavily generalize TIGER's Alaska polygon, it's still going to have a lot of vertices.

If you also want to simplify the geometries, you can do this using PostGIS topology or other tools like MapShaper. But there is lower overhead to querying geometry objects directly, rather than going through GetFaceByPoint. So even if you use PostGIS topology to generalize, you should get better performance by converting back to regular geometries.

I doubt the rounding errors introduced by ST_SubDivide would affect a practical PIP application (especially one in which you are willing to generalize), but you can guard against them in any case by:

  • adding a LIMIT 1 on your query, so that you only get one result in the event of an overlap. (Yes, it's a random error, but so is generalization)
  • falling back to a ST_DWithin with a 1e-10 tolerance or something if no intersection is found

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.