INSERT INTO osm_upgrade.vds_segment_geometry
   (vds_id,adj_pm,refnum,direction,seggeom)
SELECT vds_id,adj_pm,refnum,direction,seggeom
FROM (
  SELECT pdist,ptsec,ndist,vds_id,adj_pm,refnum,
         relation_direction as direction,
         -- vdsid, order, freeway id, freeway dir

         -- AND the computed segment geometry associated with the vds
         CASE WHEN ptsec <= ntsec

-- The previous vds comes before the next.
-- Create a line segment by taking the piece of the
-- route line between ptsec and ntsec

              THEN ST_line_substring( q.rls, ptsec,ntsec )

              ELSE

-- The "previous" vds comes /after/ the
-- "next". Probably this means that they are pointing
-- at different line segments.  Hardcode to zero or one

                    case
                      WHEN mydist > pdist then ST_line_substring( q.rls, ptsec, 1 )
                      WHEN mydist < pdist then ST_line_substring( q.rls, 0, ntsec )
                    end
            END AS seggeom
       FROM
       (

-- here we select the points halfway between a target vds and the
-- vds's on either side of it (p=previous,n=next) along the route.  The
-- points are specified as the distance along the route linestring.

         SELECT vrr.vds_sequence_id AS sequence_id,
                vrr.vds_id,vrr.adj_pm,vrr.refnum,vrr.relation_direction,
                -- vdsid,pm, and osm relation
                vrr.line AS rls,        -- the linestring the vds snaps to
                vrr.dist as mydist,
                vrr.numline AS myline,  -- to which multilines are we snapped?
                p.dist AS pdist,      -- the distance along the
                                      -- line of the previous vds
                CASE WHEN p.dist IS NOT NULL   -- this computes the bisection
                     THEN (vrr.dist+p.dist)/2  -- distance between the previous
                     ELSE 0                    -- and target vds
                     END AS ptsec,
                n.dist AS ndist,      -- the distance along the
                                      -- line of the next vds
                CASE WHEN n.dist IS NOT NULL   -- this computes the bisection
                     THEN (vrr.dist+n.dist)/2  -- distance between the target
                     ELSE 1                    -- and next vds
                     END AS ntsec

            FROM osm_upgrade.vds_route_relation vrr
                 -- joining the previous vdsid
                 LEFT JOIN osm_upgrade.vds_route_relation p
                      ON ( vrr.vds_sequence_id=p.vds_sequence_id+1 AND
                           vrr.refnum=p.refnum AND
                           vrr.relation_direction=p.relation_direction AND
                           vrr.numline=p.numline)
                 -- and joining the next vdsid
                 LEFT JOIN osm_upgrade.vds_route_relation n
                      ON ( n.vds_sequence_id=vrr.vds_sequence_id+1 AND
                           vrr.refnum=n.refnum AND
                           vrr.relation_direction=n.relation_direction AND
                           vrr.numline=n.numline)

                 -- We need to the route line too as all our
                 -- computations are based upon that
                 LEFT JOIN tempseg.numbered_route_lines rl
                      ON (rl.refnum=vrr.refnum AND
                          vrr.relation_direction=rl.direction)
                 -- WHERE
                 -- rl.refnum=99 and rl.direction='north'
                 -- rl.refnum=580
            ORDER BY vrr.vds_sequence_id
       ) q
       ORDER BY sequence_id
) qq WHERE geometrytype(seggeom) !~* 'point'
;

my $vds = $self->get_vds_or_die($pk,$data);

my $new_geoid = $self->get_new_geoid;
my $gid        = $new_geoid->gid;
my $creategeom = $self->create_geompoint_4269($lon,$lat,$gid);

$self->join_geom($vds,$gid,4269);

$new_geoid = $self->get_new_geoid();
my $projectedgid        = $new_geoid->gid;
my $projectedpoint = $self->project_geom_4326($gid,$projectedgid);

