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;
···
2015-06-24 8:30 GMT-03:00 <pgrouting-users-request@lists.osgeo.org>:
Send Pgrouting-users mailing list submissions to
pgrouting-users@lists.osgeo.orgTo 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@lists.osgeo.orgYou can reach the person managing the list at
pgrouting-users-owner@lists.osgeo.orgWhen replying, please edit your Subject line so it is more specific
than “Re: Contents of Pgrouting-users digest…”Today’s Topics:
- Re: Reg: Get nearest node in osm data (Manikanta Kondeti)
- 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@gmail.com>
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: Re: [pgrouting-users] Reg: Get nearest node in osm data
Message-ID:
<CAMxCCEaqSjAQX+5T0WigOeSWoM+e21cFM5zgq=TRQdV5AQ-egw@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,
ManiOn Wed, Jun 24, 2015 at 2:33 AM, Manikanta Kondeti <mani.iiit123@gmail.com>
wrote:Hi Steve,
Thanks a lot for your response.
On Wed, Jun 24, 2015 at 1:55 AM, Stephen Woodbridge <
woodbri@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:
- find the nearest edge
- 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=idThis 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
degreeslong, 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@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@gmail.com>
To: pgrouting-users@lists.osgeo.org
Subject: Re: [pgrouting-users] Pgrouting-users Digest, Vol 81, Issue 2
Message-ID:
<CAOikBm5NrioWTdRUjDBOYQygDokH-gGnM73PCrwDadaopyc6cQ@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@lists.osgeo.org>:
Send Pgrouting-users mailing list submissions to
pgrouting-users@lists.osgeo.orgTo 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@lists.osgeo.orgYou can reach the person managing the list at
pgrouting-users-owner@lists.osgeo.orgWhen replying, please edit your Subject line so it is more specific
than “Re: Contents of Pgrouting-users digest…”Today’s Topics:
- Reg: Get nearest node in osm data (Manikanta Kondeti)
- Re: Reg: Get nearest node in osm data (Stephen Woodbridge)
- 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@gmail.com>
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: [pgrouting-users] Reg: Get nearest node in osm data
Message-ID:
<CAMxCCEbigsduMLqQXQUsd=SzBi76F1ZZy1bcFhd9DG=
WKvT_wg@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@swoodbridge.com>
To: pgrouting-users@lists.osgeo.org
Subject: Re: [pgrouting-users] Reg: Get nearest node in osm data
Message-ID: <5589C0D1.3080809@swoodbridge.com>
Content-Type: text/plain; charset=windows-1252; format=flowedOn 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:
- find the nearest edge
- 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
degreeslong, 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@gmail.com>
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: Re: [pgrouting-users] Reg: Get nearest node in osm data
Message-ID:
<CAMxCCEZpdrsYZh=QXHkZaU2gbAtBgC8jvoTLQm=V=
O1jc6YwVA@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@swoodbridge.comwrote:
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:
- find the nearest edge
- 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=idThis 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
degreeslong, 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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-usersEnd 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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-usersEnd of Pgrouting-users Digest, Vol 81, Issue 3
…
Omar Fernando Pessôa
http://www.opessoa.com
Desenvolvedor de sistemas
Programador C# e C++