Trazendo dados pertinentes do OSM e dumps

Apenas os municípios em uso requerem dados do OSM. O próprio OSM requer uma versão mais atualizada para se obter seus dados.

Ver também https://github.com/AddressForAll/geoterm/wiki e Cruzando dados novos com os do OSM e correios.

preparo

Levando a optim.jurisdiction para a base dl01t_osm e conferindo municípios disponíveis.

#psql dl03t_main -- building
#psql dl01t_osm   -- dump OSM Planet
pg_dump  --format plain --verbose --file "/tmp/pg_io/transport01.sql" --table optim.jurisdiction \
         --table optim.donor --table optim.auth_user --table optim.donatedPack \
         dl03t_main
psql dl01t_osm < "/tmp/pg_io/transport01.sql"

select count(*) 
from planet_osm_polygon p inner join optim.jurisdiction j 
     ON  -p.osm_id=j.osm_id and p.tags->'IBGE:GEOCODIGO' ~ '^\d+$' 
     and (p.tags->'IBGE:GEOCODIGO')::bigint=jurisd_local_id
;
CREATE TABLE osm_city AS 
  select j.*, hstore_to_jsonb_loose(p.tags) || jsonb_build_object('z_order',p.z_order) as jtags, p.way as geom
from planet_osm_polygon p inner join optim.jurisdiction j 
     ON  -p.osm_id=j.osm_id and p.tags->'IBGE:GEOCODIGO' ~ '^\d+$' 
     and (p.tags->'IBGE:GEOCODIGO')::bigint=jurisd_local_id
;
select  (jtags->'admin_level')::int as admin_level, count(*) n from osm_city group by 1;
--    4 |   27  -- estados?
--    8 | 5467  -- municipios
--    7 |    1  -- brasilia?

Agora que temos as geometrias dos municipios, trazer todos municípios de optim.origin (a resgatar da base em uso) e interseções com respectivas BBOX.

-- na outra base, select array_agg(distinct jurisd_osm_id) from optim.origin; -- resulta em array abaixo
CREATE VIEW vw01_osm_city AS
  SELECT * FROM osm_city 
  WHERE osm_id IN (  -- positivo
   select unnest('{242324,242397,242595,296625,296650,297533,297564,297599,297611,297687,297897,297919,297968,297991,297995,298019,298216,298227,298253,298285,298413,298424,298426,298442,298470,303585,314856,326253,334022,368782,368786,1825815,1825817,1826737,2220782,2220789}'::bigint[]) as osm_id);

trazendo ruas e nodes

-- DROP TABLE osm_road_lixo; DROP TABLE osm_point; DROP TABLE osm_poly; DROP TABLE osm_road; DROP TABLE osm_road_quadra;

CREATE TABLE osm_road_lixo AS 
  select DISTINCT p.osm_id, c.osm_id as jurisdiction_osm_id,
          (hstore_to_jsonb_loose(p.tags) -'osm_user' -'osm_version' -'osm_changeset') || jsonb_build_object('z_order',p.z_order) as jtags,
          p.way as geom
  from planet_osm_roads p inner join vw01_osm_city c
       ON p.way && c.geom and st_intersects(p.way,c.geom) 
; -- trazendo poucos casos, baixa confiabilidade.
CREATE TABLE osm_point AS 
  select DISTINCT p.osm_id, c.osm_id as jurisdiction_osm_id,
          (hstore_to_jsonb_loose(p.tags) -'osm_user' -'osm_version' -'osm_changeset') || jsonb_build_object('z_order',p.z_order) as jtags,
          p.way as geom
  from planet_osm_point p inner join vw01_osm_city c
       ON p.way && c.geom and st_intersects(p.way,c.geom) 
;
CREATE TABLE osm_poly AS
  select DISTINCT p.osm_id, c.osm_id as jurisdiction_osm_id,
          (hstore_to_jsonb_loose(p.tags) -'osm_user' -'osm_version' -'osm_changeset') || jsonb_build_object('z_order',p.z_order) as jtags,
          p.way as geom
  from planet_osm_polygon p inner join vw01_osm_city c
       ON st_area(p.way,true)<300000000 AND (p.way && c.geom) AND st_contains(c.geom,p.way)
       -- mais usado = p.tags?'building'
;
CREATE TABLE osm_road AS
  select DISTINCT p.osm_id, c.osm_id as jurisdiction_osm_id,
          hstore_to_jsonb_loose(p.tags) || jsonb_build_object('z_order',p.z_order) as jtags,
          p.way as geom
  from planet_osm_line p inner join vw01_osm_city c
       ON p.way && c.geom and st_intersects(p.way,c.geom)
  WHERE p.tags?'highway' AND not(p.tags?'boundary') 
        AND p.tags->'highway' !~ '^(footway|path|cycleway|raceway|track|bridleway|footway)$'
        --  "highway=pedestrian" é calçadão, que vale como via
;
CREATE TABLE osm_road_quadra AS
  select DISTINCT p.osm_id, c.osm_id as jurisdiction_osm_id,
          hstore_to_jsonb_loose(p.tags) || jsonb_build_object('z_order',p.z_order) as jtags,
          p.way as geom
  from planet_osm_line p inner join vw01_osm_city c
       ON p.way && c.geom and st_intersects(p.way,c.geom)
  WHERE NOT(p.tags?'boundary' OR p.tags?'highway') AND (p.tags?'waterway' OR p.tags?'railway')
;

Distinct é redundante, haverão duplicações por conta dos elementos comuns de fronteira.

De volta com material osm na base dl03

#rm "/tmp/pg_io/transport02.sql"
pg_dump  --format plain --verbose --file "/tmp/pg_io/transport02.sql" \
		 -t osm_city -t osm_road_lixo -t osm_point -t osm_poly -t osm_road  -t osm_road_quadra \
         dl01t_osm
psql dl03t_main < "/tmp/pg_io/transport02.sql"

Para indexar na base dl03t_main:

CREATE INDEX on osm_road_lixo  USING GIN(jtags);
CREATE INDEX on osm_point      USING GIN(jtags);
CREATE INDEX on osm_poly       USING GIN(jtags);
CREATE INDEX on osm_road       USING GIN(jtags);
CREATE INDEX on osm_road_quadra USING GIN(jtags);
CREATE INDEX ON osm_city USING GIST (geom);
CREATE INDEX ON osm_road  USING GIST (geom);
CREATE INDEX ON osm_poly  USING GIST (geom);