method project_geom_4326(Int $gid4269,Int $gid4326){
    my $transform;
    my $test_eval = eval {
        my $arr = [q{ST_AsText(ST_Transform(me.geom,4326))}];
        ($transform) = $self->resultset('Public::GeomPoints4269')->search(
            { 'gid' => $gid4269 },
            {
                select => [ 'gid', \$arr ],
                as => [qw/gid  wkt_transform /],
            },
            );
    };
    if ($EVAL_ERROR) {    # find or create failed
        carp "can't fetch transform $EVAL_ERROR";
        croak;
    }
    my $projectedpoint;
    $test_eval = eval {
        my $arr = [
            q{ST_GeometryFromText(?,4326)},
            ['dummy'=>$transform->get_column('wkt_transform')]
            ];
        $projectedpoint = $self->resultset('Public::GeomPoints4326')->create(
            {
                gid  => $gid4326,
                geom => \$arr,
            }
            );
    };
    if ($EVAL_ERROR) {    # find or create failed
        carp "can't create new tranformed geometry $EVAL_ERROR";
        croak;
    }
    return $projectedpoint;
}

$new_geoid = $self->get_new_geoid();
my $projectedgid        = $new_geoid->gid;
my $projectedpoint = $self->project_geom_4326($gid,$projectedgid);

$self->join_geom($vds,$projectedgid,4326);

SELECT has_table('public','wim_stations','there is a wim_stations table');

SELECT schemas_are(ARRAY[ 'public', 'wim','osm','vds','sqitch','pgRouting' ],
                   'expected schemas exist in database');

-- is there a wim_stations table?
SELECT has_table('public', 'wim_stations', 'there is a wim_stations table');

-- are  there join tables?
SELECT has_table('public', 'wim_points_4269', 'there is a wim_points_4269 table');
SELECT has_table('public', 'wim_points_4326', 'there is a wim_points_4326 table');

-- are there geometry tables?
SELECT has_table('public', 'geom_points_4269','there is a geom_points_4269 table');
SELECT has_table('public', 'geom_points_4326','there is a geom_points_4326 table');

SELECT col_is_pk('public', 'wim_stations', 'site_no', 'wim stations site_no is pk' );
SELECT col_is_pk('public', 'geom_points_4269','gid', 'geom points 4269 gid is pk' );
SELECT col_is_pk('public', 'geom_points_4326','gid', 'geom points 4326 gid is pk' );

SELECT col_is_pk('public', 'wim_points_4326','wim_id', 'geom points 4326 gid is pk' );
SELECT col_is_pk('public', 'wim_points_4269','wim_id', 'geom points 4269 gid is pk' );

SELECT indexes_are('public', 'wim_stations',
                   ARRAY['wim_stations_pkey'],'wim_stations has index' );
SELECT indexes_are('public', 'geom_points_4269',
                   ARRAY['geom_points_4269_pkey','geom_points_4269_geom_index'],
                   'geom_points_4269 has index' );

SELECT fk_ok( :fk_schema,  :fk_table, :fk_columns,
              :pk_schema,  :pk_table, :pk_columns,
              :description );

-- 4269
SELECT fk_ok(
    'public','wim_stations','site_no',
    'public','wim_points_4269','wim_id',
    'wim_points_4269 connects to wim_stations'
);

SELECT fk_ok(
    'public','geom_points_4269','gid',
    'public','wim_points_4269','gid',
    'wim_points_4269 connects to geom_points_4269'
);

-- 4326
SELECT fk_ok(
    'public','wim_stations','site_no',
    'public','wim_points_4326','wim_id',
    'wim_points_4326 connects to wim_stations'
);

SELECT fk_ok(
    'public','geom_points_4326','gid',
    'public','wim_points_4326','gid',
    'wim_points_4326 connects to geom_points_4326'
);

SELECT results_eq(   A,      B,    :description );

