Re: [postgis-users] Topology Attributes
Hi, As usual with SQL, there must be several other ways to perform the same query. As the edge view stores 2 faces id per row (left and right), I duplicated the joins to join both left_face and right face against the original polygon table (p1 and p2 aliases) Nicolas On 8 February 2015 at 21:57, Ofer Zilberstein zilberstein.o...@gmail.com wrote: 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 ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] Hardware requirements for a server
Dear PostGIS users, I am currently planning to set up a PostGIS instance for my lab. Turns out I believe this would be useful for the whole center, so that I'm now considering setting up a PostGIS server for everyone—if interest is shared of course. At the moment, I am however struggling with what would be required in terms of hardware, and of course, the cost will depend on that—at the end of the day, it's really a matter of money well spent. I have then a series of questions/remarks, and I would welcome any feedback from people with existing experience on setting up a multi-user PostGIS server. * My own experience is rather limited: I used PostGIS quite a bit, but only on a desktop, with 2 users. The desktop was quite good (quad-core Xeon, 12 Go RAM, 500 GB hd), running Debian, and we never had any performance issue (although some queries were rather long, but still acceptable). * The use case I'm envisioning would be (at least in the foreseeable future): - About 10 faculty users (which means potentially a little bit more students using it); I would have hard time considering more than 4 concurrent users; - Data would primarily involve a lot (hundreds/thousands) of high resolution (spatial and temporal) raster and vector maps, possibly over large areas (Florida / USA / continental), as well as potentially millions of GPS records (animals individually monitored); - Queries will primarily involve retrieving points/maps over given areas/time, as well as intersecting points over environmental layers; other use cases will involve working with steps, i.e. the straight line segment connecting two successive locations, and intersecting them with environmental layers; * I couldn't find comprehensive or detailed guidelines on-line about hardware, but from what I could see, it seems that memory wouldn't be the main issue, but the number of cores would be (one core per database connection if I'm not mistaken). At the same time, we want to make sure that the experience is smooth for everyone... * Is there a difference in terms of performance and usability between a Linux-based and a MS-based server? My center is unfortunately MS-centered, and existing equipment runs with MS systems... It would thus be easier for them to set up a MS-based server. * Does anyone have worked with a server running the DB engine, while the DB itself was stored on another box/server? That would likely be the case here since we already have a dedicated box for file storage. Along these lines, does the system of the file storage box matter (Linux vs. MS)? * We may also use the server as a workstation to streamline PostGIS processing with further R analyses/modeling (or even use R from within the database using PL/R). Again, does anyone have experience doing it? Is a single workstation the recommended way to work with such workflow? Or would it be better (but more costly) to have one server dedicated to PostGIS and another one, with different specs, dedicated to analyses (R)? I realize my questions and comments may be a confusing, likely because of the lack of experience about these issues on my side. I really welcome any feedback of people working with PostGIS servers in a small unit, or any similar setting that could be informative! In advance, thank you very much! Sincerely, Mathieu Basille. -- ~$ whoami Mathieu Basille http://ase-research.org/basille ~$ locate --details University of Florida \\ Fort Lauderdale Research and Education Center (+1) 954-577-6314 ~$ fortune « Le tout est de tout dire, et je manque de mots Et je manque de temps, et je manque d'audace. » -- Paul Éluard ___ 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, 1. I advanced ...and I used the topology.toTopoGeom() and now the relation is populated. 2. I try to understand the following join *pe.parcelle** p1 on (1, (p1.topo).id) *= (r1.layer_id, r1.topogeo_id) the second part of the join is clear -- comes from the relation table however the first part - is not so clear according to you - parcelle is the polygon table with a topo column containing the TopoGeometry object corresponding to the polygon (you have used here pe.parcelle - is it the table name ? ) so if in my case I have table 'department' with column 'topogeom' it would look like department d1 on (1,(d1.topogeom).id) = (r1.layer_id, r1.topogeo_id) ? Ofer On Mon, Feb 9, 2015 at 3:52 PM, Ofer Zilberstein zilberstein.o...@gmail.com wrote: Hi Nicolas, 1. I used ST_CreateTopoGeo() to build the topology (not the topology.toTopoGeom() as you mentioned ) When I look at the relation table its empty ? should I use topology.toTopoGeom() instead, and it will populate it ? 2. can you explain what the parentheses (special the 1, ...) part mean... join pe.parcelle p1 on *(1, (p1.topo).id)* = (r1.layer_id, r1.topogeo_id) Thanks in advanced ... Ofer On Mon, Feb 9, 2015 at 11:05 AM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hi, As usual with SQL, there must be several other ways to perform the same query. As the edge view stores 2 faces id per row (left and right), I duplicated the joins to join both left_face and right face against the original polygon table (p1 and p2 aliases) Nicolas On 8 February 2015 at 21:57, Ofer Zilberstein zilberstein.o...@gmail.com wrote: 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 ___ 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 -- *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] Getting TopologyExections when trying to node linestrings to create an overlay
On Sun, Feb 8, 2015 at 9:31 PM, John Abraham j...@hbaspecto.com wrote: 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. That's pretty much what my e-mail does; it even includes a sample database to reproduce the issue. I brought it up here in case some discussion or slimming down needed/could be done before filing a bug report, and to be extra sure that it's a GEOS issue and not a PostGIS once, given the complexity of the query I'm running. ___ 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
Hey, I executed your data, the following command solve the problem (with very recent GEOS for me) (POSTGIS=2.2.0dev r12846 GEOS=3.5.0dev-CAPI-1.9.0 r0 PROJ=Rel. 4.8.0, 6 March 2012 GDAL=GDAL 2.0.0dev, released 2014/04/16 LIBXML=2.8.0 RASTER) : DROP TABLE IF EXISTS unique_polygon ; CREATE TABLE unique_polygon AS SELECT geom_set_id, row_number() over() as gid, ST_Translate(dmp.geom, - 385614, - 4795454 ) AS geom FROM error_generating_polygons,unnest(polygons) as geomn, st_dump(geomn) as dmp; CREATE INDEX ON unique_polygon USING GIST(geom) ; DRoP TABLE IF EXISTS unioned_poly ; CREATE TABLE unioned_poly AS SELECT ST_Union( ST_MakePolygon(ST_ExteriorRing(geom)) ) FROM unique_polygon GROUP BY geom_set_id (150 sec) The change compared to your approach : convert input to table of simple polygons, (no array, no multi). Then translate to improve precision in geos computing Then the union. I don't really understand what you are trying to do, but ist_union seems dangerous and quit ineffective for that . Of course reducing the number of useless points before union make it 10 times faster . DRoP TABLE IF EXISTS unioned_poly ; CREATE TABLE unioned_poly AS SELECT ST_Union( ST_Buffer( ST_MakePolygon( ST_ExteriorRing( ST_SImplifyPreserveTopology( geom ,10 ) ) ) ,1 ) ) FROM unique_polygon GROUP BY geom_set_id (17 sec) Cheers, Rémi-C 2015-02-09 13:00 GMT+01:00 Rémi Cura remi.c...@gmail.com: Hey Sandro, this is a precision related issue, coordinates are way too big and should be translated. Cheers, Rémi-C 2015-02-09 12:25 GMT+01:00 Sandro Santilli s...@keybit.net: On Tue, Feb 03, 2015 at 11:28:35AM -0500, BladeOfLight16 wrote: https://drive.google.com/file/d/0B_6I7kRgE8teVUpha2Q4ZlNDMWs/view?usp=sharing . ... DO $$ DECLARE problem_row error_generating_polygons%ROWTYPE; BEGIN FOR problem_row IN (SELECT * FROM error_generating_polygons) LOOP BEGIN PERFORM ST_Union(ST_Boundary(geom)) FROM UNNEST(problem_row.polygons) p (geom); RAISE NOTICE 'geom_set_id % succeeded', problem_row.geom_set_id; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Error for geom_set_id % (Code %): %', problem_row.geom_set_id, SQLSTATE, SQLERRM; END; END LOOP; END $$; First of all I confirm it still happens with GEOS=3.5.0dev-CAPI-1.9.0 r4038. Second, I took a look at a random set (geom_set_id=1) and I found it pretty big. That's to say you could probably further reduce the dataset for the ticket. That set contains 109 polygons, I can get the error by attempting to union the boundaries of the first 40 in that set, and I'm sure you can further reduce the input. So my suggestion: 1) file the ticket 2) attach the _smallest_ input that reproduces the problem About ST_IsValid: lines are always valid, so there's no need to test. Most likely this is a robustness issue failing to deal with very close but not equal lines. NOTE: I've tried my reduced input (~40) geoms against the topology builder and it also resulted in errors, until I specified a tolerance of 1e-4. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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] Getting TopologyExections when trying to node linestrings to create an overlay
Hey Sandro, this is a precision related issue, coordinates are way too big and should be translated. Cheers, Rémi-C 2015-02-09 12:25 GMT+01:00 Sandro Santilli s...@keybit.net: On Tue, Feb 03, 2015 at 11:28:35AM -0500, BladeOfLight16 wrote: https://drive.google.com/file/d/0B_6I7kRgE8teVUpha2Q4ZlNDMWs/view?usp=sharing . ... DO $$ DECLARE problem_row error_generating_polygons%ROWTYPE; BEGIN FOR problem_row IN (SELECT * FROM error_generating_polygons) LOOP BEGIN PERFORM ST_Union(ST_Boundary(geom)) FROM UNNEST(problem_row.polygons) p (geom); RAISE NOTICE 'geom_set_id % succeeded', problem_row.geom_set_id; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Error for geom_set_id % (Code %): %', problem_row.geom_set_id, SQLSTATE, SQLERRM; END; END LOOP; END $$; First of all I confirm it still happens with GEOS=3.5.0dev-CAPI-1.9.0 r4038. Second, I took a look at a random set (geom_set_id=1) and I found it pretty big. That's to say you could probably further reduce the dataset for the ticket. That set contains 109 polygons, I can get the error by attempting to union the boundaries of the first 40 in that set, and I'm sure you can further reduce the input. So my suggestion: 1) file the ticket 2) attach the _smallest_ input that reproduces the problem About ST_IsValid: lines are always valid, so there's no need to test. Most likely this is a robustness issue failing to deal with very close but not equal lines. NOTE: I've tried my reduced input (~40) geoms against the topology builder and it also resulted in errors, until I specified a tolerance of 1e-4. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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] Getting TopologyExections when trying to node linestrings to create an overlay
On Tue, Feb 03, 2015 at 11:28:35AM -0500, BladeOfLight16 wrote: https://drive.google.com/file/d/0B_6I7kRgE8teVUpha2Q4ZlNDMWs/view?usp=sharing. ... DO $$ DECLARE problem_row error_generating_polygons%ROWTYPE; BEGIN FOR problem_row IN (SELECT * FROM error_generating_polygons) LOOP BEGIN PERFORM ST_Union(ST_Boundary(geom)) FROM UNNEST(problem_row.polygons) p (geom); RAISE NOTICE 'geom_set_id % succeeded', problem_row.geom_set_id; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Error for geom_set_id % (Code %): %', problem_row.geom_set_id, SQLSTATE, SQLERRM; END; END LOOP; END $$; First of all I confirm it still happens with GEOS=3.5.0dev-CAPI-1.9.0 r4038. Second, I took a look at a random set (geom_set_id=1) and I found it pretty big. That's to say you could probably further reduce the dataset for the ticket. That set contains 109 polygons, I can get the error by attempting to union the boundaries of the first 40 in that set, and I'm sure you can further reduce the input. So my suggestion: 1) file the ticket 2) attach the _smallest_ input that reproduces the problem About ST_IsValid: lines are always valid, so there's no need to test. Most likely this is a robustness issue failing to deal with very close but not equal lines. NOTE: I've tried my reduced input (~40) geoms against the topology builder and it also resulted in errors, until I specified a tolerance of 1e-4. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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 Nicolas, 1. I used ST_CreateTopoGeo() to build the topology (not the topology.toTopoGeom() as you mentioned ) When I look at the relation table its empty ? should I use topology.toTopoGeom() instead, and it will populate it ? 2. can you explain what the parentheses (special the 1, ...) part mean... join pe.parcelle p1 on *(1, (p1.topo).id)* = (r1.layer_id, r1.topogeo_id) Thanks in advanced ... Ofer On Mon, Feb 9, 2015 at 11:05 AM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hi, As usual with SQL, there must be several other ways to perform the same query. As the edge view stores 2 faces id per row (left and right), I duplicated the joins to join both left_face and right face against the original polygon table (p1 and p2 aliases) Nicolas On 8 February 2015 at 21:57, Ofer Zilberstein zilberstein.o...@gmail.com wrote: 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 ___ 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
