Hello everyone,
I have a question about pgr_createTopology…
I am attempting to make a routable US street network table using pgRouting (pgRouting ver 2.0.0, postGIS 2.1.1, postgresql 9.2).
It is running on localhost (32 bit windows, 4 gb RAM).
I have a postgres table created originally from street shapefiles that now has about 43 million rows.
As I expected, the create topology function faced an out of memory error about halfway through the creation, and gave me this message:
My original statement:
SELECT pgr_createTopology(‘usa_streets’, 0.00001, ‘geom’, ‘gid’);
Result:
NOTICE: out of memory
NOTICE: UPDATE public.usa_streets SET source = 22148149,target = 22152619 WHERE gid = 9166028
Total query runtime: 96149189 ms.
1 row retrieved.
The resulting vertices_pgr table has only about 22 million rows and I get errors for null sources when trying to run routing functions.
Would it be possible to run another Create Topology for rows in the original edges table where source and target are null, or will this just create two conflicting networks?
I am curious if anyone here is familiar or experienced with a workaround for this problem? Or is the only solution more RAM? If I’m leaving out any relevant information please let me know.
Thank you for reading,
John Zabrenski
Hello John,
Vicky has developed a new version of create topology that supports incremental build of the topology.
https://github.com/cvvergara/pgrouting/tree/commonFunctions
This will get merged into pgrouting at some point, but it would be great if you wanted to give it a try and report if you have problems. We have done some basic testing but it sounds like you have a great test case for this.
You can clone her repository and build pgrouting from that source and then you should have access to those new functions. Vicky is on the list so if you need help or run into issues, give a shout and let us know how it goes.
Since you are on windows, it might be easier to just take the plpgsql code from her source and install it without trying to build everything.
-Steve
On 9/30/2014 5:20 PM, John Zabrenski wrote:
Hello everyone,
I have a question about pgr_createTopology...
I am attempting to make a routable US street network table using
pgRouting (pgRouting ver 2.0.0, postGIS 2.1.1, postgresql 9.2).
It is running on localhost (32 bit windows, 4 gb RAM).
I have a postgres table created originally from street shapefiles that
now has about 43 million rows.
As I expected, the create topology function faced an out of memory error
about halfway through the creation, and gave me this message:
My original statement:
SELECT pgr_createTopology('usa_streets', 0.00001, 'geom', 'gid');
Result:
NOTICE: out of memory
NOTICE: UPDATE public.usa_streets SET source = 22148149,target =
22152619 WHERE gid = 9166028
Total query runtime: 96149189 ms.
1 row retrieved.
The resulting vertices_pgr table has only about 22 million rows and I
get errors for null sources when trying to run routing functions.
Would it be possible to run another Create Topology for rows in the
original edges table where source and target are null, or will this just
create two conflicting networks?
I am curious if anyone here is familiar or experienced with a workaround
for this problem? Or is the only solution more RAM? If I'm leaving out
any relevant information please let me know.
Thank you for reading,
John Zabrenski
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
Hi Steve,
I tested this with a two part build of the state of Rhode Island and it succeeded. Did a test of pgr_drivingdistance and got results that look normal (http://goo.gl/lYY7Hc).
So in the near future I will be attempting the build of the US street network and will let you know of any developments.
Thanks for your assistance,
John
···
On Tue, Sep 30, 2014 at 11:05 PM, Stephen Woodbridge <woodbri@swoodbridge.com> wrote:
Hello John,
Vicky has developed a new version of create topology that supports incremental build of the topology.
https://github.com/cvvergara/pgrouting/tree/commonFunctions
This will get merged into pgrouting at some point, but it would be great if you wanted to give it a try and report if you have problems. We have done some basic testing but it sounds like you have a great test case for this.
You can clone her repository and build pgrouting from that source and then you should have access to those new functions. Vicky is on the list so if you need help or run into issues, give a shout and let us know how it goes.
Since you are on windows, it might be easier to just take the plpgsql code from her source and install it without trying to build everything.
-Steve
On 9/30/2014 5:20 PM, John Zabrenski wrote:
Hello everyone,
I have a question about pgr_createTopology…
I am attempting to make a routable US street network table using
pgRouting (pgRouting ver 2.0.0, postGIS 2.1.1, postgresql 9.2).
It is running on localhost (32 bit windows, 4 gb RAM).
I have a postgres table created originally from street shapefiles that
now has about 43 million rows.
As I expected, the create topology function faced an out of memory error
about halfway through the creation, and gave me this message:
My original statement:
SELECT pgr_createTopology(‘usa_streets’, 0.00001, ‘geom’, ‘gid’);
Result:
NOTICE: out of memory
NOTICE: UPDATE public.usa_streets SET source = 22148149,target =
22152619 WHERE gid = 9166028
Total query runtime: 96149189 ms.
1 row retrieved.
The resulting vertices_pgr table has only about 22 million rows and I
get errors for null sources when trying to run routing functions.
Would it be possible to run another Create Topology for rows in the
original edges table where source and target are null, or will this just
create two conflicting networks?
I am curious if anyone here is familiar or experienced with a workaround
for this problem? Or is the only solution more RAM? If I’m leaving out
any relevant information please let me know.
Thank you for reading,
John Zabrenski
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users