Matching GPS points to the road network

Question

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?

Answer 1

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.

Answer 2

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"