[OSM-dev] intermediate spacial grid for postgis joins

[sav123]

Hello All,

context : postgres database, cartoocss

I was trying to build the exact table for the cartoo css layer 
"turning_circle_casing".
It is a join between the tables line and point at high zoom.

sql from cartoo css authors embedded in a create table by a script :
CREATE TABLE  IF NOT EXISTS turning_circle_casing_raw AS select * from
(SELECT DISTINCT ON (p.way)
p.way AS way, l.highway AS int_tc_type,
CASE WHEN l.service IN ('parking_aisle', 'drive-through', 'driveway')
THEN 'INT-minor'::text
ELSE 'INT-normal'::text
END AS int_tc_service,v.prio
FROM planet_osm_point p
JOIN planet_osm_line l ON ST_DWithin(p.way, l.way, 0.1)
JOIN (VALUES
('tertiary', 1),
('unclassified', 2),
('residential', 3),
('living_street', 4),
('service', 5)
) AS v (highway, prio)
ON v.highway=l.highway
WHERE p.highway = 'turning_circle'
OR p.highway = 'turning_loop'
) as creator  WHERE ST_IsValid(way) ORDER BY 
ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) COLLATE "C";

1,597,250 selected rows, 28800 seconds to build , 50 seconds to index on 
geometry col.

Then I produced an intermediate table to divide by 60 one of the tables :
CREATE TABLE  IF NOT EXISTS planet_osm_point_casing AS ( select way from
planet_osm_point where highway in ('turning_circle','turning_loop') )  ;
CREATE INDEX IF NOT EXISTS planet_osm_point_casing_i ON 
planet_osm_point_casing USING GIST (way) ;
ANALYZE planet_osm_point_casing;

      3 or 4 minutes to get the new table with 1,631,000 record instead of 
the original 103,755,000.


The request execution time was reduced to 25000 seconds. Still too much.

Then I built a regular grid table 256 x 256 from -20037508 to 20037508 on x 
and on y
insert into local_join_grid values
('BOX3D(xmin ymin,xmax ymax)'::box3d), etc ...
looping on xmin ymin,xmax ymax to get 65536 records.

and I modified the above request like that :

CREATE TABLE  IF NOT EXISTS turning_circle_casing_raw AS select * from
(
SELECT DISTINCT ON (p.way)
p.way AS way, l.highway AS int_tc_type,
CASE WHEN l.service IN ('parking_aisle', 'drive-through', 'driveway')
THEN 'INT-minor'::text
ELSE 'INT-normal'::text
END AS int_tc_service,v.prio
FROM local_join_grid b
JOIN planet_osm_point_casing p on p.way && ST_SetSRID(b.bbox, 3857)
JOIN planet_osm_line l ON l.way && ST_SetSRID(b.bbox, 3857) and
l.highway in ('tertiary', 
'unclassified','residential','living_street','service') and 
ST_DWithin(p.way, l.way, 0.1)
JOIN (VALUES
('tertiary', 1),
('unclassified', 2),
('residential', 3),
('living_street', 4),
('service', 5)
) AS v (highway, prio)
ON v.highway=l.highway
) as creator   WHERE ST_IsValid(way) ORDER BY 
ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) COLLATE "C";

and I got the result in 1802 s, index in 43 s. It is 15 times faster.
I suppose that the optimal grid size depends of the hardware and the 
postgresql configuration.


The comparison between the 2 results shows some rows missing. It is because 
the grid which is not correct. It cuts streets.
I know 1 or 2 heavy heurisitics to build a good grid if it doesn't exist.
I could also start with existent boundaries.

Does the grid exist ? some ideas ?

:)
Igael