[postgis-users] Re: pgrouting and postgis
Daniel W
gentoo.murray at gmail.com
Wed Jun 11 04:17:15 PDT 2008
Hi Daniel,
I tried it and now it works for me, too.
I wasn't sure, if I need a geometry column, because it was only
described for the Dijkstra algorithm and i thought, it would work for
the A-Star Algorithm without the geometry. So I leapt this part and
was a little confused by the error code, which isn't very helpful...
So for routing, I only need the length of a geometry and the geometry
self ( for the A-Star algorithm also the coordinates for start and end
node) and must do the steps you described?
Ok, thx for you help. :-)
I will modify my program, so that it write the complete Geometry of
every edge in the table.
Best regards,
Daniel
On Wed, Jun 11, 2008 at 12:24 PM, Daniel Kastl <orkney at gmx.de> wrote:
> -----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-----
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list