[postgis-users] Re: pgrouting and postgis
Daniel Kastl
orkney at gmx.de
Wed Jun 11 03:24:46 PDT 2008
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi Daniel,
The data you sent me also failed with my pgRouting installation.
So I tried to rebuild the topology with "assign_vertex_id" and new
source/target column.
Your data didn't have a geometry column, so I took x1/y1 and x2/y2 to
create one.
With this topology routing works, so it must be a data issue.
subway=# SELECT
AddGeometryColumn('subways','the_geom',4326,'MULTILINESTRING',2);
subway=# UPDATE subways SET
the_geom=GeometryFromText('MULTILINESTRING(('||x1||' '||y1||','||x2||'
'||y2||'))',4326);
subway=# ALTER TABLE subways ADD COLUMN source integer;
subway=# ALTER TABLE subways ADD COLUMN target integer;
subway=# CREATE INDEX source_idx ON subways(source);
subway=# CREATE INDEX target_idx ON subways(target);
subway=# CREATE INDEX geom_idx ON subways USING GIST(the_geom
GIST_GEOMETRY_OPS);
subway=# SELECT assign_vertex_id('subways', 0.0001, 'the_geom', 'id');
Then a simple Dijkstra request:
subway=# SELECT * FROM shortest_path('SELECT id, source::integer,
target::integer, cost::double precision FROM subways', 10, 50, false,
false);
vertex_id | edge_id | cost
- -----------+---------+--------------------
10 | 7 | 0.0553116096630727
9 | 6 | 0.0208274380181027
8 | 4964 | 0.0268226987132527
7 | 48 | 0.0112832211728027
48 | 1380 | 0.0874840856130771
1127 | 2463 | 0.0372287156591971
1776 | 2462 | 0.0350026827612493
1775 | 2461 | 0.0335936721915771
1774 | 2459 | 0.0181156223261193
1773 | 2457 | 0.142098008162459
49 | 51 | 0.116607141663929
50 | -1 | 0
(12 Zeilen)
Hope this helps you,
Daniel
Daniel W schrieb:
> Hi Daniel,
>
> thx for the answer.
>
> I dont think, that this is a distribution specific problem. I also
> tried it on a 32 Bit Fedora machine with the configuration: and got
> the same error:
>
> postgresql.i386 8.2.7-1.fc7
> postgis.i386 1.3.3-1.fc7
> pgrouting-1.02
>
> Maybe you are right and something with the data is wrong. I attached a
> 4,3 MB SQL dump of my database. I hope its not too big for the
> mailinglist. And i will also try the official pgrouting forum.
>
> Daniel
>
>
> On Wed, Jun 11, 2008 at 2:14 AM, Daniel Kastl <orkney at gmx.de> wrote:
>> Hi Daniel,
>>
>> Maybe http://pgrouting.postlbs.org/discussion is the better place for
>> this question.
>> That kind of error happened in a previous version of pgRouting, and I
>> thought it has been solved.
>>
>> I never tried pgRouting on Gentoo Linux, so it could be a distribution
>> issue. If something is wrong with the data, it would help you could try
>> it with another Linux distribution.
>> In that case you could file a bug report (tickets) on the pgRouting site
>> (you need to register an account for that) and we will try to solve the
>> problem?
>>
>> Daniel
>>
>> PS: if the data is not too large, could you send me some of your data
>> for testing?
>>
>>
>>
>> Daniel W schrieb:
>>> Hello.
>>>
>>> Maybe it will help. I found these logs, if I do the query:
>>>
>>>
>>> terminate called after throwing an instance of 'std::bad_alloc'
>>> what(): St9bad_alloc
>>> LOG: server process (PID 13657) was terminated by signal 6
>>> LOG: terminating any other active server processes
>>> WARNING: terminating connection because of crash of another server
process
>>> DETAIL: The postmaster has commanded this server process to roll back
>>> the current transaction and exit, because another server process
>>> exited abnormally and possibly corrupted shared memo
>>> ry.
>>> HINT: In a moment you should be able to reconnect to the database and
>>> repeat your command.
>>> LOG: all server processes terminated; reinitializing
>>> LOG: database system was interrupted at 2008-06-10 22:52:32 CEST
>>> LOG: checkpoint record is at 0/37D04360
>>> LOG: redo record is at 0/37D04360; undo record is at 0/0; shutdown TRUE
>>> LOG: next transaction ID: 1883985; next OID: 2550772
>>> LOG: database system was not properly shut down; automatic recovery
in progress
>>> LOG: record with zero length at 0/37D043A8
>>> LOG: redo is not required
>>> LOG: database system is ready
>>>
>>>
>>> greets,
>>> Daniel
>>>
>>>
>>> On Tue, Jun 10, 2008 at 6:26 PM, Daniel W <gentoo.murray at gmail.com>
wrote:
>>>
>>>> Hello,
>>>>
>>>> i am new to Postgis and pgRouting and i am trying to route over some
>>>> data, but it doesnt work for me.
>>>>
>>>> I installed a routing database with following commands as described in
>>>> the pgrouting online tutorial
>>>> (http://pgrouting.postlbs.org/wiki/Workshop-CreateRoutingDatabase).
>>>>
>>>> The data, which i want to use for routing, I got from the
>>>> Openstreetmap projekt. I wrote me a program, which parse the xml file
>>>> and write the data in a database.
>>>> My Table with the name "subways" was created with the following command:
>>>>
>>>> CREATE TABLE subways (ID integer , startNode integer REFERENCES
>>>> nodes(ID), endNode integer REFERENCES nodes(ID), cost double
>>>> precision, name char(40), x1 double precision, y1 double precision, x2
>>>> double precision,y2 double precision, PRIMARY KEY(ID, startNode,
>>>> endNode));
>>>>
>>>> And the table looks like that:
>>>>
>>>> id= way id
>>>> name = name of the street
>>>> startnode, endnode are the IDs of the nodes
>>>> x1,y1 are the coordinates of the startnode
>>>> x2,y2 are the coordinates of the endnodenode
>>>> cost = length of the way
>>>>
>>>> id | startnode | endnode | cost |
>>>> name | x1 | y1 | x2 | y2
>>>>
-------+-----------+-----------+----------------------+------------------------------------------+-----------+------------+-----------+------------
>>>> 0 | 140530 | 140555 | 8.5403091715829 |
>>>> | 8.3889675 | 53.0587826 | 8.28445 |
>>>> 53.1030248
>>>> 2 | 206374638 | 26870008 | 0.320998562351643 |
>>>> | 8.2145964 | 53.1430959 | 8.2168197 |
>>>> 53.1405356
>>>> 3 | 201754820 | 92909800 | 0.172230180241359 | Industriestraße
>>>> | 8.1939216 | 53.1498095 | 8.1965042 |
>>>> 53.1497966
>>>> 5 | 26869963 | 165748872 | 0.0268226987132517 |
>>>> Heiligengeistwall | 8.2087014 | 53.1412273 |
>>>> 8.208876 | 53.1414446
>>>> 6 | 165748872 | 26869964 | 0.0208274380181027 |
>>>> Heiligengeistwall | 8.208876 | 53.1414446 |
>>>> 8.2090004 | 53.1416164
>>>>
>>>>
>>>>
>>>> (In another Table named nodes, i have every Point-ID with coordinates)
>>>> If I try now the routing function A-Star.....
>>>>
>>>>
>>>> SELECT * FROM shortest_path_astar('SELECT id, startnode as source,
>>>> endnode as target, cost, x1, y1, x2, y2 from subways', 31567898,
>>>> 96012788, false, false);
>>>>
>>>>
>>>> ... I get following error code:
>>>>
>>>>
>>>> server closed the connection unexpectedly
>>>> This probably means the server terminated abnormally
>>>> before or while processing the request.
>>>> The connection to the server was lost. Attempting reset: Succeeded.
>>>>
>>>>
>>>>
>>>> I hope you can help me, my system is:
>>>>
>>>> Gentoo Linux 64 Bit
>>>> postgresql 8.0.15
>>>> postgis 1.3.1
>>>> pgrouting 1.02
>>>>
>>>>
>>>> Best regards,
>>>> Daniel
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>> ----------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFIT6fuovwtB8loDwoRAmyYAJ9WZex2KwwtDeeGPsKVHNqr8Ts+2wCfWk/a
nuKa3AYbhOyOU/9iuwDT5BQ=
=nMEn
-----END PGP SIGNATURE-----
More information about the postgis-users
mailing list