PostGIS optimizations

[ppKrauss]

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]

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]

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]

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
#4 (comment)
.

[ppKrauss]

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]

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
#4 (comment)
.

[ppKrauss]

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

• 1.8 million streets into 317k sectors = 1.8*1000000/317000 = 1800/317 = 5.7... So ~6 streets per sector. PS: one street is (really?) one osm_id?
• 5.3 million intersections into 317k sectors = 5300/317 = 16.7... So ~17 intersections per sector

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,

• With QGIS, topological tools
• using PostGIS, a raster algorithm (loosing some position-precision but as you say the CNEFE is not precise), or a vector algorithm, starting with subtraction (st_difference) of the CNEFE-setores intersections, and repeting many tyny st_buffer and st_difference(st_intersection) procedures.
• using PostGIS and R, generating regular point grid inside each setor, and creating a city-scale Voronoi, using the points as samples...

---

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(st_intersection).

[lucasmation]

@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