ecourier.co.uk GPS data mine

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

ecourier.co.uk GPS data mine

Saul Albert
Dear OSM,

Well I just had a *very* intereasting meeting with Jay, the cto of
ecourier.co.uk - they're running a very slick-looking courier-logistics
business, delivering real-time-trackable packages to businesses in the
city. Their offices are like some kind of 1996 web-company throwback,
growing like mad, new open plan workspaces springing up like mushrooms as
they take on new swathes of staff....

Anyway, they're totally into the free mapping thing and their GPRS / GPS
toting army of 60 couriers are clocking up about 1.5 million gps points /
day, which they're collecting in a mssql database, then just dumping
them all after a week so their M$ database doesn't fall over.

If we can suggest an easy way for Jay to give us a weekly dump before he
flushes the database, and hopefully automate that, this could be a very
very useful source of data..

Would anyone from the dev team be up for talking to him about how to do
this? It's going to be a bit of a 'mare, because he's already said
there's no way he could put a script on the server.. they're too precious
about that, but I don't see why we couldn't put a *nix box in their
offices that fires off a query to the server once a week pre-dump,
applies any necessary formatting / futzing to the results, and blats them
striaght up to OSM.

I *could* attempt to do something like that myself, but it would probably
take me 1000* longer than anyone with a clue and is less likely to
work...

help!

X

S.
--
-- http://chinabone.lth.bclub.org.uk/~saul/

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Tom Carden
Saul,

That sounds fantastic, and I'm sure there's a way to sort it out.
Thinking... are you sure that someone who won't allow scripts to run on
their server would allow a 3rd party box onto their network (and the web)
and allow it access to the database!?!

One thing that springs to mind is that raw gps points aren't all that - it
would be utterly, jaw-droppingly, amazingly fantastic if they could give us
street names as well.  Presumably they record the destination of the
packages, and the time of delivery?

If anyone has contacts with a taxi firm that records similar information,
this is exactly the kind of information we need - raw points AND metadata.

Still thinking,

Tom.


----- Original Message -----
From: "Saul Albert" <[hidden email]>
To: "openstreetmap" <[hidden email]>
Sent: Tuesday, June 28, 2005 2:41 PM
Subject: [Openstreetmap] ecourier.co.uk GPS data mine


> Dear OSM,
>
> Well I just had a *very* intereasting meeting with Jay, the cto of
> ecourier.co.uk - they're running a very slick-looking courier-logistics
> business, delivering real-time-trackable packages to businesses in the
> city. Their offices are like some kind of 1996 web-company throwback,
> growing like mad, new open plan workspaces springing up like mushrooms as
> they take on new swathes of staff....
>
> Anyway, they're totally into the free mapping thing and their GPRS / GPS
> toting army of 60 couriers are clocking up about 1.5 million gps points /
> day, which they're collecting in a mssql database, then just dumping
> them all after a week so their M$ database doesn't fall over.
>
> If we can suggest an easy way for Jay to give us a weekly dump before he
> flushes the database, and hopefully automate that, this could be a very
> very useful source of data..
>
> Would anyone from the dev team be up for talking to him about how to do
> this? It's going to be a bit of a 'mare, because he's already said
> there's no way he could put a script on the server.. they're too precious
> about that, but I don't see why we couldn't put a *nix box in their
> offices that fires off a query to the server once a week pre-dump,
> applies any necessary formatting / futzing to the results, and blats them
> striaght up to OSM.
>
> I *could* attempt to do something like that myself, but it would probably
> take me 1000* longer than anyone with a clue and is less likely to
> work...
>
> help!
>
> X
>
> S.
> --
> -- http://chinabone.lth.bclub.org.uk/~saul/
>
> _______________________________________________
> Openstreetmap mailing list
> [hidden email]
> http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap 


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Richard Fairhurst
Quoting Tom Carden <[hidden email]>:

> Saul,
>
> That sounds fantastic, and I'm sure there's a way to sort it out.

Apologies for the me too... but that is _astonishingly_ good news. This brings
us so much closer to having the first usable product for a genuinely popular
location.

