[closed] ST_Within vs ST_Intersects slow performance and alternatives

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 '17, 15:22

mrparadox
11●4●4●5
accept rate: 0%

closed 10 Aug '17, 19:04

SK53 ♦
28.1k●48●268●433

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 '17, 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 '17, 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

learn more about Markdown

Question tags:

osm ×710
osm2pgsql ×263
postgresql ×165
query ×147
postgis ×134

question asked: 10 Aug '17, 15:22

question was seen: 4,756 times

last updated: 10 Aug '17, 19:04

[closed] ST_Within vs ST_Intersects slow performance and alternatives

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 '17, 19:04

Follow this question

Related questions