[pgrouting-users] Pgrouting-users Digest, Vol 81, Issue 2
Omar Fernando Pessôa
omar.pessoa at gmail.com
Wed Jun 24 04:30:39 PDT 2015
You can find the nearest point using <#> operator. It's faster.
SELECT source, target, id, ST_LineLocatePoint(the_geom,
ST_GeometryFromText('POINT(%.4f %.4f)',4326)) as percentual FROM
vertex_ruas ORDER BY the_geom <#> ST_GeometryFromText('POINT(%.4f
%.4f)',4326) LIMIT 1;
..
*Omar Fernando Pessôa*
http://www.opessoa.com
Desenvolvedor de sistemas
Programador C# e C++
2015-06-23 18:04 GMT-03:00 <pgrouting-users-request at lists.osgeo.org>:
> Send Pgrouting-users mailing list submissions to
> pgrouting-users at lists.osgeo.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
> or, via email, send a message with subject or body 'help' to
> pgrouting-users-request at lists.osgeo.org
>
> You can reach the person managing the list at
> pgrouting-users-owner at lists.osgeo.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of Pgrouting-users digest..."
>
>
> Today's Topics:
>
> 1. Reg: Get nearest node in osm data (Manikanta Kondeti)
> 2. Re: Reg: Get nearest node in osm data (Stephen Woodbridge)
> 3. Re: Reg: Get nearest node in osm data (Manikanta Kondeti)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 24 Jun 2015 01:26:32 +0530
> From: Manikanta Kondeti <mani.iiit123 at gmail.com>
> To: pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
> Subject: [pgrouting-users] Reg: Get nearest node in osm data
> Message-ID:
> <CAMxCCEbigsduMLqQXQUsd=SzBi76F1ZZy1bcFhd9DG=
> WKvT_wg at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hi,
>
> I am writing a small application to get the route on the map. I am using
> openlayers.js on client and pgrouting as a backend routing library for my
> project. When a click is triggered I get a lat, lon, and I have the osm
> data in postgres which is loaded using osm2pgrouting. I'll pass this
> lat,lon to server and I have to find the nearest node id from this
> lat,lon. Can I query the database and get it done? Help me out.
>
> Thank you,
> Mani
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150624/b561eb58/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 2
> Date: Tue, 23 Jun 2015 16:25:53 -0400
> From: Stephen Woodbridge <woodbri at swoodbridge.com>
> To: pgrouting-users at lists.osgeo.org
> Subject: Re: [pgrouting-users] Reg: Get nearest node in osm data
> Message-ID: <5589C0D1.3080809 at swoodbridge.com>
> Content-Type: text/plain; charset=windows-1252; format=flowed
>
> On 6/23/2015 3:56 PM, Manikanta Kondeti wrote:
> > Hi,
> >
> > I am writing a small application to get the route on the map. I am
> > using openlayers.js on client and pgrouting as a backend routing library
> > for my project. When a click is triggered I get a lat, lon, and I have
> > the osm data in postgres which is loaded using osm2pgrouting. I'll pass
> > this lat,lon to server and I have to find the nearest node id from this
> > lat,lon. Can I query the database and get it done? Help me out.
>
> Probably you want to do this in two steps:
>
> 1. find the nearest edge
> 2. find the nearest node along the edge
>
> Anyway your queries will be something like:
>
> -- get the closest edge
> select * from edges where st_dwithin(geom, st_setsrid(st_makepoint(long,
> lat), 4326), maxdist) order by st_distance(geom,
> st_setsrid(st_makepoint(long, lat), 4326)) asc limit 1;
>
> -- get closest node along edge
> select case when st_line_locate_point(geom,st_setsrid(st_makepoint(long,
> lat), 4326)) < 0.5 then source else target from edges edge_id=id
>
>
>
> -- or search to just get the closest node
> select * from nodes where st_dwithin(geom, st_setsrid(st_makepoint(long,
> lat), 4326), maxdist) order by st_distance(geom,
> st_setsrid(st_makepoint(long, lat), 4326)) asc limit 1;
>
>
> where:
>
> maxdist - maximum distance to search for geometries in db units probably
> degrees
>
> long, lat - values for the location in question
>
> id - is the id of the closest edge found in the first query
>
>
> ------------------------------
>
> Message: 3
> Date: Wed, 24 Jun 2015 02:33:57 +0530
> From: Manikanta Kondeti <mani.iiit123 at gmail.com>
> To: pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
> Subject: Re: [pgrouting-users] Reg: Get nearest node in osm data
> Message-ID:
> <CAMxCCEZpdrsYZh=QXHkZaU2gbAtBgC8jvoTLQm=V=
> O1jc6YwVA at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hi Steve,
>
> Thanks a lot for your response.
>
> On Wed, Jun 24, 2015 at 1:55 AM, Stephen Woodbridge <
> woodbri at swoodbridge.com
> > wrote:
>
> > On 6/23/2015 3:56 PM, Manikanta Kondeti wrote:
> >
> >> Hi,
> >>
> >> I am writing a small application to get the route on the map. I am
> >> using openlayers.js on client and pgrouting as a backend routing library
> >> for my project. When a click is triggered I get a lat, lon, and I have
> >> the osm data in postgres which is loaded using osm2pgrouting. I'll pass
> >> this lat,lon to server and I have to find the nearest node id from this
> >> lat,lon. Can I query the database and get it done? Help me out.
> >>
> >
> > Probably you want to do this in two steps:
> >
> > 1. find the nearest edge
> > 2. find the nearest node along the edge
> >
> > Anyway your queries will be something like:
> >
>
> This is working perfect and found the nearest edge.
>
>
> > -- get the closest edge
> > select * from edges where st_dwithin(geom, st_setsrid(st_makepoint(long,
> > lat), 4326), maxdist) order by st_distance(geom,
> > st_setsrid(st_makepoint(long, lat), 4326)) asc limit 1;
> >
> >
> This is giving me syntax error. I've added the screenshot
>
>
> > -- get closest node along edge
> > select case when st_line_locate_point(geom,st_setsrid(st_makepoint(long,
> > lat), 4326)) < 0.5 then source else target from edges edge_id=id
> >
> >
> >
> >
> This is not giving me any nearest nodes. I've this screenshot as well.
>
>
> > -- or search to just get the closest node
> > select * from nodes where st_dwithin(geom, st_setsrid(st_makepoint(long,
> > lat), 4326), maxdist) order by st_distance(geom,
> > st_setsrid(st_makepoint(long, lat), 4326)) asc limit 1;
> >
> >
> > where:
> >
> > maxdist - maximum distance to search for geometries in db units probably
> > degrees
> >
> > long, lat - values for the location in question
> >
> > id - is the id of the closest edge found in the first query
> >
>
>
>
> Let me know if I miss something.
>
> Thanks,
> Mani
>
>
>
> > _______________________________________________
> > Pgrouting-users mailing list
> > Pgrouting-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/pgrouting-users
> >
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150624/e0c842f9/attachment.html
> >
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: Screen Shot 2015-06-24 at 2.30.06 AM.png
> Type: image/png
> Size: 29586 bytes
> Desc: not available
> URL: <
> http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150624/e0c842f9/attachment.png
> >
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: Screen Shot 2015-06-24 at 2.33.45 AM.png
> Type: image/png
> Size: 78018 bytes
> Desc: not available
> URL: <
> http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150624/e0c842f9/attachment-0001.png
> >
>
> ------------------------------
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
> End of Pgrouting-users Digest, Vol 81, Issue 2
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150624/1fadf753/attachment-0001.html>
More information about the Pgrouting-users
mailing list