Richard

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Saul Albert
In reply to this post by Tom Carden
Hi Tom,

On Tue, Jun 28, 2005 at 02:56:43PM +0100, Tom Carden wrote:
> Thinking... are you sure that someone who won't allow scripts to run on
> their server would allow a 3rd party box onto their network (and the web)
> and allow it access to the database!?!

er. yeah. Well this is exactly why I'd like to have a couple of people on
a cc list to talk to Jay with - so we can advise him on the best way to
do this. I'm sure the metadata is available too (destinations at least,
full route information, road by road would be proprietary no?) In any
case, I am clearly not capable of brokering a useful deal with Jay that
gives us all we need. I'll try get him to come along to 'map limehouse'
on the 17th, maybe we can meet then. It'll be a real 'communidee' day, so
should make the point about community interest / social responsibility
etc. etc. although he's a smart cookie and is just into free mapping
because he thinks it's cool.

Tom, would you be up for being on a CC list of maybe 3 of us who I can
say are 'from OSM', who can gently gently tease this data out of
ecourier?

Jo? Steve? Anyone else want to play data diplomats?

X

S.


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Petter Reinholdtsen

[Saul Albert]
> Tom, would you be up for being on a CC list of maybe 3 of us who I
> can say are 'from OSM', who can gently gently tease this data out of
> ecourier?

You didn't say, but I suspect this company is in London.  I'm not, so
I suspect it is best to leave the liaison job to someone else.  But
the task you describe need three steps:

 1 Extract GPS data from the mysql database and store it as GPX files
 2 Compress the GPX files
 3 Upload the GPX files to OSM, using a predefined username/password.

For the first step, I got a perl script doing that job.  An early
version of it was posted to this list,
<URL:http://bat.vr.ucl.ac.uk/pipermail/openstreetmap/2004-October/000029.html>.
The second step is a simple gzip -9, and the third step need a new
script.

Last, we need a wrapper script calling these commands in sequence, and
a cron system to start it at a regular interval.

Are we sure the OSM database is able to handle that amount of data?
It seem to have bigger and bigger problems keeping up.


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: Re: ecourier.co.uk GPS data mine

frank mohr
Petter Reinholdtsen wrote:
> ... But
> the task you describe need three steps:
>
>  1 Extract GPS data from the mysql database and store it as GPX files

As I read Toms first mail it's not a mysql but a M$-SQL server

So I think here are only 2 chances to get the data into
the "free world" ;-)

- connect a Unix/Linux box to the network and query
  the server (by ODBC?)
- Export the data on the Win-box and import them
  (One idea might be export as a Access db and
  use mdbtools to read it)

Frank

       

       
               
___________________________________________________________
Gesendet von Yahoo! Mail - Jetzt mit 1GB Speicher kostenlos - Hier anmelden: http://mail.yahoo.de


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

stevec-4
In reply to this post by Saul Albert
* @ 28/06/05 02:41:22 PM [hidden email] wrote:
> Would anyone from the dev team be up for talking to him about how to do
> this? It's going to be a bit of a 'mare, because he's already said

Saul: Cool!

Absolutely, contact details? Ben is away soon, Tom and I have spare
boxes if they want it that way.

* @ 28/06/05 02:56:43 PM [hidden email] wrote:
> If anyone has contacts with a taxi firm that records similar information,
> this is exactly the kind of information we need - raw points AND metadata.

I've written to three taxi firms without response. Every time I get in
to a cab I talk about OSM and try and get them to take a GPS around but
I don't usually get far explaining it from a F/OSS point of view, or the
mapping POV. So now I talk of OSM as an 'art project' and I get full
understanding.

I spoke to one chap who's a black cabbie and is willing to take around
my GPS for 20 quid/day. Its a bit steep but compared with hiring a car
its good. If this ecourier thing takes off then its redundant.

* @ 29/06/05 08:25:45 AM [hidden email] wrote:
> Are we sure the OSM database is able to handle that amount of data?
> It seem to have bigger and bigger problems keeping up.

