Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


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:

    Data as
    ( SELECT gid,geom FROM AS Mp
        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'
    Data, Line
    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

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:

     ST_Distance(line.way, st_transform(
         st_setsrid(st_makepoint(36.768676, -1.289927), 4326),
         900913)) as distance 
                   st_setsrid(st_makepoint(36.768676, -1.289927), 4326),

-[ 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


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.