Populating a table that uses a different geometry from another table of a different geometry

Question

Does anyone know how I can populate a table that uses a different geometry from another table of a different geometry? (example geometry(points) -> geometry(Linestring) or geometry(polygon)
I'm new to SQL.
This is what the source table would look like: (note that this table has type, because in column type it it is either road or district)
CREATE TABLE points(
name text, 
type text, 
geom geometry(POINT));

This is what I'm putting data from that table into a table that looks like this:
CREATE table road(
id serial PRIMARY KEY, 
name text, 
geom geometry(LINESTRING));

I'm having trouble with this as I am required to input from points into road or district and then union then afterwards in another query. so far I've tried several things, but since I'm very new to SQL I'm not entirely sure what I am actually doing This is my code:
INSERT INTO district(name, geom) SELECT (name, geom) FROM points WHERE type =  'district';
INSERT INTO district SELECT * FROM points WHERE type = 'district';
INSERT INTO district SELECT name, points.geom FROM points where points.type = 'road';

so far I've gotten an assortment of errors, mainly saying:
ERROR: column "geom" does not exist

or
ERROR duplicate key value violates unique constraint "road_pkey"
DETAIL: key(id) - (Snowflake Lane) already exists.
SQL state: 23505

currently, the only way I've gotten it to work is if I manually put in all the roads and districts but I'm not supposed to do it that way since I need to UNION combine: List the name and geometry for place and etc.
I've been stuck on this part for days and still, haven't figured it out yet...

geozelot · Answer

Your points table is missing a crucial piece of information: the identifier of the sequential order of points that make up each of the respective higher dimensional geometry (i.e. within the LineString or Polygon). Without knowing the order of points that make up e.g. a road, you can only assemble them in random order! Note that, to construct a Polygon from points directly (in contrast to constructing a minimum enclosing area, e.g. convex hull), you'd need to create a (closed) LineString and then polygonize it.

Assuming your point table had a column seq_id that denotes the order of points within the desired road geometry, a Point aggregation into LineStrings could look like this:
INSERT INTO roads (name, geom) (
  SELECT name, ST_MakeLine(geom ORDER BY seq_id)
  FROM   points
  WHERE  type = 'road'
  GROUP BY
         name
);

and, given the same seq_id, an aggregation of Polygons into districts like this:
INSERT INTO districts (name, geom) (
  SELECT name, ST_MakePolygon(ST_AddPoint(geom, ST_StartPoint(geom)))
  FROM   (
    SELECT name, ST_MakeLine(geom ORDER BY seq_id)
    FROM   points
    WHERE  type = 'district'
    GROUP BY
           name
  ) q
);

where you need to add the ST_StartPoint of each LineString to itself to create a closed ring.

Note that both error messages do not relate to your example table roads, i.e. your PK column (denoted by roads_pkey, which may or may not be the PK, though) is of type SERIAL, but the error mentions a TEXT value; either you have a different PK, or a UNIQUE constraint on roads.name called roads_pkey.
It would help if you posted an example representative of your real life use case to debug those errors.
However, with the aggregation based on GROUP BY name, as in above queries, you will likely not receive a duplication error.

Populating a table that uses a different geometry from another table of a different geometry

One Answer

Add your own answers!

Ask a Question