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.
select distinct(ST_SRID(mytable.geom)) as srid, count(*) from mytable group by srid;
gives: 3006; 2674798
I found some solutions and tried them:
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)
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
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?