[26899] | 1 | -------------------------------------------------------------------------------
|
---|
| 2 | -- The following script creates a new table for the pgsql simple schema for
|
---|
| 3 | -- storing full way geometries.
|
---|
| 4 | --
|
---|
| 5 | -- Author: Ralf
|
---|
| 6 | -------------------------------------------------------------------------------
|
---|
| 7 |
|
---|
| 8 |
|
---|
| 9 | -- drop table if it exists
|
---|
| 10 | DROP TABLE IF EXISTS way_geometry;
|
---|
| 11 |
|
---|
| 12 | -- create table
|
---|
| 13 | CREATE TABLE way_geometry(
|
---|
| 14 | way_id bigint NOT NULL
|
---|
| 15 | );
|
---|
| 16 | -- add PostGIS geometry column
|
---|
| 17 | SELECT AddGeometryColumn('', 'way_geometry', 'geom', 4326, 'GEOMETRY', 2);
|
---|
| 18 |
|
---|
| 19 |
|
---|
| 20 |
|
---|
| 21 | -------------------------------------------------------------------------------
|
---|
| 22 | -- the following might go into the POST_LOAD_SQL-array in the class "PostgreSqlWriter"??
|
---|
| 23 | -------------------------------------------------------------------------------
|
---|
| 24 |
|
---|
| 25 | -- add a linestring for every way (create a polyline)
|
---|
| 26 | INSERT INTO way_geometry select id, ( select ST_LineFromMultiPoint( Collect(nodes.geom) ) from nodes
|
---|
| 27 | left join way_nodes on nodes.id=way_nodes.node_id where way_nodes.way_id=ways.id ) FROM ways;
|
---|
| 28 |
|
---|
| 29 | -- after creating a line for every way (polyline), we want closed ways to be stored as polygones.
|
---|
| 30 | -- So we need to delete the previously created polylines for these ways first.
|
---|
| 31 | DELETE FROM way_geometry WHERE way_id IN
|
---|
| 32 | ( SELECT ways.id FROM ways
|
---|
| 33 | WHERE ST_IsClosed( (SELECT ST_LineFromMultiPoint( Collect(n.geom) ) FROM nodes n LEFT JOIN way_nodes wn ON n.id=wn.node_id WHERE ways.id=wn.way_id) )
|
---|
| 34 | AND ST_NumPoints( (SELECT ST_LineFromMultiPoint( Collect(n.geom) ) FROM nodes n LEFT JOIN way_nodes wn ON n.id=wn.node_id WHERE ways.id=wn.way_id) ) >= 3
|
---|
| 35 | )
|
---|
| 36 | ;
|
---|
| 37 |
|
---|
| 38 | -- now we need to add the polyline geometry for every closed way
|
---|
| 39 | INSERT INTO way_geometry SELECT ways.id,
|
---|
| 40 | ( SELECT ST_MakePolygon( ST_LineFromMultiPoint(Collect(nodes.geom)) ) FROM nodes
|
---|
| 41 | LEFT JOIN way_nodes ON nodes.id=way_nodes.node_id WHERE way_nodes.way_id=ways.id
|
---|
| 42 | )
|
---|
| 43 | FROM ways
|
---|
| 44 | WHERE ST_IsClosed( (SELECT ST_LineFromMultiPoint( Collect(n.geom) ) FROM nodes n LEFT JOIN way_nodes wn ON n.id=wn.node_id WHERE ways.id=wn.way_id) )
|
---|
| 45 | AND ST_NumPoints( (SELECT ST_LineFromMultiPoint( Collect(n.geom) ) FROM nodes n LEFT JOIN way_nodes wn ON n.id=wn.node_id WHERE ways.id=wn.way_id) ) >= 3
|
---|
| 46 | ;
|
---|
| 47 | -------------------------------------------------------------------------------
|
---|
| 48 |
|
---|
| 49 | -- create index on way_geometry
|
---|
| 50 | CREATE INDEX idx_way_geometry_way_id ON way_geometry USING btree (way_id);
|
---|
| 51 | CREATE INDEX idx_way_geometry_geom ON way_geometry USING gist (geom);
|
---|