James Keener | 29 Aug 02:45 2015

pgr_drivingdistance problem

When I run pgr_drivingdistance on an small extract (578 rows) (attached) from an extract of Pennsylvania's osm pbf file not all edges are being followed. I first downloaded the pbf. Then I imported it with osm2pgrouting osmosis --read-pbf file=pennsylvania-latest.osm.pbf --write-xml pennsylvania-latest.osm osm2pgrouting -file pennsylvania-latest.osm -conf /usr/share/osm2pgrouting/mapconfig.xml -dbname osm3 -user jim -skipnodes -passwd hunter2 and then running CREATE TABLE smallways AS SELECT * FROM ways WHERE ST_DISTANCE(the_geom, ST_GEOMFROMTEXT('POINT(-79.900121 40.448889)', 4326)) < 0.2; and creating the appropriate indices to build a test environment (the output of which is the small extract (578 rows)(attached)). To test pgr_drivingdistance, I did the following: CREATE OR REPLACE VIEW testing AS SELECT gid, the_geom FROM ( SELECT seq, id1 AS vertext_id, id2 AS gid, cost FROM pgr_drivingdistance( 'SELECT gid AS id, source, target, length AS cost, reverse_cost FROM smallways', 179103, 0.5, false, true) ) t INNER JOIN smallways USING (gid); and then loading that view into QGIS, I see the following:
Why are some edges not being selected? I initially thought it was the (source, target) (labels in screenshot) ordering, but that doesn't seem to be the case, as some edges such as 148517 to the SW of the annotated starting point, seems to be in good order.

Attachment (smallways.sql.bz2): application/x-bzip, 52 KiB
postgis-users mailing list
postgis-users <at> lists.osgeo.org
Lee Hachadoorian | 26 Aug 21:01 2015

Loading a PostGIS dump

New Linux machine, new PostGIS install (postgresql-9.3-postgis-2.1). I
created DB and added extensions. Now trying to load database using
postgis_restore.pl. This is a large database (63GB dump, created from
DB that was probably 600GB or more).

The script ran for a while. Then it stopped at

(1 row)

It's been hanging out there for hours now, so I assume something is
hopelessly wrong.

The loader created foo.dump.lst at 10:50am and I piped errors to a
text file. Errors file shows that I did not create a user who owns one
of the schemas. `ERROR:  role "geo_user" does not exist` appears 260

Can I just kill the loader script with Ctrl+C? Is database
recoverable? Should I just drop database and try again from the
beginning? Is failure related to missing role, or if not how do I
figure out what the error is so that I can get this to run to



Lee Hachadoorian
Asst Professor of Geography, Temple University
Christoph Mayrhofer | 24 Aug 18:09 2015

floyd-warhall all pairs shortest path


I looked into all pairs shortest path routing algorithms to use for traffic simulations. 
I found that the Floyd–Warshall algorithm works well for my purpose. 

pgRouting has a function for this which produces a table with the shortest path distance between all source/destination pairs.

In order to get the actual paths rather than only distances it suffices to make a minor adaption to the algorithm as described in the path reconstruction section in https://en.wikipedia.org/wiki/Floyd%E2%80%93Warshall_algorithm

It is basically supposed to output the first node of the shortest path between the source and destination in addition to the overall distance of that route. 
This information is sufficient to reconstruct all paths using the parent child relationship recursively.

Does pgr_apspWarshall support this?
Or can anyone point to the person that implemented pgr_apspWarshall?

So far I use my own implementation outside of PostGIS, but I think whis functionality might be of interest for others too.

best regards, Christoph Mayrhofer
postgis-users mailing list
postgis-users <at> lists.osgeo.org
zach cruise | 21 Aug 21:32 2015

Re: [postgis-devel] split one geojson into tiles

ok i'll take a look
MAURER Roger | 21 Aug 16:06 2015

Intersection between lines and polygons with lengths by nature of polygons

The following statement of intersection between lines and polygons gives the length of the lines in function of nature of the polygons (jardin, route, etc.)


WITH mg_interection AS

  (SELECT ga.gid, ga.fid, cs.genre_txt || ': ' || Round(SUM(ST_Length(ST_Intersection(ga.the_geom,cs.the_geom)))::numeric,1) || 'm' AS longueur_par_cs

  FROM mg_tmp.mf_ga_strang ga

  LEFT JOIN vd_cad.vd_cad_tpr_cstoutes_s cs ON ST_Intersects(ga.the_geom,cs.the_geom)

  WHERE ga.fid = 385

  GROUP BY ga.gid, ga.fid, cs.genre_txt

  ORDER BY ga.gid, ga.fid, cs.genre_txt)

SELECT gid, fid, array_to_string(array_agg(longueur_par_cs),'; ') FROM mg_interection GROUP BY gid, fid ORDER BY gid ASC;


