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


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

enter image description here

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. enter image description here 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. enter image description here

  • If data is too large for a shapefile (or for many other reasons) I usually write output to a file geodatabase. If PostGIS is not a strict requirement maybe this workaround would work. – PolyGeo Aug 7 '12 at 3:40
  • I do this to when using arcgis but qgis can't write to a fgdb either. I want to use postgis as in arcgis I have had many issues with even fgdb's once they got over 1GB and have had to split them into multiple parts (and then rejoin them) to process them effectively and am hoping that reading/writing to Postgis will eliminate some of these issues. – GeorgeC Aug 7 '12 at 4:14
  • You could just move the original shapefiles into PostGIS and do the processing there. PostGIS has i.e. ST_Intersection for "clipping" and ST_Dump for multi to single part. – Micha Aug 7 '12 at 13:32
  • thanks @Micha I tried ST_Dump but it is giving me some errors. Please see my updated Question. – GeorgeC Aug 8 '12 at 5:32
  • Loose all the CapitalLetters in the table name. Try: AddGeometyrColumn('lowrez_highhz_single',...). Sorry for the confusion. – Micha Aug 9 '12 at 6:36

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

| improve this answer | |
  • -thanks for some clearly documented test code. I had some issues with it, please see the updated question. I didn't understand what the code from the postgis/bostongis support site did exactly as it wasn't clearly documented and I am a postgis baby. – GeorgeC Aug 9 '12 at 1:12
  • Loose all the CapitalLetters in the table name. Try: AddGeometryColumn('lowrez_highhz_single',...). Sorry for the confusion. I also put these changes into the answer above. – Micha Aug 9 '12 at 6:36
  • -no luck. It still doesn't work. I have updated the question. best. – GeorgeC Aug 9 '12 at 6:58
  • Two things to check: Can you drop that table and recreate (re-import) the shapefile, but using only lower case from the start? Also make sure that the column named 'geom' is created (after AddGeometryColumn() ) and check what is in the geometry_columns table. i.e. SELECT * FROM geometry_columns. Be sure that the table is there with the correct geometry column name. – Micha Aug 10 '12 at 10:33

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.