adaption-of-osm2postgresql_05rc4.sql

-- -- -- -- -- --
--  POLYGONS (for geographies)- --
-- -- -- -- -- --
-- CREATE TABLE WITH POLYGONS MADE OF A SINGLE LINESTRING
-- For each way determine the closed linestrings and store them in a separate table
DROP TABLE simple_polys;
CREATE TABLE simple_polys AS(
  SELECT id AS way_id,
    ST_MakePolygon(linestring::geometry)::geography as polygon,
    ST_Area(ST_MakePolygon(linestring::geometry)::geography) as area -- TODO: convert into metric system (or better do it on projected, metric data, processing eveything after reprojection)
  FROM ways
  WHERE ST_IsClosed(linestring::geometry)
    and ST_NPoints(linestring::geometry) > 3
-- new in version 0.2
--    and idint4 not in (SELECT member_id FROM relation_members WHERE member_role = 'outer')
);
ANALYZE VERBOSE simple_polys;
CREATE UNIQUE INDEX idx_simple_polys_way_id ON simple_polys (way_id);


-- TODO Check the code below
-- I would prefer to get rid of the mods of the orginal schema

-- ADD polygons to TABLE relations
-- Use the geometry datatype here, and only cast back to geography when done with
-- calculations
ALTER TABLE relations DROP COLUMN outerring_array;
ALTER TABLE relations DROP COLUMN outerring_linestring;
ALTER TABLE relations DROP COLUMN outerring;
ALTER TABLE relations DROP COLUMN innerring_linestring;
ALTER TABLE relations DROP COLUMN polygon;

ALTER TABLE relations ADD COLUMN outerring_array bigint[];
ALTER TABLE relations ADD COLUMN outerring_linestring geometry;
ALTER TABLE relations ADD COLUMN outerring geometry;

ALTER TABLE relations ADD COLUMN innerring_linestring geometry[];

ALTER TABLE relations ADD COLUMN polygon geography;

-- Create an ARRAY of all outerrings
UPDATE relations SET outerring_array = (
  SELECT  array_agg( r1.member_id) as array1
  FROM relation_members r1, ways
  WHERE r1.member_role = 'outer'
    and r1.relation_id = relations.id
    and ways.id = r1.member_id
    and ST_NPoints(ways.linestring::geometry) > 1 and ST_IsValid(ways.linestring::geometry)
  GROUP BY r1.relation_id
);

-- Create outerring linestring (not checked if valid yet)
UPDATE relations SET outerring_linestring = (
  SELECT   ST_LineMerge(ST_Collect(ways.linestring::geometry))
  FROM relation_members r1, ways
  WHERE r1.member_role = 'outer'
  and r1.relation_id = relations.id
  and 
  ST_NPoints(ways.linestring::geometry) > 1 and ST_IsValid(ways.linestring::geometry) 
  and ways.id = r1.member_id
  GROUP BY r1.relation_id
);


-- Create innerrings linestrings (not checked if valid yet)
UPDATE relations SET innerring_linestring = (
    ARRAY(
          SELECT ST_LineMerge(ST_Collect(linestring::geometry)) as inner_ring FROM ways WHERE ways.id  IN
            (SELECT member_id FROM relation_members r1
                WHERE r1.relation_id = relations.id and r1.member_role = 'inner'
            )
        )
  )
  WHERE array_length(ARRAY(
          SELECT ST_LineMerge(ST_Collect(linestring::geometry)) as inner_ring FROM ways WHERE ways.id  IN
            (SELECT member_id FROM relation_members r1
                WHERE r1.relation_id = relations.id and r1.member_role = 'inner'
            )
        ),1) >0 --check that there is at least one inner line
;

ALTER TABLE relations DROP COLUMN poly_type;
ALTER TABLE relations ADD COLUMN poly_type text;

UPDATE relations SET poly_type= 'unknown';



-- A ring with only 3 points is flat: A-B-A (1st point = 3rd point), hence buggy
UPDATE relations SET poly_type= 'no valid outerring' WHERE 
ST_NPoints(outerring_linestring::geometry) < 4 -- 5 relations are buggy in italy.osm
or outerring_linestring IS NULL -- about 16000 (relations between simple nodes?)
; 
-- The above must be done before what follows, because if less than 3 points, test may crash
UPDATE relations SET poly_type= 'no valid outerring' WHERE 
poly_type = 'unknown'
and NOT ST_IsClosed( outerring_linestring); -- 136 are buggy in italy.osm

