|
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);
showing 5 of 8
show 3 more comments
|
NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 09 Aug '17, 00:20
question was seen: 1,734 times
last updated: 11 Aug '17, 14:32
Related questions
NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum
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.