search

lucasmation / osm_cnefe_import

Scripts to prepare CNEFE data, on street names and addresses in Brasil, to be imported/integrated into Opeen Street Map
8 stars 3 forks source link

PostGIS optimizations #4

Open ppKrauss opened 8 years ago

ppKrauss commented 8 years ago

About optimization analysis at
OSM_Streets_SetorCensitario_spatial_match.sql and stackoverflow.com question. try 

CREATE TABLE OSM_Streets_by_SetorCensitario AS
SELECT OSM_Streets_by_Mun.*, setor_censitarioL.geom
FROM 
OSM_Streets_by_Mun AS street  INNER JOIN setor_censitario AS setor
   ON   OSM_Streets_by_Mun.cod_UF = substring(setor_censitarioL.cd_geocodi,1,2) AND
        OSM_Streets_by_Mun.cod_mun = substring(setor_censitarioL.cd_geocodi,1,7)
WHERE ST_DWithin(way,setor_censitarioL.geom,0.005)
--  instead ST_Intersects(way,ST_Buffer(setor_censitarioL.geom,0.005))=true

See ST_DWithin() at guide.

lucasmation commented 8 years ago

oba! it works!

I managed to make it work with option "G" of OSM_Streets_SetorCensitario_spatial_match.sql It runs in 1h36min, returning 22.3 million street-setor intersections (buffer of 0.005 was quite large (I need to reduce the size to something more meaningfull)

I'll now try to implement your && suggestion

ppKrauss commented 8 years ago

Ok, and your cache with geom_buffed field is a good strategy for performance (!)... Anyhow, for simple queries, remember that the ST_DWithin() function is an alias for "&& and intersection with st_buffer", it uses internally the BBOX test optimization (&&), and avoid polygon construction overhead of buffer.

About "22.3 million street-setor intersections", you need to explain (and illustrate with images) better what you need.

lucasmation commented 8 years ago

I created table listing all intersections of OSM-street segments with the setores censitários shapefile. Because the setores censitarios are imprecise I used a buffer of 0.005 wsg84 units (which are decimal degrees)

I guess you are asking why the number of intersections is so large. From 1.8 million streets and 317k sectors, how could we get 23 million intersections?

The problem is that the buffer I used is very large, so each sector became huge, and with lots of overlaps. I'll now try to refine this, lowering the buffer.

Ideally the buffer would be proportional to the amount of distortion (dislocation to a certain direction) in each city. But that is for much latter in the project

On Mon, Oct 5, 2015 at 8:56 AM, Peter notifications@github.com wrote:

Ok, and your cache with geom_buffed field is a good strategy for performance (!)... Anyhow, for simple queries, remember that the ST_DWithin() function is an alias for "&& and intersection with st_buffer", it uses internally the BBOX test optimization (&&), and avoid polygon construction overhead of buffer.

About "22.3 million street-setor intersections", you need to explain (and illustrate with images) better what you need.

— Reply to this email directly or view it on GitHub https://github.com/lucasmation/osm_cnefe_import/issues/4#issuecomment-145505335 .

ppKrauss commented 8 years ago

I think you must to transform your cover of CNEFE-setores into a real tiling of convex polygons... That is, approximate them to a Voronoi tile-coverage.

For PostGIS see this discussion.

lucasmation commented 8 years ago

Peter, I did not get your point.

I lowered the buffer to 0.0005 decimal degrees (+ or - 55m) . Now there are 5.3 million street - Setor intersections

Em Seg, 5 de out de 2015 9:34 AM, Peter notifications@github.com escreveu:

I think you must to transform your cover of CNEFE-setores into a real tiling of convex polygons... That is, approximate them to a Voronoi tile-coverage https://en.wikipedia.org/wiki/Voronoi_diagram.

For PostGIS see this discussion http://gis.stackexchange.com/q/114764/7505.

— Reply to this email directly or view it on GitHub https://github.com/lucasmation/osm_cnefe_import/issues/4#issuecomment-145514241 .

ppKrauss commented 8 years ago

Yes, good result, it is a good reference value:

If your algorithm is counting twice or more intersections of the same street, the explanation of "17>6" is that there are some kind of "street folding", with distinct lines crossing sections... By other hand, if the algorithm is not counting "foldings", the result "17>6" can be explained as an effect of buffering, because adds a kind of "317k stretching-sectors of 55m".

I still do not comfortable with "random 55m" (and flutuations with altitude, etc. in lat-long metrics) is an error with the "one urban block" order of magnitude... The order of the objects that we need to analyse.

Thre are many ways to obtain "perfect tiling of CNEFE-setores", try with little fragments of your map, what you preffer,

PS: answering " I did not get your point"... The point is the "tiling problem", the sectors coverage must be perfect, without holes or overlapping areas... The solution for polygons resembles Voronoi (illustrated as b below), but is not (only when polygons are near points). The starting point, to obtain disjointing polygons (as a below), is the _st_difference(stintersection).

enter image description here

lucasmation commented 8 years ago

@ppKrauss tks.

quick notes:

1) no duplicates are generated (I checked with a count query with distinct). This is because streets and sectors come from different tables. (st_intersects() on the same table does indeed generate duplicates, but not the case here). 2) In theory the same street can be divided into multiple seguiments in OSM (osm_id). Depends how it was mapped. And I think OSM "schema" is such that streets tend to be divided. For instance, if there is a speed limit change, I think the mapper would have to divide the street into separate segments for each speed limit. 3) The problems with the SC polygons are a bit complex, and I try do address them in this project: https://github.com/lucasmation/corrige_setor_censitario_2010