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.
No comments:
Post a Comment