SELECT results_eq( :sql,    :sql,    :description );
SELECT results_eq( :sql,    :array,  :description );
SELECT results_eq( :cursor, :cursor, :description );
SELECT results_eq( :sql,    :cursor, :description );
SELECT results_eq( :cursor, :sql,    :description );
SELECT results_eq( :cursor, :array,  :description );

-- make sure join relations are all there
select results_eq(
    'select site_no from wim_stations order by site_no',
    'select wim_id from wim_points_4269 order by wim_id'
);

select results_eq(
    'select site_no from wim_stations order by site_no',
    'select wim_id from wim_points_4326 order by wim_id'
);

-- do the geometries actually match the metadata in all cases?
PREPARE build_geoms_4269 AS
  SELECT ST_GeomFromEWKT('SRID=4269;POINT('||a.longitude||' '||a.latitude||')')
  FROM wim_stations a
  ORDER BY site_no;

PREPARE existing_geoms_4269 AS
  SELECT b.geom
  FROM wim_points_4269 a
  JOIN geom_points_4269 b USING (gid)
  ORDER BY wim_id;

-- expect that the metadata matches the stored 4269 geoms
SELECT results_eq(
    'build_geoms_4269',
    'existing_geoms_4269'
);

-- now test projection
PREPARE projected_geoms_4269_to_4326 AS
  SELECT ST_TRANSFORM(b.geom,4326)
  FROM wim_points_4269 a
  JOIN geom_points_4269 b USING (gid)
  ORDER BY wim_id;

PREPARE existing_geoms_4326 AS
  SELECT b.geom
  FROM wim_points_4326 a
  JOIN geom_points_4326 b USING (gid)
  ORDER BY wim_id;


--expect that the transformed 4269 geoms matches the stored 4326 geoms
SELECT results_eq(
    'projected_geoms_4269_to_4326',
    'existing_geoms_4326'
);

sqitch init calvad_wim_geometries --uri https://github.com/jmarca/calvad_wim_geometries --engine pg
createdb -U slash -h 127.0.0.1 brokendb
sqitch target add brokendb db:pg://slash@127.0.0.1/brokendb
sqitch engine add pg brokendb
sqitch add brooklyn  -m 'Hello Brooklyn!'
sqitch deploy

./node_modules/.bin/mocha test/test_deploy.js --timeout 0

SET client_min_messages TO warning;
CREATE EXTENSION IF NOT EXISTS pgtap;
RESET client_min_messages;

BEGIN;
SELECT no_plan();
-- SELECT plan(1);

SELECT pass('Test fix_4269_geoms!');

-- do the geometries actually match the metadata in all cases?
PREPARE build_geoms_4269 AS
  SELECT ST_GeomFromEWKT('SRID=4269;POINT('||a.longitude||' '||a.latitude||')')
  FROM wim_stations a
  ORDER BY site_no;

PREPARE existing_geoms_4269 AS
  SELECT b.geom
  FROM wim_points_4269 a
  JOIN geom_points_4269 b USING (gid)
  ORDER BY wim_id;

-- expect that the metadata matches the stored 4269 geoms
SELECT results_eq(
    'build_geoms_4269',
    'existing_geoms_4269'
);

SELECT finish();
ROLLBACK;

BEGIN;
-- first, clear the decks
drop table  wim_points_4269;

-- recreate without any FK to geom_points
CREATE TABLE wim_points_4269 (
    gid integer NOT NULL,
    wim_id integer NOT NULL  primary key REFERENCES wim_stations (site_no)   ON DELETE CASCADE
);

-- get rid of gid relationship in geom_points
ALTER TABLE ONLY geom_points_4269
    DROP CONSTRAINT geom_points_4269_gid_fkey;
CREATE SEQUENCE geom_points_4269_gid_seq;
ALTER TABLE geom_points_4269
    ALTER COLUMN gid SET DEFAULT
    nextval('geom_points_4269_gid_seq');
