[postgis-users] Google Directions -> PostGIS

James David Smith james.david.smith at gmail.com
Tue Jun 11 08:26:16 PDT 2013


Hi Mark,

I've finally got around to messing about this with. I can't thank you
enough for the code you've provided. I'm getting what seems like a
fairly simple problem however and wonder if you can help? When I get
to actually implementing the code using the query:

SELECT create_route_geometry('-33.868848 151.203089', '-33.88595
151.21836', 'transit');

I get an error saying this:

ERROR:  R interpreter expression evaluation error
DETAIL:  Error in library("bitops") : there is no package called 'bitops'
CONTEXT:  In PL/R function r_georouter
SQL statement "SELECT r_georouter('-33.868848 151.203089', '-33.88595
151.21836', 'transit' )"
PL/pgSQL function create_route_geometry(text,text,text) line 9 at
EXECUTE statement

I wonder if you've got any suggestions? I have opened R and installed
the bitops package so I'm a bit confused. It could be something to do
with my PL/R installation so I'll have to look at that too...

Thanks

James




On 17 May 2013 03:01, Mark Wynter <mark at dimensionaledge.com> wrote:
> The following is an integrated PostGIS/R recipe I pulled together some
> months back.  The function does what you describe by returning a route
> geometry from the google maps routing engine, based on a start location,
> finish location and mode of travel.  I've also provided an example of how to
> use it within a pl/pgsql stored procedure, plus a screenshot from QGIS /
> Google Maps showing the different routes depending on the mode of travel.
>
> As Travis points out, the issue implementing this function is not a
> technical one but a legal one.  The same issue applies to using Google Maps
> API for geocoding.  BIng Maps also have a routing engine, and their terms of
> use appear more liberal with regard to storing of content within a database.
>
> Mark
>
> -----
>
>
> CREATE OR REPLACE FUNCTION r_georouter(startstr text, finishstr text,
> travelmode text) RETURNS SETOF text AS
> $$
>
> library("bitops")
> library("RJSONIO")
> decodeLine <- function(encoded){
>  require(bitops)
>
>  vlen <- nchar(encoded)
>  vindex <- 0
>  varray <- NULL
>  vlat <- 0
>  vlng <- 0
>
>  while(vindex < vlen){
>    vb <- NULL
>    vshift <- 0
>    vresult <- 0
>    repeat{
>      if(vindex + 1 <= vlen){
>        vindex <- vindex + 1
>        vb <- as.integer(charToRaw(substr(encoded, vindex, vindex))) - 63
>      }
>
>      vresult <- bitOr(vresult, bitShiftL(bitAnd(vb, 31), vshift))
>      vshift <- vshift + 5
>      if(vb < 32) break
>    }
>
>    dlat <- ifelse(
>      bitAnd(vresult, 1)
>      , -(bitShiftR(vresult, 1)+1)
>      , bitShiftR(vresult, 1)
>    )
>    vlat <- vlat + dlat
>
>    vshift <- 0
>    vresult <- 0
>    repeat{
>      if(vindex + 1 <= vlen) {
>        vindex <- vindex+1
>        vb <- as.integer(charToRaw(substr(encoded, vindex, vindex))) - 63
>      }
>
>      vresult <- bitOr(vresult, bitShiftL(bitAnd(vb, 31), vshift))
>      vshift <- vshift + 5
>      if(vb < 32) break
>    }
>
>    dlng <- ifelse(
>      bitAnd(vresult, 1)
>      , -(bitShiftR(vresult, 1)+1)
>      , bitShiftR(vresult, 1)
>    )
>    vlng <- vlng + dlng
>
>    varray <- rbind(varray, c(vlng * 1e-5, vlat * 1e-5))
>  }
>  coords <- data.frame(varray)
>  names(coords) <- c("lon", "lat")
>  return(coords)
> }
>
> # set the origin, destination and travel mode
> # if travel mode is "transit" you need to specify a departure (or arrival)
> time
> origin <- startstr
> destination <- finishstr
> travelMode <- travelmode
> departureTime <- Sys.time() #I want to leave now!
>
> # build the URL
> baseUrl <- "http://maps.googleapis.com/maps/api/directions/json?"
> origin <- gsub(" ", "+", origin)
> destination <- gsub(" ", "+", destination)
> finalUrl <- paste(baseUrl
>                  , "origin=", origin
>                  , "&destination=", destination
>                  , "&sensor=false"
>                  , "&mode=", travelMode
>                  , "&departure_time=", as.integer(departureTime)
>                  , sep = "")
>
> # get the JSON returned by Google and convert it to an R list
> url_string <- URLencode(finalUrl)
> trip <- fromJSON(paste(readLines(url_string), collapse = ""), simplify =
> FALSE)
>
> # get the encoded coordinates for the full trip
> tripPathEncoded <- trip$routes[[1]]$overview_polyline$points
> tripPathEncoded
>
> # decode the encoded coordinates
> tripPathCoords <- decodeLine(tripPathEncoded)
>
> #print(tripPathCoords)
> geom = "ST_GeomFromText('LINESTRING("
> for(j in 1:(length(tripPathCoords$lon)-1)){
> geom = sprintf("%s %.5f %.5f,", geom,
> tripPathCoords$lon[[j]],tripPathCoords$lat[[j]])
> }
> # Close the linestring
> geom = sprintf("%s %.5f %.5f)', 4326)", geom,
> tripPathCoords$lon[[j+1]],tripPathCoords$lat[[j+1]])
>
> return(data.frame(geom))
>
> $$ LANGUAGE 'plr';
>
>
> DROP TABLE IF EXISTS route_geom;
> CREATE TABLE route_geom (
> route_id serial,
> start character varying,
> finish character varying,
> travelmode character varying,
> wkb_geometry geometry(LINESTRING, 4326)
> )
> WITH (
> OIDS=FALSE
> );
>
>
> CREATE OR REPLACE FUNCTION create_route_geometry(startstr text, finishstr
> text, travelmode text) RETURNS text AS $$
> DECLARE
> wkt_linestring text;
> result geometry;
> sequencevar text := 'route_geom_route_id_seq';
>
> BEGIN
> --Call the plr function and parse the text returned by the function to a
> declared variable
> EXECUTE 'SELECT r_georouter('|| quote_literal(startstr) ||', '||
> quote_literal(finishstr) ||', '|| quote_literal(travelmode) ||' )' INTO
> wkt_linestring;
>
> --Generate the wkb_geometry from the wkt_linestring, and assign to the
> result variable of type geometry
> EXECUTE 'SELECT '|| wkt_linestring INTO result;
>
> --Insert the result as well as the start and finish locations and travel
> mode used to generate the geometry into the database table
> EXECUTE 'INSERT INTO route_geom VALUES(NEXTVAL('||
> quote_literal(sequencevar) ||'), '|| quote_literal(startstr) ||', '||
> quote_literal(finishstr) ||', '|| quote_literal(travelmode) ||', ' ||
> quote_literal(CAST(result As text)) ||')';
>
> --Alternatively, you could update the value of the wkb_geometry column of
> the table as such
> --EXECUTE 'UPDATE route_geom SET wkb_geometry = '''|| CAST(result As text)
> ||'''';
>
> RETURN 'done';
> END;
> $$ LANGUAGE plpgsql STRICT;
>
> -- Note that the google maps API notation is lat/lon, whereas for PostGIS
> its lon/lat.
> --Alternatively r_geocoder will accept the start and finish locations as a
> street address.
>
> SELECT create_route_geometry('-33.868848 151.203089', '-33.88595 151.21836',
> 'transit');
> SELECT create_route_geometry('-33.868848 151.203089', '-33.88595 151.21836',
> 'driving');
>
>
> Credits:  R function for decoding polylines
> http://s4rdd.blogspot.com.au/2012/12/google-maps-api-decoding-polylines-for.html
>
>
> [see attached file: r_georouter_routepaths_example.png]
>
> Message: 7
> Date: Thu, 16 May 2013 16:01:46 +0100
> From: James David Smith <james.david.smith at gmail.com>
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Subject: [postgis-users] Google Directions -> PostGIS
> Message-ID:
> <CAMu32ADfoNdYwPz0B-w4s89kL8yPSTAGjx+ik7dym29CrH4vjQ at mail.gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
>
> Hi there,
>
> This isn't so much a question as such, more a request for opinions and
> advice. I was wondering whether anyone had any experience with using the
> Google API for getting driving directions/routing - but then storing the
> results in a PostgreSQL/PostGIS database. I was thinking that my workflow
> might go something like this:
>
> 1) Take table of trip_ids, start points, end points, travel modes and start
> times from the database
> 2) Export them as a CSV
> 3) Import the CSV to R
> 4) Use R to loop through the data and send queries off to the Google API.
> 5) The results are a series of polylines each with the geometry as a
> polyline, the length of the stage, the time of the stage etc. As a JSON
> file. If I could break this apart in it's constiuent parts, could I then
> store it back in my PostGIS database?
>
> I guess the hardest bit I can see about this process, is converting the
> google encoded polyline to a PostGIS polyline I think. Any thoughts on that?
>
> Once this is all done I then want to use the routes with another layer to
> calculate someones exposure to X over each journey.
>
> Any general thoughts about this process would be welcome.
>
> Thanks
>
> James
>
>
> Message: 10
> Date: Thu, 16 May 2013 12:05:31 -0400
> From: Travis Kirstine <traviskirstine at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Google Directions -> PostGIS
> Message-ID:
> <CALtm4h0VE61pQ6uT4wxCPwh=DzMu-cRG9gsQVCRPBCU+JHv0Jg at mail.gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
>
> I don't think this would be permitted based on Google's terms of service,
> for example:
>
> Section 10.1.3 *Restrictions against Data Export or Copying.*
>
> (b) *No Pre-Fetching, Caching, or Storage of Content.* You must not
>
> pre-fetch, cache, or store any Content, except that you may store: (i)
> limited amounts of Content for the purpose of improving the performance of
> your Maps API Implementation if you do so temporarily (and in no event for
> more than 30 calendar days), securely, and in a manner that does not permit
> use of the Content outside of the Service; and (ii) any content identifier
> or key that the Maps APIs Documentation specifically permits you to store.
> For example, you must not use the Content to create an independent database
> of "places" or other local listings information.
>
>
>
>


More information about the postgis-users mailing list