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, LineWHERE 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.
أكثر...
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, LineWHERE 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.
أكثر...