Hi all,

I am trying to query the total number of building for each German postcode. I am using the the center point of the buildings and postcode shape files. There are around 26 Mil buildings and around 8K postcodes. I am using the most simplest shape file (less points) but the search still takes days. Although, ST_Within performs faster than ST_Intersects it still takes 0.24 seconds for one search. 26 Mil will take weeks. Any suggestions to improve the performance or alternative solutions? My sql is below. Thanks.

SELECT a.osm_id b.plz FROM building_check a LEFT JOIN plz_test b on ST_Within(a.geo, b.geom);

asked 10 Aug, 15:22

mrparadox's gravatar image

mrparadox
113
accept rate: 0%

closed 10 Aug, 19:04

SK53's gravatar image

SK53 ♦
19.5k40206311

Not informative, you need to add more info: "hat indexes are placed on the tables? What does a query plan look like? Also this is a generic PostGIS query & may be more appropriate for GIS StackExchange.

(10 Aug, 19:02) SK53 ♦

The question has been closed for the following reason "Duplicate Question: Asked in similar form https://help.openstreetmap.org/questions/57522/osm-sql-query-with-node-and-zip-code-shape-files-using-st_intersects" by SK53 10 Aug, 19:04

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:

×443
×143
×100
×92
×87

question asked: 10 Aug, 15:22

question was seen: 173 times

last updated: 10 Aug, 19:04

powered by OSQA