UPDATE relations SET poly_type= 'no valid outerring' WHERE 
poly_type = 'unknown'
and NOT ST_IsSimple( outerring_linestring); -- 102 more are buggy in italy.osm


-- If (NOT poly_type= 'no valid outerring') after the above, it means there is a valid outerring. Now, let us see if there is a valid inerring (or several)

-- If there is no inner line, there is no valid inerring
UPDATE relations SET poly_type= 'no valid inerring'
WHERE poly_type = 'unknown'
and innerring_linestring IS NULL
; -- 3015 more have no valid inerring

-- Innering must be closed
UPDATE relations SET poly_type= 'no valid inerring'
WHERE poly_type = 'unknown'
and (NOT ST_IsClosed(ST_LineMerge(ST_Collect(innerring_linestring ))))
; -- 44 more are buggy

-- Innering must be big enough
-- FIXME: this checks that all the innerings together have more than 3 points, not that each innering is valid
UPDATE relations SET poly_type= 'no valid inerring'
WHERE poly_type = 'unknown'
and ST_NPoints(ST_LineMerge(ST_Collect(innerring_linestring ))) <4
; 



-- Check further validity of innerring: closed and big enough
-- However, if there are several holes and only one is too small, the test based on NPoints()
-- will not be sensitive enough (such wrong relations are probably extremely rare; none found in italy.osm).
-- FIXME: is this still necessary after the tests above?
UPDATE relations SET poly_type= 'valid innerring'
WHERE poly_type = 'unknown'
and ( ST_IsClosed(ST_LineMerge(ST_Collect(innerring_linestring ))))
and ST_NPoints(ST_LineMerge(ST_Collect(innerring_linestring ))) > 3
; 

-- SELECT id,poly_type FROM relations WHERE ISClosed(ST_LineMerge(ST_Collect(innerring_linestring ))) and NPoints(ST_LineMerge(ST_Collect(innerring_linestring )))=3; --should give no result if OSM data were perfect; not the case (2 results for italy.osm).

-- TODO: try to collect the tags FROM the rings and assign them to the relation if relevant

UPDATE relations SET polygon = 
ST_MakePolygon(outerring_linestring, (innerring_linestring ))::geography
WHERE poly_type= 'valid innerring'
and ST_GeometryType(outerring_linestring) ='LINESTRING';


-- Create simple polygon not having (valid) innering(s)
UPDATE relations SET polygon = ST_MakePolygon(outerring_linestring)::geography
WHERE (poly_type = 'no valid inerring'
OR poly_type = 'unknown')
--   and id=1309665
and GeometryType((outerring_linestring)) ='LINESTRING'
;


-- SO FAR EVERYTHING SEEMS FINE


-- Disaggregate multilines into linestrings, one per row
DROP TABLE if exists dumped_multilinestring;
CREATE TABLE dumped_multilinestring AS (
SELECT
    relations.id as relation_id,
    generate_series(
        1,
        (Select ST_NumGeometries(outerring_linestring))
    ) as lineseq,
    ST_GeometryN(outerring_linestring, generate_series(
        1, 
        ST_NumGeometries (outerring_linestring))
    ) AS outerring_linestring
FROM
    relations
WHERE
    GeometryType(outerring_linestring) = 'MULTILINESTRING' --anyway the query would not give any 'LINESTRING'
);

CREATE INDEX idx_dumped_multilinestring_relation_id ON dumped_multilinestring (relation_id);

ALTER TABLE dumped_multilinestring ADD COLUMN outerring_polygon geometry;

UPDATE
    dumped_multilinestring
SET
    outerring_polygon = ST_MakePolygon(outerring_linestring)
WHERE
    ST_IsClosed(outerring_linestring);



-- WTF???
-- ALTER TABLE dumped_multilinestring ADD COLUMN idint4 int4;
-- UPDATE dumped_multilinestring SET idint4 = relation_id*1000 + lineseq; --*might* create duplicate id if more than 999 linestrings in a single multilinestring AND if bad luck

-- Put all polygons in a single table
/*
DROP TABLE IF EXISTS polygons ;
CREATE TABLE polygons AS ( 
  SELECT id as relation_id, polygon, NULL::text as class FROM relations
 UNION ALL
  SELECT polygon, NULL::int4  as relation_id, NULL::text as class FROM simple_polys
 UNION ALL
  SELECT outerring_polygon, relation_id, NULL::text as class FROM dumped_multilinestring
);
*/