Write spatial operations to PostGIS from QGIS or ArcGIS

Question

I need to write my spatial operations to a (new) postgis layer (as the data is to large for a shp file). Can this be done in the QGIS browser or from ArcGIS without SDE?

When you go (for example) --> vector--geoprocessing--clip or geometry--multipart to singlepart you can only save to a shp format eventhough the input data is from postgis.

I have tried the postgis manager and you can edit tables, import/export data but I can't see any way of writing the data to postgis directly.

I have qgis lisboa and postgis 2.5 as well as arcgis advanced (10.1) but not ArcSDE. If necessary a python solution is fine (maybe using @dimitris response in Writing a plugin for Qgis using python to access PostGIS and write geometries

=== POSTGIS Attempt

I am new to Postgis. So as a test I have run the following from http://postgis.net/docs/ST_Dump.html

SELECT (ST_Dump("LowRez_HighHz".the_geom)).geom AS the_geom

FROM 
  public."LowRez_HighHz";

It runs with the following output

Total query runtime: 68839 ms. 47591 rows retrieved.

and then I get an output but has it really removed all the multiparts? I doesn't look like it's writing.

So when I add the following

-- Break a compound curve into its constituent linestrings and circularstrings
SELECT ST_AsEWKT(a.geom), ST_HasArc(a.geom)
  FROM ( SELECT (ST_Dump(p_geom)).geom AS geom
         FROM (SELECT ST_GeomFromEWKT('COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))') AS p_geom) AS b
        ) AS a;
          st_asewkt          | st_hasarc
-----------------------------+----------
 CIRCULARSTRING(0 0,1 1,1 0) | t
 LINESTRING(1 0,0 1)         | f

I get

ERROR: syntax error at or near "st_asewkt" LINE 11:
st_asewkt | st_hasarc ^

********** Error **********

ERROR: syntax error at or near "st_asewkt" SQL state: 42601

How do I correct this?

The table details are

FROM http://www.bostongis.com/postgis_dump.snippet I also tried the following

SELECT "LowRez_HighHz", ST_Multi(ST_Collect(f.the_geom)) as singlegeom FROM (SELECT "LowRez_HighHz", (ST_Dump(the_geom)).geom As the_geom FROM "LowRez_HighHz" ) As f GROUP BY "LowRez_HighHz"

and I get

ERROR: out of memory DETAIL: Failed on request of size 15183219.

********** Error **********

ERROR: out of memory SQL state: 53200 Detail: Failed on request of size 15183219.

I have 16GB RAM and 1TB free space...what's happening?

=== Testing Micha's input

I can't seem to run the code in one go. I get

NOTICE: CREATE TABLE will create implicit sequence "lowrez_highhz_single_gid_seq" for serial column "lowrez_highhz_single.gid" ERROR: Table 'LowRez_HighHz_single' does not occur in the search_path CONTEXT: SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 )" PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement

But when I run

-- Create a new table
CREATE TABLE LowRez_HighHz_single (gid serial, Shape_Leng numeric, Shape_Area numeric);

It creates it as

NOTICE: CREATE TABLE will create implicit sequence "lowrez_highhz_single_gid_seq" for serial column "lowrez_highhz_single.gid" Query returned successfully with no result in 60 ms.

When I then comment out the first line and add

-- Make it a spatial table
SELECT AddGeometryColumn('LowRez_HighHz_single','geom',4283,'POLYGON',2);

I get

ERROR: Table 'LowRez_HighHz_single' does not occur in the search_path CONTEXT: SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 )" PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement

What am I doing wrong? I also tried public."LowRez_HighHz_single"

=== Update on using all lower case.

I tried it but fails...might be because the table has mixed case currently. the error code is

ERROR: relation "lowrez_highhz" does not exist SQL state: 42P01 Character: 327

I also tried it on a dataset that is already all lowercase.

Answer 1

Wait a sec. How did we get into CIRCULARSTRINGs? If I understand correctly you have a MULTIPOLYGON layer, LowRez_HighHz, which you want to break up (multipart to single part) into POLYGONs. That's what ST_Dump can do.

-- Create a new table
CREATE TABLE LowRez_HIghHz_single (gid serial, Shape_Leng numeric, Shape_Area numeric);
-- Make it a spatial table
SELECT AddGeometryColumn('lowrez_highhz_single','geom',4283,'POLYGON',2);
-- Use ST_Dump to insert new geometries
INSERT INTO lowrez_highhz_single(geom) SELECT (ST_Dump(lh.geom)).geom FROM lowrez_highhz AS lh;

As for the "Out of Memory" error, I can't seem to understand what you're trying to do. It seems you are breaking up the MULTI features and recombining them into a collection in one select??