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
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...