The stability problems have nothing to do with the amount of data, but
apache2 and tomcat getting upset. I think the fixes in place (as of a
few days ago) should make it ok. I want to write everything in ruby
anyway.



have fun,

SteveC [hidden email] http://www.fractalus.com/steve/

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Alex Willmer
In reply to this post by Saul Albert
On Tue, 2005-06-28 at 18:41 +0100, Saul Albert wrote:
> Tom, would you be up for being on a CC list of maybe 3 of us who I can
> say are 'from OSM', who can gently gently tease this data out of
> ecourier?
>
> Jo? Steve? Anyone else want to play data diplomats?
>

If you're still in need of anybody, I'd like to offer my services.

I have good Windows & database knowledge, in my day job I'm a DBA.

Contact me offlist if I can be of help.

Regards

Alex Willmer


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Lars Aronsson
In reply to this post by Saul Albert
Saul Albert wrote:

> If we can suggest an easy way for Jay to give us a weekly dump

What if the extracted track logs can be used to prove that one of
their employees exceeded a speed limit or was at the place of a
crime?  On what conditions will the company be willing to share
the logs?  This is more intriguing than the technical aspects.


--
  Lars Aronsson ([hidden email])
  Aronsson Datateknik - http://aronsson.se

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Richard Fairhurst
Quoting Lars Aronsson <[hidden email]>:

> What if the extracted track logs can be used to prove that one of
> their employees exceeded a speed limit or was at the place of a
> crime?  On what conditions will the company be willing to share
> the logs?  This is more intriguing than the technical aspects.

Some of the Geowiki.com co-conspirators had this same concern about sharing our
data (re: the speed limit, not the crime, I hope). What we're going to do is
simply strip out the timestamps.

Richard

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Petter Reinholdtsen

[Richard]
> What we're going to do is simply strip out the timestamps.

Is that valid with GPX?  I believe gpx2shp is sick when no timestamp
is present.


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: Re: ecourier.co.uk GPS data mine

Tom Carden
"Petter Reinholdtsen" <[hidden email]> wrote:
>
> [Richard]
>> What we're going to do is simply strip out the timestamps.
>
> Is that valid with GPX?  I believe gpx2shp is sick when no timestamp
> is present.
>

It would also render many of our ideas for automatic feature extraction
pretty much impossible.

I remember hearing about a driving enthusiast who was convicted of speeding
using his own video tape as evidence - is GPS data admissible in the same
way?  Hopefully it's accurate enough to convict someone if it's accurate
enough for mapping!

Perhaps it would be easier to keep the owner of the GPS trail anonymous?  If
we don't know, then we can't tell.

Tom.


_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: Re: ecourier.co.uk GPS data mine

Lars Luthman
In reply to this post by Petter Reinholdtsen
On Wed, 2005-06-29 at 15:41 +0200, Petter Reinholdtsen wrote:
> [Richard]
> > What we're going to do is simply strip out the timestamps.
>
> Is that valid with GPX?  I believe gpx2shp is sick when no timestamp
> is present.

It is valid GPX, the timestamp is optional for way/route/trackpoints:

http://www.topografix.com/GPX/1/1/#type_wptType

--
Lars Luthman
PGP key:     http://www.d.kth.se/~d00-llu/pgp_key.php
Fingerprint: FCA7 C790 19B9 322D EB7A  E1B3 4371 4650 04C7 7E2E

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap

signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Saul Albert
In reply to this post by Alex Willmer
hi Alex,

great- just the skillset we're likely to need :).

Based on current response and my reading of the project-flow, I'll send
an email to Jay outlining the processes mentioned by Petter and Frank,
and CCing you, Steve, Tom, Jo and anyone else who wants to be 'in' on
this process. Mail me now if you want to be part of the conversation,
although I reckon 5 is about the right number of people to be
distributedly aware of what's going on and capable of acting on any
implementation decisions.. which we'll garner opinion for on the list, of
course.

I hope this sounds like a useful course of action.

Here we go.

X

S.

On Wed, Jun 29, 2005 at 12:51:10PM +0100, Alex Willmer wrote:

