[pgrouting-users] Pgrouting-users Digest, Vol 81, Issue 3
Omar Fernando Pessôa
omar.pessoa at gmail.com
Wed Jun 24 05:10:29 PDT 2015
Sorry...
It's this!
SELECT source, target, id, percentual from (SELECT the_geom,
ST_GeometryFromText('POINT(%.4f %.4f)', 4326) as point, osm_name, source,
target, id, ST_LineLocatePoint(the_geom,ST_GeometryFromText('POINT(%.4f
%.4f)', 4326)) as percentual FROM ruas ORDER BY the_geom <#>
ST_GeometryFromText('POINT(%.4f %.4f)', 4326) LIMIT 100)a order by
st_distance(point, the_geom) limit 1;
..
*Omar Fernando Pessôa*
http://www.opessoa.com
Desenvolvedor de sistemas
Programador C# e C++
2015-06-24 8:30 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. Re: Reg: Get nearest node in osm data (Manikanta Kondeti)
> 2. Re: Pgrouting-users Digest, Vol 81, Issue 2 (Omar Fernando Pess?a)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 24 Jun 2015 05:02:52 +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:
> <CAMxCCEaqSjAQX+5T0WigOeSWoM+e21cFM5zgq=
> TRQdV5AQ-egw at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Sorted it.
>
> After small correction:
> select case when
> st_line_locate_point(the_geom,st_setsrid(st_makepoint(67.338263167225,
> -17.1189713223), 4326)) < 0.5 then (source) else (target) END AS node FROM
> ways WHERE gid=400280;
>
> Thank you,
> Mani
>
> On Wed, Jun 24, 2015 at 2:33 AM, Manikanta Kondeti <mani.iiit123 at gmail.com
> >
> wrote:
>
> > 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/6654cb35/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 2
> Date: Wed, 24 Jun 2015 08:30:39 -0300
> From: Omar Fernando Pess?a <omar.pessoa at gmail.com>
> To: pgrouting-users at lists.osgeo.org
> Subject: Re: [pgrouting-users] Pgrouting-users Digest, Vol 81, Issue 2
> Message-ID:
> <
> CAOikBm5NrioWTdRUjDBOYQygDokH-gGnM73PCrwDadaopyc6cQ at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> 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.html
> >
>
> ------------------------------
>
> _______________________________________________
> 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 3
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150624/731ceacd/attachment-0001.html>
More information about the Pgrouting-users
mailing list