Hi, I have two tables, one containing polygons of building and one containing polygons of German zipcodes. The idea is to identify and matching the building to its zipcode. But I am not getting any matching results. Can someone pls point me to the right direction. My SQL code is blow. SELECT a.osm_id,a.geo, b.plz FROM building_check2 a LEFT JOIN plz_gebiete b on ST_Intersects(a.geo, b.geom); asked 09 Aug '17, 00:20  mrparadox showing 5 of 8 show 3 more comments |
This is a static archive of our old OpenStreetMap Help Site.
Please post any new questions and answers at community.osm.org.
Not enough info to answer the question. Are the two tables in the same co-ordinate system for instance.
I manged to get the right results after setting the co-ordinate systems. But the performance is very slow. I have about 8000 postcodes polygons and 26 Mil nodes. search for one node takes about 0.3 seconds. ST_Within reduces it to about 0.25s. Is there a way to improve the search or an alternative way? My aim is to get the number of buildings for each German postcode.
What indices do you have on the tables?
I don't have any.
Building and post code table have name and coordinates. Building is single node and post code contain a polygon as coordinates.
Well then I wouldn't be surprised that it is slow :-)
The problem is that most buildings don't have any tags and i cannot figure a good way to index them. Do you have any idea how I could improve the search?
Add a GIST index on a.geo and another on b.geom.
Unfortunately didn't make much of a difference.