ALTER SEQUENCE geom_points_4269_gid_seq OWNED BY geom_points_4269.gid;
SELECT setval('geom_points_4269_gid_seq',nextval('geom_ids_gid_seq'));

-- try to fix the broken geometries

-- create geoms holds geometries created from the metadata
WITH create_geoms AS (
  SELECT site_no, ST_GeomFromEWKT('SRID=4269;POINT('||a.longitude||' '||a.latitude||')') as geom
  FROM wim_stations a
  ORDER BY site_no
),

-- some of the geoms are likely repeated.  only make one
distinct_geoms AS (
  select distinct geom from create_geoms
),

-- Only keep those points that are not already in the
-- geom_points_4269 table
not_in_geoms_table AS (
  select a.geom
  from distinct_geoms a
  left outer join geom_points_4269 b on (a.geom=b.geom)
  where b.geom is null
),

-- insert the new geometries
inserted_geoms AS (
  INSERT INTO geom_points_4269 (geom)
  SELECT geom
  FROM not_in_geoms_table
    RETURNING geom,gid
),

-- combine both new and old for pairing off in join table
new_and_old as (
  select a.geom,a.gid
  from geom_points_4269 a
  join distinct_geoms b on (a.geom=b.geom)
  union
  select aa.geom,aa.gid
  from inserted_geoms aa
  join distinct_geoms b on (aa.geom=b.geom)
),

-- there is no guarantee that geometries in
-- geom_points_4269 are unique
-- so reduce new_and_old using DISTINCT
unique_set as (
  select distinct min(gid) as gid,geom
  from new_and_old
  group by geom
),

-- finally, join wim site numbers with the newly created gid values by
-- joining the tables on geom.  I expect multiple gids might occur
-- here.
wim_join_geoms AS (
  select a.site_no as wim_id,b.gid
  from create_geoms a
  join unique_set b ON(a.geom = b.geom)
)
INSERT INTO wim_points_4269 (wim_id,gid)
SELECT * FROM wim_join_geoms order by wim_id;

-- now that the geometries are paired off, reinstate the fk constraint
ALTER TABLE ONLY wim_points_4269
    ADD CONSTRAINT wim_points_4269_gid_fkey FOREIGN KEY (gid)
        REFERENCES geom_points_4269(gid)
    ON DELETE CASCADE;

DROP table if exists tempseg.relation_direction_geometries;

CREATE SEQUENCE tempseg_relation_direction_geometries_seq increment by 2;

SELECT rr.id as relation_id,sequence_id,linestring,
nextval('tempseg_relation_direction_geometries_seq') as ordering,
       CASE WHEN rr.direction = 'roles' THEN
            CASE WHEN m.member_role='' then 'any'
                 WHEN m.member_role is null then 'any'
                 --handle US:US 6, North&East, West&South
                 WHEN relation_id=337473 then
                      CASE WHEN m.member_role='east' then 'north'
                           WHEN m.member_role='west' then 'south'
                           ELSE m.member_role
                      END
                 ELSE m.member_role
            END
            WHEN rr.direction='' then 'any'
            WHEN rr.direction is null then 'any'
            ELSE rr.direction
       END as direction
into tempseg.relation_direction_geometries
FROM route_relations rr
JOIN relation_members AS m ON (rr.id=m.relation_id)
JOIN ways AS w ON ( member_id = w.id )
WHERE npoints(linestring) > 1  -- has to be a line or the st_collect breaks
-- and relation_id=69364
order by relation_id,direction,sequence_id;


-- first fix the directions of any to be all

drop table if exists tempseg.nother_table;
select rdg.relation_id,rdg.sequence_id,rdg.linestring,rdg.ordering,b.direction
into tempseg.nother_table
from tempseg.relation_direction_geometries rdg
join (select distinct relation_id,direction
      from tempseg.relation_direction_geometries d
      where direction != 'any') b on (b.relation_id=rdg.relation_id)
