[postgis-users] Topology Attributes
Hi, When you build topology from set of connected polygons, the original polygon attributes are not part of the topology model. The model creates MBR face for each original polygon. The edges hold the left right faces. How or what is the right way to add to the edges the right and left original polygons ? -- *Ofer Zilberstein* Mobile: 972-544886873 Skype: ofer.zilberstein Mail: zilberstein.o...@gmail.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Topology Attributes
Hi, When you build the topology, for instance with topology.toTopoGeom(), you get back a TopoGeometry object for each polygon added to the topology. This TopoGeometry is used to keep a link between your polygon object and all the topological elements composing it. The relation table inside the topo schema holding the topology contains the identifier of the topoGeometry object and the identifiers of topo elements composing the polygon. To get the right and left original polygons from the topology, you make a join between edge, relation and pg table: (topo_grass is the topo schema, parcelle is the polygon table with a topo column containing the TopoGeometry object corresponding to the polygon): select e.edge_id, e.left_face, e.right_face, p1.id as left_id, p2.id as right_id from topo_grass.edge e join topo_grass.relation r1 on r1.element_id = e.left_face join pe.parcelle p1 on (1, (p1.topo).id) = (r1.layer_id, r1.topogeo_id) join topo_grass.relation r2 on r2.element_id = e.right_face join pe.parcelle p2 on (1, (p2.topo).id) = (r2.layer_id, r2.topogeo_id); Nicolas On 8 February 2015 at 10:29, Ofer Zilberstein zilberstein.o...@gmail.com wrote: Hi, When you build topology from set of connected polygons, the original polygon attributes are not part of the topology model. The model creates MBR face for each original polygon. The edges hold the left right faces. How or what is the right way to add to the edges the right and left original polygons ? -- *Ofer Zilberstein* Mobile: 972-544886873 Skype: ofer.zilberstein Mail: zilberstein.o...@gmail.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Topology Attributes
Thanks, I'm trying to figure out the process Ofer On Sun, Feb 8, 2015 at 4:58 PM, Richard Greenwood richard.greenw...@gmail.com wrote: I don't know if this will help but here is a link to a PDF http://www.greenwoodmap.com/presentations/WyGEO-Lander-2014.pdf of a presentation I did a while ago. Starting on page 14 I am trying to illustrate how attributes from a simple polygon source can be joined to edges in PostGIS topology . Rich On Sun, Feb 8, 2015 at 2:29 AM, Ofer Zilberstein zilberstein.o...@gmail.com wrote: Hi, When you build topology from set of connected polygons, the original polygon attributes are not part of the topology model. The model creates MBR face for each original polygon. The edges hold the left right faces. How or what is the right way to add to the edges the right and left original polygons ? -- *Ofer Zilberstein* Mobile: 972-544886873 Skype: ofer.zilberstein Mail: zilberstein.o...@gmail.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Richard W. Greenwood, PLS www.greenwoodmap.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- *Ofer Zilberstein* Mobile: 972-544886873 Skype: ofer.zilberstein Mail: zilberstein.o...@gmail.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Topology Attributes
I don't know if this will help but here is a link to a PDF http://www.greenwoodmap.com/presentations/WyGEO-Lander-2014.pdf of a presentation I did a while ago. Starting on page 14 I am trying to illustrate how attributes from a simple polygon source can be joined to edges in PostGIS topology . Rich On Sun, Feb 8, 2015 at 2:29 AM, Ofer Zilberstein zilberstein.o...@gmail.com wrote: Hi, When you build topology from set of connected polygons, the original polygon attributes are not part of the topology model. The model creates MBR face for each original polygon. The edges hold the left right faces. How or what is the right way to add to the edges the right and left original polygons ? -- *Ofer Zilberstein* Mobile: 972-544886873 Skype: ofer.zilberstein Mail: zilberstein.o...@gmail.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Richard W. Greenwood, PLS www.greenwoodmap.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Recursive CTE nearest neighbour function
Hello Mark, Any reason for not using the standard KNN search mechanism ? With the LATERAL keyword you can solve the reference issue of subqueries. Like this : -- find 2 closest bar from each bus stop select bus.gid, bus.name, lat.gid, lat.name, lat.dist from points as bus , lateral ( select bar.gid , st_distance(bar.geom, bus.geom) as dist , bar.name from points as bar where bar.type = 'bar' order by bar.geom - bus.geom -- forbidden without lateral limit 2 ) as lat where bus.type = 'bus_stop' order by bus.gid, lat.dist desc; One good reason for not using this could be the - (and the # one) working only on bounding boxes and not real geometries, therefore sending only approximate results with lines and polygons. Rechecking by distance could be a good idea. Vincent Le dimanche 8 février 2015, 14:59:41 Mark Wynter a écrit : Hi List I’ve been playing with different approaches to KNN problem, given the spatial density of features often varies dramatically across urban and rural settings. I’ve written a recursive CTE function, that uses ST_DWithin recursively, expanding the distance threshold each time (but only if necessary). The function can be found here at github: https://raw.githubusercontent.com/dimensionaledge/cf_public/master/points/DE _KNN.sql I’ve checked out the Boston GIS pgis_fn_nn example and write-up, but am increasingly erring toward the use of recursive CTE functions as my knowledge grows around how they can be applied to dynamic problems. For the KNN recursive function, I’ve compared performance for 3 different use cases, each returning 10500 records. Use Case: 1) 3500 origin points, 1.8 million Linestring features, K=3…. Query time = 20 seconds. 2) 3500 origin points, 3500 Point features, K=3…. Query time = 212 seconds or 3.5 minutes. 3) 3500 origin points, 3500 Polygon features (the same points as in (2) except buffered by 1 meter), K=3…. Query time = 7 seconds. What surprised me is the results of (3) versus (2). From 212 seconds down to 7 seconds. There were geometry indexes on all use cases, but the only difference with (3) is that I buffered the feature set in (2) by 1 meter, in effect creating a Polygon representation of the points. I sense this has something to do with the bounding box comparison used by ST_DWithin, as mentioned in the PostGIS manual. I’ve also studied Paul’s tip here: http://postgis.net/2013/08/26/tip_ST_DWithin I’m keen to get the insights of others, and learn ways of further improving my code patterns … ? How have others dealt with KNN problems in PostGIS when working with BIG point feature sets? Kind regards Mark 1) Query Form WITH results AS (SELECT ogc_fid, DE_knn(wkb_geometry, 'prep.osm_new_roads_poa11_cleaned_full', 'wkb_geometry', 'lid', 0.0001, 10,3) as knn FROM jtw.nsw_tz_centroids) SELECT ogc_fid, (knn).id as lid, (knn).distance, row_number() over (PARTITION BY ogc_fid ORDER BY 3 ASC) FROM results; 2) Query Form WITH results AS (SELECT ogc_fid, DE_knn(wkb_geometry, 'jtw.nsw_tz_centroids', 'wkb_geometry', 'ogc_fid', 0.0001,20,8) as knn FROM jtw.nsw_tz_centroids) SELECT ogc_fid, (knn).id as lid, (knn).distance, row_number() over (PARTITION BY ogc_fid ORDER BY 3 ASC) FROM results; 3) Query Form WITH results AS (SELECT ogc_fid, DE_knn(wkb_geometry, 'jtw.nsw_tz_centroids_buffers', 'wkb_geometry', 'ogc_fid', 0.0001,20,3) as knn FROM jtw.nsw_tz_centroids) SELECT ogc_fid, (knn).id as lid, (knn).distance, row_number() over (PARTITION BY ogc_fid ORDER BY 3 ASC) FROM results; -- Vincent Picavet - Gérant Oslandia www.oslandia.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Getting TopologyExections when trying to node linestrings to create an overlay
On Tue, Feb 3, 2015 at 11:28 AM, BladeOfLight16 bladeofligh...@gmail.com wrote: I'm trying to create a polygon overlay. The basic process is relatively simple: 1) Get the boundaries 2) Union the boundaries to node the linestrings 3) Polygonize the noded outlines 4) Filter out holes using a contains or intersects test. The problem I'm running into is that I'm getting GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING errors from ST_Union. [a lot snipped] I haven't seen any response to this. I was just wondering if anyone else had a chance or intentions to look this over. Granted, it's pretty long and involved (sorry for that), but I thought all the details I included were important. I do know it went through the mailing list; someone on IRC helped me find it in the... I guess it's not the archives; I don't know what it's called. But the online browsing mechanism. Thanks to anyone who's taking a look. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Topology Attributes
Much Thanks, First I will try to understand the join that you did and then try to implement it... Ofer On Sun, Feb 8, 2015 at 12:44 PM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hi, When you build the topology, for instance with topology.toTopoGeom(), you get back a TopoGeometry object for each polygon added to the topology. This TopoGeometry is used to keep a link between your polygon object and all the topological elements composing it. The relation table inside the topo schema holding the topology contains the identifier of the topoGeometry object and the identifiers of topo elements composing the polygon. To get the right and left original polygons from the topology, you make a join between edge, relation and pg table: (topo_grass is the topo schema, parcelle is the polygon table with a topo column containing the TopoGeometry object corresponding to the polygon): select e.edge_id, e.left_face, e.right_face, p1.id as left_id, p2.id as right_id from topo_grass.edge e join topo_grass.relation r1 on r1.element_id = e.left_face join pe.parcelle p1 on (1, (p1.topo).id) = (r1.layer_id, r1.topogeo_id) join topo_grass.relation r2 on r2.element_id = e.right_face join pe.parcelle p2 on (1, (p2.topo).id) = (r2.layer_id, r2.topogeo_id); Nicolas On 8 February 2015 at 10:29, Ofer Zilberstein zilberstein.o...@gmail.com wrote: Hi, When you build topology from set of connected polygons, the original polygon attributes are not part of the topology model. The model creates MBR face for each original polygon. The edges hold the left right faces. How or what is the right way to add to the edges the right and left original polygons ? -- *Ofer Zilberstein* Mobile: 972-544886873 Skype: ofer.zilberstein Mail: zilberstein.o...@gmail.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- *Ofer Zilberstein* Mobile: 972-544886873 Skype: ofer.zilberstein Mail: zilberstein.o...@gmail.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Recursive CTE nearest neighbour function
Hi Vincent. When doing KNN searches on point to point datasets, your suggestion is perfect! Using your approach, Use Case (2) case took 0.7 seconds, down from my previous best of 7 seconds. The reason I didn’t go down this pathway initially was for the reasons you mentioned - bounding box approximations of lines and polygons and the potential for “inaccuracies.” My use case that prompted this was finding OSM road linestrings closest to points. Seeing your code, I’ve also got new appreciation for the keyword LATERAL. Many thanks, Mark ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Getting TopologyExections when trying to node linestrings to create an overlay
Il 09/02/2015 02:31, John Abraham ha scritto: Well I could say that using PostGIS ST_Intersects with messy data always seems to give me TopologyExceptions. I've had luck with various combinations of ST_SnapToGrid and ST_Buffer(0), but with messy data there always seems to be some weird case that requires manually edits. Hi John, have you tried ST_MakeValid? All the best. -- Paolo Cavallini - www.faunalia.eu QGIS PostGIS courses: http://www.faunalia.eu/training.html *New course* QGIS for naturalists: http://www.faunalia.eu/en/nat_course.html ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Getting TopologyExections when trying to node linestrings to create an overlay
Well I could say that using PostGIS ST_Intersects with messy data always seems to give me TopologyExceptions. I've had luck with various combinations of ST_SnapToGrid and ST_Buffer(0), but with messy data there always seems to be some weird case that requires manually edits. Sorry to be the bearer of bad news. Frankly, I don't understand why the GEOS library has to throw that error. I would encourage you to isolate particular problems and file bug reports. Improvements in GEOS to eliminate the underlying error(s) would certainly be welcome. -- John Abraham On Feb 8, 2015, at 1:43 PM, BladeOfLight16 bladeofligh...@gmail.com wrote: On Tue, Feb 3, 2015 at 11:28 AM, BladeOfLight16 bladeofligh...@gmail.com mailto:bladeofligh...@gmail.com wrote: I'm trying to create a polygon overlay. The basic process is relatively simple: 1) Get the boundaries 2) Union the boundaries to node the linestrings 3) Polygonize the noded outlines 4) Filter out holes using a contains or intersects test. The problem I'm running into is that I'm getting GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING errors from ST_Union. [a lot snipped] I haven't seen any response to this. I was just wondering if anyone else had a chance or intentions to look this over. Granted, it's pretty long and involved (sorry for that), but I thought all the details I included were important. I do know it went through the mailing list; someone on IRC helped me find it in the... I guess it's not the archives; I don't know what it's called. But the online browsing mechanism. Thanks to anyone who's taking a look. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
