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