where rdg.direction='any';

-- Theoretically, that is every 'any' duplicated at most

-- need to make sure I handle lines that *only* have any direction?

-- then merge those

-- first the ones in 'nother_table

drop table if exists tempseg.initial_routelines cascade;
select relation_id,
       direction,
-- st_numGeometries(st_linemerge(st_collect(linestring)))
 st_linemerge(st_collect(linestring))
 as linestring
into tempseg.initial_routelines
from (
 select rdg.relation_id,
       rdg.direction,
       rdg.linestring,
       rdg.sequence_id
 from tempseg.relation_direction_geometries rdg
 where rdg.direction != 'any'
 UNION
 select n.relation_id,
       n.direction,
       n.linestring,
       n.sequence_id
 from tempseg.nother_table n
) q group by relation_id,direction;

--  now add the 'any' direction ones
insert into tempseg.initial_routelines
select relation_id,
       'both' as direction,
-- st_numGeometries(st_linemerge(st_collect(linestring)))
 st_linemerge(st_collect(linestring))
 as linestring
from (
 select rdg.relation_id,
       rdg.direction,
       rdg.linestring,
       rdg.sequence_id
 from tempseg.relation_direction_geometries rdg
 where rdg.direction = 'any' and
       rdg.relation_id not in (select distinct relation_id from tempseg.nother_table)
) a
group by relation_id, direction;

-- okay, now clean up those multilines


drop table if exists tempseg.route_relations_split_lines cascade;
CREATE TABLE  tempseg.route_relations_split_lines (
  relation_id bigint not null,
  direction text,
  id4 serial primary key,
  segment_id int not null
);
SELECT AddGeometryColumn( 'tempseg','route_relations_split_lines', 'routeline', 4326, 'LINESTRING', 2);

-- truncate tempseg.route_relations_split_lines;
insert into tempseg.route_relations_split_lines
 (relation_id,direction,segment_id,routeline)
SELECT
  relation_id,direction,generate_series(1,(Select ST_NumGeometries(linestring))*2,2) as segment_id,
ST_GeometryN(linestring, generate_series(1, ST_NumGeometries(linestring))) AS routeline
FROM tempseg.initial_routelines
where ST_NumGeometries(linestring) is not null
-- and  relation_id = 70350
order by relation_id,direction
;

--  select distinct relation_id from tempseg.route_relations_split_lines order by relation_id;
--  relation_id
-- -------------
-- --        70350
-- --       74660
-- -- skipping       74885
-- --       76279

-- I did those above 4 on OSM, and all of them were off ramps attached
-- to the main relation, or wrong way segments.  So try this...just
-- drop all short segments

-- select relation_id,direction,segment_id,
--  st_length_Spheroid(
--      routeline,
--      'SPHEROID["GRS_1980",6378137,298.257222101]') as llength
-- from tempseg.route_relations_split_lines
-- where relation_id in  (70350, 74660, 74885, 76279)
-- order by relation_id,direction,llength;

-- so, first try getting rid of lines under 500 meters

drop table if exists tempseg.fixed_relations;

select relation_id,direction
into tempseg.fixed_relations
from (
select GeometryType(st_linemerge(st_collect(routeline))) as geomtype,relation_id,direction from tempseg.route_relations_split_lines
group by relation_id,direction
order by geomtype
)a where geomtype = 'LINESTRING';

-- manually ratchet up the length from 100 to 1000, I guess


delete from tempseg.route_relations_split_lines
where
(st_length_Spheroid(
     routeline,
     'SPHEROID["GRS_1980",6378137,298.257222101]')) < 200
;

insert into tempseg.fixed_relations (relation_id,direction)
select relation_id,direction
from (
  select GeometryType(
          st_linemerge(st_collect(routeline))) as geomtype,
         relation_id,direction
  from tempseg.route_relations_split_lines
  left outer join tempseg.fixed_relations q using(relation_id,direction) where q.relation_id is null
  group by relation_id,direction
  order by geomtype
)a where geomtype = 'LINESTRING';