1524;385;"jardin: 10.1m; route, chemin: 28.4m; trottoir: 12.6m; îlot: 6.4m"


This lengths and natures are all in one column (value = "jardin: 10.1m; route, chemin: 28.4m; trottoir: 12.6m; îlot: 6.4m").

To do some statistics works, I’d like to have one column for each nature of the polygons (jardin, route, etc.) and the length in the row even if the value is null.


Somebody knows a solution (perhaps with crosstabN() function)?

postgis-users mailing list
postgis-users <at> lists.osgeo.org
Andreas Neumann | 20 Aug 16:30 2015

Which operators/functions work with circular arcs?


I wonder how I can find out what operators and what functions work with 
circular arcs? Are they marked in the documentation as such?

Thank you for your hints!

zach cruise | 19 Aug 18:19 2015

split one geojson into tiles

sorry, cross-posting from mapserver...

what do the experts think of
https://github.com/glob3mobile/mmt-vector-tiles? i can't use it
because of java, but i need postgis to slice one large geojson file
into multiple static geojson files/tiles so i can upload them to the
disk and serve from the webserver.

else i'd have to convert postgis to spatialite, like so-
Elmehdi OUADOUD | 19 Aug 09:50 2015

Re: How can I upload .shp files?


You have 3 solutions:

1-Use QGIS specialy PostGIS Manager and import your shp files to database;
2-Use PostGIS plugin PostGIS shapefile  Import/Export Manager;
3-Use shp2pgsql command

Le mercredi 5 août 2015 04:50:58 UTC+2, Joao Bosco Jares a écrit :
Hi All,

I know that can sounds weird this thread. but if we are talking about a popular data base, we need to meet a way to upload .shp file to post gis using an API, batch files seems a terrible workaround. I tried Rjava but nothing, I tried GISserver, but his .war is not working. So, I don't know what I can do anymore. Any advice are very welcome.

Ps.: I'm using Java/Spring, if anyone knows a efficient solution to deal with this architecture are very welcome too.

Thanks in advance.

postgis-users mailing list
postgis-users <at> lists.osgeo.org
Paragon Corporation | 17 Aug 22:14 2015

FW: [pgrouting-users] pgrouting Release candidate

We'll have windows RC1 binaries as well by end of week.





From: pgrouting-users-bounces <at> lists.osgeo.org [mailto:pgrouting-users-bounces <at> lists.osgeo.org] On Behalf Of Vicky Vergara
Sent: Monday, August 17, 2015 3:14 PM
To: pgrouting-dev <at> lists.osgeo.org; pgrouting-users <at> lists.osgeo.org
Subject: [pgrouting-users] pgrouting Release candidate


The pgRouting team would like to announce:

      pgRouting 2.1.0 RC1 Release
is ready for review and testing.





Closed Issues

We are very excited about this release and all the new features that are
being made available.
We hope the community will join in and support all the effort to get the
release this far with additional testing and feedback.

Best regards,
   The pgRouting Team

Pgrouting-users mailing list
Pgrouting-users <at> lists.osgeo.org
postgis-users mailing list
postgis-users <at> lists.osgeo.org
Jonathan Moules | 5 Aug 13:59 2015

PointCloud to Delaunay Triangles

Hi List,

I have a pointcloud, and I want to turn it into a TIN (so I guess ST_DelaunayTriangles), and then measure the length of the sides of the triangles.


Does anyone have any suggestions on how to do this, ideally in a performant fashion?



I've tried a few things, but I'm currently struggling to just turn the points into the TIN. The following fails with the error "set-valued function called in context that cannot accept a set" – alas googling doesn't find anything that explains clearly what the error means. I'm guessing it doesn't want to use an aggregation function (st_union and st_accum also fail), but it's not clear why.





            TABLENAME where cloud_id = 994


Any thoughts welcome.




HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

postgis-users mailing list
postgis-users <at> lists.osgeo.org
franco base | 4 Aug 09:21 2015

Postgis doesn't work after search_path

I'm working on

"PostgreSQL 9.1.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.6.2, 64-bit"


"POSTGIS="2.1.0 r11822" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER"

Postgis is in Public Schema

I run 
ALTER DATABASE mydb SET search_path TO 'test'

After Postgis doesn't work

So I give this command 
reset search_path

and then
alter database mydb set search_path = "$user", public, topology

This select:
SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase

show that mydb is now ok and the setconfig is the same for all the db: 
"{"search_path=\"$user\", public, topology"}"

On the other db Postgis is ok but on 'mydb' postgis doesn't work again.
This is the error (it's the same for all postgis function):

ERROR:  function st_union(public.geometry) does not exist
LINE 33: select st_union(wkb_geometry) AS wkb_geometry

Have you any tips?



postgis-users mailing list
postgis-users <at> lists.osgeo.org