1

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.

  • Your last table looks a lot like a csv, maybe exporting your geometries as such works out. – Erik Jan 23 '18 at 10:24
  • @ErikLohmann it is a csv from MMQGIS, but formating it is very time consuming when you need to do it over and over – Marco Jan 23 '18 at 10:28
  • Meaning, you wish your exported csv to have values separated by tabstops and these lines? – Erik Jan 23 '18 at 10:46
  • Yes, using another key than "shapeid", getting rid of "" and hopefully using fixed space delimited format (no tabs) – Marco Jan 23 '18 at 11:06
2

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.

  • Fixed space delimited format due to an old coding that no one is going to change. The documentation of the input file is in the Appendix D of the User's Manual of SWMM nepis.epa.gov/Exe/ZyPURL.cgi?Dockey=P100N3J6.txt is there is any interest – Marco Jan 23 '18 at 14:20
  • @Marco I don´t know, that still doesn´t explicitly say the input file has to be fixed space delimited. it does say that 'Data items can appear in any column of a line.' and mentions how a certain 'appearence' was created in the project file (which is an output file no?). it does also say that comment lines 'can' be prefixed with a semicolon. ...did you use this format before? in basically all language I know one reads out tabular data from file by it's (single) delimiter, and having to reduce arbitrary amounts of them to one is a little overhead...but that´s just me wondering about things ,) – ThingumaBob Jan 23 '18 at 14:45
  • @Marco if however this is absolutely necessary, consider using e.g. python to get full control over how the file is written. the str.format() function can easily implement fixed padding within lines (check out this blog for examples if you are not familiar). with the psycopg2 module you can query a PostgreSQL/PostGIS DB with above examples (without the COPY ... TO ... of course) and write the returning rows to CSV using the csv module. – ThingumaBob Jan 23 '18 at 15:03
  • @ThingumaBod I have not tried other formats, but I will give them a try tomorrow, tabular or comma (semicolon is for comments) will make life a lot easier. Regarding the python str.format() sounds promising, it is me who is still a bad coder. I will have it in mind if I ever attempt to write a plugin for QGIS – Marco Jan 23 '18 at 21:33
  • Great! First time I execute an SQL sentence in QGIS. Just one more question. Is there a way to select the *inerior' of the line type data? i.e. only the vertex that are not the ends – Marco Jan 23 '18 at 22:18
1

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;
  • Sorry for such silly questions, but in your example, is myAreaLayer the geometry already in a PostGIS database? Which needs an attribute called Subcatchment, isn't it? – Marco Jan 23 '18 at 13:47
  • Yes, the geometries are in Postgres. You can change the Subcatchment for whatever field uniquely identify your features – JGH Jan 23 '18 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.