delete from tempseg.route_relations_split_lines
where
(st_length_Spheroid(
     routeline,
     'SPHEROID["GRS_1980",6378137,298.257222101]')) < 400
;

insert into tempseg.fixed_relations (relation_id,direction)
select relation_id,direction
from (
  select GeometryType(
          st_linemerge(st_collect(routeline))) as geomtype,
         relation_id,direction
  from tempseg.route_relations_split_lines
  left outer join tempseg.fixed_relations q using(relation_id,direction) where q.relation_id is null
  group by relation_id,direction
  order by geomtype
)a where geomtype = 'LINESTRING';

delete from tempseg.route_relations_split_lines
where
(st_length_Spheroid(
     routeline,
     'SPHEROID["GRS_1980",6378137,298.257222101]')) < 600
;

insert into tempseg.fixed_relations (relation_id,direction)
select relation_id,direction
from (
  select GeometryType(
          st_linemerge(st_collect(routeline))) as geomtype,
         relation_id,direction
  from tempseg.route_relations_split_lines
  left outer join tempseg.fixed_relations q using(relation_id,direction) where q.relation_id is null
  group by relation_id,direction
  order by geomtype
)a where geomtype = 'LINESTRING';

delete from tempseg.route_relations_split_lines
where
(st_length_Spheroid(
     routeline,
     'SPHEROID["GRS_1980",6378137,298.257222101]')) < 800
;

insert into tempseg.fixed_relations (relation_id,direction)
select relation_id,direction
from (
  select GeometryType(
          st_linemerge(st_collect(routeline))) as geomtype,
         relation_id,direction
  from tempseg.route_relations_split_lines
  left outer join tempseg.fixed_relations q using(relation_id,direction) where q.relation_id is null
  group by relation_id,direction
  order by geomtype
)a where geomtype = 'LINESTRING';

-- select GeometryType(st_linemerge(st_collect(routeline))) as geomtype,relation_id,direction from tempseg.route_relations_split_lines
-- left outer join tempseg.fixed_relations q using(relation_id,direction) where q.relation_id is null
-- and id4 not in
-- ( select id4
--   from tempseg.route_relations_split_lines
--   where  (st_length_Spheroid(
--      routeline,
--      'SPHEROID["GRS_1980",6378137,298.257222101]')) < 900
-- )
-- group by relation_id,direction
-- order by geomtype;


-- okay, that has pretty much run out of its usefulness.  Now I need
-- to insert segments as needed.  My thought is for those geoms with
-- two or three parts, make segments to join the parts, if that
-- segment is less than 1km

-- how?

-- select geometry 1 of N, last point, join with first point, geometry 2 of N

-- so make a new temp table

drop table if exists tempseg.more_split_lines cascade;
CREATE TABLE  tempseg.more_split_lines (
  relation_id bigint not null,
  direction text,
  id4 serial primary key,
  fix boolean default false,
  segment_id int not null
);
SELECT AddGeometryColumn( 'tempseg','more_split_lines', 'routeline', 4326, 'LINESTRING', 2);


insert into tempseg.more_split_lines
 (relation_id,direction,segment_id,routeline)
SELECT
  relation_id,direction,generate_series(1,(Select ST_NumGeometries(linestring))*2,2) as segment_id,
ST_GeometryN(linestring, generate_series(1, ST_NumGeometries(linestring))) AS routeline
from (
  select st_linemerge(st_collect(routeline)) as linestring,
         relation_id,direction
  from tempseg.route_relations_split_lines
  left outer join tempseg.fixed_relations q using(relation_id,direction)
  where q.relation_id is null
  group by relation_id,direction
) b
where ST_NumGeometries(linestring) is not null;



-- self join, construct joining segments