> On Tue, 2005-06-28 at 18:41 +0100, Saul Albert wrote:
> > Tom, would you be up for being on a CC list of maybe 3 of us who I can
> > say are 'from OSM', who can gently gently tease this data out of
> > ecourier?
> >
> > Jo? Steve? Anyone else want to play data diplomats?
> >
>
> If you're still in need of anybody, I'd like to offer my services.
>
> I have good Windows & database knowledge, in my day job I'm a DBA.
>
> Contact me offlist if I can be of help.
>
> Regards
>
> Alex Willmer
>

--
-- http://chinabone.lth.bclub.org.uk/~saul/

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: Re: ecourier.co.uk GPS data mine

Raphael Jacquot-2
In reply to this post by Petter Reinholdtsen
Petter Reinholdtsen wrote:
> [Richard]
>
>>What we're going to do is simply strip out the timestamps.
>
>
> Is that valid with GPX?  I believe gpx2shp is sick when no timestamp
> is present.
just remove a random constant from the timestamps, using the same
constant for a whole track...

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: Re: ecourier.co.uk GPS data mine

Matt Amos-2
In reply to this post by Tom Carden
On Wednesday 29 June 2005 14:49, Tom Carden wrote:
> "Petter Reinholdtsen" <[hidden email]> wrote:
> > Is that valid with GPX?  I believe gpx2shp is sick when no
> > timestamp is present.
>
> It would also render many of our ideas for automatic feature
> extraction pretty much impossible.

GPX requires that the points be correctly ordered, which should be
good enough for feature extraction. there is a good reason to strip
or alter the timestamps anyway, as they could be used to identify
patterns of movement. its always good to maintain a healthy level of
paranoia, especially when dealing with other people's data ;-)

> I remember hearing about a driving enthusiast who was convicted of
> speeding using his own video tape as evidence - is GPS data
> admissible in the same way?  Hopefully it's accurate enough to
> convict someone if it's accurate enough for mapping!

http://www.cnn.com/2004/LAW/02/17/peterson.trial/index.html

seems to agree, but i don't think its been ruled upon in this
country...

> Perhaps it would be easier to keep the owner of the GPS trail
> anonymous?  If we don't know, then we can't tell.

this is probably a better idea. the following seems to put us in the
clear (as we're not the employer or owner of any of the vehicles):

http://www.drivesafely.org/faqs/driving_the vehicle.htm

a guess could probably be made if the GPS unit is taking readings at
predictable intervals...

cya,

matt

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap

attachment0 (197 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ecourier.co.uk GPS data mine

Lars Aronsson
In reply to this post by Richard Fairhurst
[hidden email] wrote:

> Some of the Geowiki.com co-conspirators had this same concern
> about sharing our data (re: the speed limit, not the crime, I
> hope). What we're going to do is simply strip out the
> timestamps.

But then you also strip out any speed information, that could be
useful in telling bike paths apart from motorways, don't you?


--
  Lars Aronsson ([hidden email])
  Aronsson Datateknik - http://aronsson.se

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap
Reply | Threaded
Open this post in threaded view
|

Re: Re: ecourier.co.uk GPS data mine

lsces
In reply to this post by Tom Carden
Tom Carden wrote:

> I remember hearing about a driving enthusiast who was convicted of
> speeding using his own video tape as evidence - is GPS data admissible
> in the same way?  Hopefully it's accurate enough to convict someone if
> it's accurate enough for mapping!

I got stung by a speed camera near Coventry. When I checked the track I
HAD slowed down so could not see their allegation. I got the coordinates
of the camera - which they got wrong :) but it was enough to see that
the camera was in a different place and that I had speeded up then
slowed down just enough to be stung :(
So YES it is accurate enough - but the fact that they can't give you
VALID grid locations is no grounds to appeal ;)
Given that we CAN run tracks, an accurate grid location SHOULD be part
of the ticket, rather than some vaguely worded text!

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services

_______________________________________________
Openstreetmap mailing list
[hidden email]
http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap