1

I am a newbie in Postgres and Postgis and I am trying to do a matching of GPS points to the road network. I need to find closest road from a shapefile and take speed attribute from it. I am using postgreSQL 9.3.7 and postgis 2.1.7.

I have imported a shapefile of the road network into my database. There are many columns, among others: speedlimit, rlid, startdate, enddate, startdistance, enddistance, direction and geom.

I feel unsure about SRID. When I plugged the contents of the .prj file into prj2epsg.org, I got 3006 - SWEREF99_TM as result, and that's what I used to import the shapefile. But I don't know if I need to transform it to 4326, because GPS-points I want to match are longitude and latitude I got from Google maps.

The query:

select distinct(ST_SRID(mytable.geom)) as srid, count(*) from mytable group by srid;

gives: 3006; 2674798

null; 930

I found some solutions and tried them:

Query 1:

SELECT speedlimit, ST_Distance(ST_GeomFromText('POINT(lat long)',3006),geom) 
AS distance FROM mytable ORDER BY distance ASC LIMIT 1;

70;6146326.22657711 (this query always gives same result, even if I change GPS points)

Query 2:

SELECT ST_makePOINT(lat long) as gps_point, 
ST_Distance( ST_Closestpoint( st_setSRID(r.geom,4326), st_setSRID(ST_makePOINT(lat long),4326) ) ,
ST_makePOINT(lat long) ,true ) as distance_with_c_p, r.speedlimit
FROM mytable r WHERE (ST_Distance_Spheroid (st_setSRID(r.geom,4326),st_setSRID(ST_makePOINT(lat long),4326)
, 'SPHEROID["GRS 1980",6378137,298.257222101]') < 100 ) 
ORDER BY 2 LIMIT 5

"01010000008849B89047D64C408FFE976BD1222840";1088667.79697218;50 "01010000008849B89047D64C408FFE976BD1222840";1088667.79697218;50 "01010000008849B89047D64C408FFE976BD1222840";3784055.84203355;110 "01010000008849B89047D64C408FFE976BD1222840";5163241.18884849;80 "01010000008849B89047D64C408FFE976BD1222840";5163241.18884849;80

Query 3:

with index_query as (
  select 
    st_distance(geom, 'SRID=3006;POINT(lat long)') as distance,
    mytable.speedlimit
  from mytable 
  order by geom <#> 'SRID=3006;POINT(lat long)' limit 100
)
select * from index_query order by distance limit 1;

6146326.22657711;70 (this query also gives same result even if I change GPS points)

I don't understand why the nearest distance always is so far away. GPS points are taken from the main roads, and I tested several GPS points.

Is it something about SRID, or what am I doing wrong?

2

If you want a distance in meaningful units, you'll want to ST_Transform your GPS points from 4326 to the target projection. Then ST_Distance will give you results in the units of the projection (usually metres, but some North American projections use survey feet instead).

So, instead of ST_GeomFromText('POINT(lat long)',3006), you'd want something like ST_Transform(ST_SetSRID(ST_Point(long,lat),4326),3006).

For performance, you might want to consider creating and indexing a column of the GPS geometry if you'll be using it repeatedly.

  • Thank you for replying! I don't think my queries give correct match. When I choose GPS-points from a road where I know the speed limit is 110 km/h I get: speed limit 70 and distance: 6146326.21602811. After that I choose a road with speed limit of 30 km/h I get: speed limit 70 and distance: 6146326.35002838. So I don't think I get the closest match from the shape file and the speed attribute from it. – user1653278 Jun 4 '15 at 6:59
  • Did you check which order lat and lon should be when constructing a point in EPSG:4326? You wrote lat long in your question, but I believe that longitude (x-coordinate) needs to be specified first. Transposition could give huge errors, depending how far your area is from west Africa... – Toby Speight Jun 4 '15 at 7:28
  • 1
    The combination of your suggestions did the work, thank you! This is the working query SELECT speedlimit, ST_Distance(ST_Transform(ST_SetSRID(ST_Point(long, lat),4326),3006),geom) AS distance FROM mytable ORDER BY distance ASC LIMIT 1; Thanks a lot! Now I can begin indexing, like you said. – user1653278 Jun 4 '15 at 8:20
0

Please see the code below:

with index_query as 
(
select 
      st_distance(geom, 'SRID=4326;POINT(long lat)') as distance,
      mytable.speedlimit
from mytable 
order by geom <#> 'SRID=4326;POINT(long lat)' limit 100
)
select * from index_query order by distance limit 1;

Please notes that SRID = 4326 and also switch the "lat long" to "long lat"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.