insert into tempseg.more_split_lines
  (relation_id, direction, segment_id, fix,routeline)
  select rl1.relation_id, rl1.direction,rl1.segment_id + 1 as segment_id,
       true as fix,
       st_makeline(
             st_endpoint(rl1.routeline),
             st_startpoint(rl2.routeline)
       ) as routeline
  from tempseg.more_split_lines rl1
  join tempseg.more_split_lines rl2
       on (rl1.relation_id=rl2.relation_id
           and rl1.direction = rl2.direction
           and rl1.segment_id + 2 = rl2.segment_id
          )
  where not ST_equals(st_endpoint(rl1.routeline),
             st_startpoint(rl2.routeline))
  and ST_Distance_Sphere(st_endpoint(rl1.routeline),
             st_startpoint(rl2.routeline)) < 1000
;

-- select  GeometryType(st_linemerge(st_collect(routeline))) as geomtype,
--        relation_id,direction
-- from tempseg.more_split_lines
-- group by relation_id,direction
-- order by geomtype;

-- Okay, that is as good as I can get for now

drop table if exists tempseg.revised_route_lines cascade;
CREATE TABLE  tempseg.revised_route_lines (
  relation_id bigint not null,
  direction text,
  id4 serial primary key,
  unique (relation_id,direction)
);
SELECT AddGeometryColumn( 'tempseg','revised_route_lines', 'routeline', 4326, 'GEOMETRY', 2);


insert into tempseg.revised_route_lines
(routeline,relation_id,direction)
select  st_linemerge(st_collect(routeline)),
       relation_id,direction
from tempseg.more_split_lines
group by relation_id,direction;

insert into tempseg.revised_route_lines
(routeline,relation_id,direction)
select st_linemerge(st_collect(routeline)),
       relation_id,direction
from tempseg.route_relations_split_lines
join tempseg.fixed_relations q using(relation_id,direction)
group by relation_id,direction;


insert into tempseg.revised_route_lines
(routeline,relation_id,direction)
select linestring,relation_id,direction
from tempseg.initial_routelines
where geometrytype(linestring)='LINESTRING';

-- Deploy revised_route_lines:rsl to pg
-- requires: more_splits
-- requires: fixed_relations
-- requires: split_lines

BEGIN;

CREATE TABLE  tempseg.revised_route_lines (
  relation_id bigint not null,
  direction text,
  id4 serial primary key,
  unique (relation_id,direction)
);
SELECT AddGeometryColumn( 'tempseg','revised_route_lines', 'routeline',
                          4326, 'GEOMETRY', 2);

INSERT INTO tempseg.revised_route_lines
            (routeline,relation_id,direction)
SELECT
        ST_linemerge(ST_collect(routeline)),
        relation_id,direction
FROM tempseg.more_split_lines
GROUP BY relation_id,direction;

INSERT INTO tempseg.revised_route_lines
            (routeline,relation_id,direction)
SELECT ST_linemerge(ST_collect(routeline)),
       relation_id,direction
FROM tempseg.route_relations_split_lines
JOIN tempseg.fixed_relations q USING(relation_id,direction)
GROUP BY relation_id,direction;


INSERT INTO tempseg.revised_route_lines
            (routeline,relation_id,direction)
SELECT linestring,relation_id,direction
FROM tempseg.initial_routelines
LEFT OUTER JOIN tempseg.revised_route_lines rrl using(relation_id,direction)
WHERE geometrytype(linestring)='LINESTRING'
AND rrl.relation_id IS NULL;

COMMIT;

PREPARE tallyup AS
   SELECT count(*)::integer
   FROM tempseg.route_relations_split_lines;

PREPARE this_or_that AS
   SELECT CASE
            WHEN exists(SELECT tablename
                        FROM pg_tables
                        WHERE schemaname='tempseg'
                          AND tablename='fixed_relations')
            THEN 1884
            ELSE 2799
            END;

SELECT results_eq('tallyup', 'this_or_that',
                  'The data got loaded okay');