Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

my goal is to filter measuring points of vehicles by mapping them to streets. So I could filter out all the false data located in fields and rivers and so on.

I imported my data into a PostGIS database and can work fine with my Point data. I also imported the part of OSM that i need into a PostGIS table.

Is using ST_DWithin the right way? I did use that to only show points in a certain distance to another point.

How can I now get only these points that are located, let's say 20 metres around the OSM lines?

EDIT1:

I have been trying to first filter the data and the OSM line data to a circle area of 5km and then using ST_DWithin to get the mapping:

WITH
    Data as
    ( SELECT gid,geom FROM schema1.data AS Mp
        WHERE Mp.id=14
        AND EXISTS (
            SELECT 1 FROM schema1.base As Base
            WHERE Base."ID"=14
            AND st_covers(st_makeenvelope(-180, -90, 180, 90, 4326), Mp.geom)
            AND ST_DWithin(Mp.geom::geography, Base.geom::geography, 5000)
            )
    ),
    Line As
    ( SELECT * FROM public.planet_osm_line As Line
        WHERE EXISTS (
            SELECT 1 FROM schema1.base As Base
            WHERE Base."ID"=14
            AND ST_DWithin(Line.way::geography, Base.geom::geography, 5000)
            AND Line.highway='motorway'
            )
    )
SELECT
    Data.*
FROM
    Data, Line
WHERE
    st_covers(st_makeenvelope(-180, -90, 180, 90, 4326), Data.geom)
    AND ST_DWithin(Data.geom::geography, Line.way::geography, 20);

Unfortunately "Data" still consists of 5014309 and "Line" of 5686 rows. What can I improve in my query to speed up things here?

If i understand the query execution correctly, postresql is trying to build a temporary table and thus joining Data and Line to 5014309*5686 rows...

I would appreciate any help.

share|improve this question

1 Answer 1

I had the same exact problem. I have points that represent vehicles and would like to get the closest road to within some error tolerance.

In my case, the data I have is in SRID 900913 while the submitted points are lat,lng.

Here's the query that's working for me:

SELECT 
     osm_id, 
     name, 
     ST_XMin(line.way), 
     ST_XMax(line.way), 
     ST_Distance(line.way, st_transform(
         st_setsrid(st_makepoint(36.768676, -1.289927), 4326),
         900913)) as distance 
FROM 
     line 
WHERE 
     ST_DWithin(line.way, 
                st_transform(
                   st_setsrid(st_makepoint(36.768676, -1.289927), 4326),
                   900913),
                5);


-[ RECORD 1 ]---------------
osm_id   | 25060263
name     | Gitanga Road
st_xmin  | 4093024.51
st_xmax  | 4093669.13
distance | 0.993252319680161
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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