Friday, 29 January 2016

postgis - How can I map data points to OpenStreetMap (OSM) line data?


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

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...