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
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.