How to export geometry as a table in QGIS

Question

I am trying to use Qgis to produce the input file for SWMM and the first step is to produce the geometry tables. I need to export the geometry of elements as a tables with these format:

(poygons)

;;Subcatchment   X-Coord            Y-Coord           
;;-------------- ------------------ ------------------
Area1            134880.644         6495607.728       
Area1            135200.867         6495595.252       
Area1            134859.850         6495577.785       
Area1            134864.009         6495608.560       
Area2            135202.531         6495594.420       
Area2            135117.692         6495685.081       
Area2            134870.663         6495665.119       
Area2            134863.177         6495608.560       

(lines)

;;Link           X-Coord            Y-Coord           
;;-------------- ------------------ ------------------
Link3            134941.633         6495830.023       
Link3            134953.814         6495860.476       

(points)

;;Node           X-Coord            Y-Coord           
;;-------------- ------------------ ------------------
Node1            134866.732         6495657.169       
Node2            134918.668         6495651.752       
Node3            134919.624         6495674.375       
Node4            134933.644         6495678.517       
Node5            135002.538         6495870.626       
Outfall1         134890.879         6496098.005       

So far, I have tried the MMQIS, but I do not know how to tailor the output:

"shapeid","x","y"
"0","134956.853261","6495850.73098"
"0","134951.891304","6495838.10054"
"0","134944.222826","6495660.82337"
"0","135027.44837","6495695.78261"
"0","135001.223092","6495794.37799"
"0","135013.690217","6495854.33967"
"0","135010.081522","6495867.4212"
"0","134956.853261","6495850.73098"

My guess is that a set of SQL sentences could do the work (I saw https://gis.stackexchange.com/a/10113/36393), but I have no clue how to start them and what I need.

The elements are stored in separate files (GeoPackage or shape), I do not care about the attribute table (now) and I think I will be able to use a database. I have PostGIS (I am very unexperienced on it), but maybe it is easier to work with a SpatiaLite file.

Answer 1

It´s a bit puzzling to me why any software would require a fixed space delimited format as input...but anyway, AFAIK within QGIS your options are limited since both 'Save as...' -> CSV and the MMQGIS have their backdraws for your requirements.

PostgreSQL's COPY (or /copy if your DB rights are limited) does give you more flexibility in constructing the table structure, albeit still with no fixed space delimiter:

COPY (
    SELECT concat('Area', sub.id::text) AS "Subcatchment",
           ST_X(sub.geom) AS "X-Coord",
           ST_Y(sub.geom) AS "Y-Coord"
    FROM (
        SELECT ROW_NUMBER() OVER() AS id,
               (ST_DumpPoints(geom)).geom AS geom
        FROM <your_area_table>
    ) AS sub
) TO 'path/to/file.csv' DELIMITER ' ' CSV HEADER;

This is the example query for your area table, for the lines just replace 'Subcatchment' and 'Area' with 'Link' and refer to your line table (<your_area_table> in above query).

For your Points, use:

COPY (
    SELECT concat('Node', sub.id::text) AS "Node",
           ST_X(sub.geom) AS "X-Coord",
           ST_Y(sub.geom) AS "Y-Coord"
    FROM (
        SELECT ROW_NUMBER() OVER() AS id,
               geom
        FROM <your_point_table>
    ) AS sub
) TO 'path/to/file.csv' DELIMITER ' ' CSV HEADER;

This gives you a CSV each, delimited by one space and without quotes (this is dependant on a few things, but should be true for your case). Note that your geometry column is called the kind-of standard 'geom' in above queries, so name yours accordingly.

Answer 2

You can do it using SQL in PostGIS. You would get the points from your polygons/lines by "dumping" them from the source, then you print the X and Y components. The trick is to dump the points in a query and extract the X and Y in another, so you are 1) sure that X and Y match and 2) you don't have to dump the points twice. Using fake data, it would be:

with src as 
   (select 1 as id, 
    st_geomfromtext('polygon((1 1, 2 1, 2 2, 1 2, 1 1))') as geom
   UNION 
    select 2 as id, 
    st_geomfromtext('polygon((10 10, 20 10, 20 20, 10 20, 10 10))') as geom),
dumppts as 
  (select id, 
  (st_dumppoints(geom)).geom from src)
select id, 
       st_x(geom), st_y(geom) from dumppts;

 id | st_x | st_y
----+------+------
  1 |    1 |    1
  1 |    2 |    1
  1 |    2 |    2
  1 |    1 |    2
  1 |    1 |    1
  2 |   10 |   10
  2 |   20 |   10
  2 |   20 |   20
  2 |   10 |   20
  2 |   10 |   10
(10 rows)

Using real data it would be similar to

 with dumppts as 
      (select Subcatchment, 
      (st_dumppoints(geom)).geom from myAreaLayer)
 select Subcatchment, 
        st_x(geom) as "X-Coord", 
        st_y(geom) as "Y-Coord" from dumppts;