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, 00:20

mrparadox's gravatar image

mrparadox
111
accept rate: 0%

Not enough info to answer the question. Are the two tables in the same co-ordinate system for instance.

(09 Aug, 16:35) SK53 ♦

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.

(11 Aug, 11:28) mrparadox

What indices do you have on the tables?

(11 Aug, 12:06) SimonPoole ♦

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.

(11 Aug, 12:11) mrparadox

Well then I wouldn't be surprised that it is slow :-)

(11 Aug, 12:45) SimonPoole ♦

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?

(11 Aug, 12:54) mrparadox

Add a GIST index on a.geo and another on b.geom.

(11 Aug, 13:40) Richard ♦

Unfortunately didn't make much of a difference.

(11 Aug, 14:32) mrparadox
showing 5 of 8 show 3 more comments
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×430
×140
×96
×85

question asked: 09 Aug, 00:20

question was seen: 81 times

last updated: 11 Aug, 14:32

powered by OSQA