This question has been asked in before, but for my level of experience I am hoping that someone could explain the solution in greater detail. I am using either PG Admin or QGIS."POSTGIS="2.1.4 r12966" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.9.2" LIBJSON="UNKNOWN" TOPOLOGY RASTER PostgreSQL 9.3.5 on x86_64-apple-darwin14.0.0, compiled by Apple LLVM version 6.0 (...)"
I have a multipolygon table of about 6,000 parcels. I would like to dissolve the boundaries between parcels that have ST_Touches = true and they have the same landuse. I have been unable to write SQL to do this using ST_Union. I tried to use ST_Dump as recommended by yhw42 in response to Underdark's question, but I do not understand how to convert the geometry_dump into something I can visualize in QGIS.
The closest I have gotten is to produce a many to many table of the 5,000 pairs of touching parcels. I made this list unique by controlling the order of the unique id's, where tables A and B are the same:
SELECT least(A.gid,B.gid),greatest(A.gid,B.gid)
I then tried to do the union with either ST_Union or ST_Collect in my approximation of the answer to this question:
SELECT ST_Collect(S.geoma,T.geomb)FROM (SELECT A.geom AS geoma FROM parcels AS A, touching AS B WHERE A.gid = B.least) as S, (SELECT A.geom AS geomb FROM parcels AS A, touching AS B WHERE A.gid = B.greatest) as T;
This query runs, but it runs for more than an hour, which I do not think is realistic for the number of features. I have not tried the performance recommendations listed here yet, as I believe the problem is in my cludgey SQL. Could anyone make a recommendation as to how I can improve this?
أكثر...
I have a multipolygon table of about 6,000 parcels. I would like to dissolve the boundaries between parcels that have ST_Touches = true and they have the same landuse. I have been unable to write SQL to do this using ST_Union. I tried to use ST_Dump as recommended by yhw42 in response to Underdark's question, but I do not understand how to convert the geometry_dump into something I can visualize in QGIS.
The closest I have gotten is to produce a many to many table of the 5,000 pairs of touching parcels. I made this list unique by controlling the order of the unique id's, where tables A and B are the same:
SELECT least(A.gid,B.gid),greatest(A.gid,B.gid)
I then tried to do the union with either ST_Union or ST_Collect in my approximation of the answer to this question:
SELECT ST_Collect(S.geoma,T.geomb)FROM (SELECT A.geom AS geoma FROM parcels AS A, touching AS B WHERE A.gid = B.least) as S, (SELECT A.geom AS geomb FROM parcels AS A, touching AS B WHERE A.gid = B.greatest) as T;
This query runs, but it runs for more than an hour, which I do not think is realistic for the number of features. I have not tried the performance recommendations listed here yet, as I believe the problem is in my cludgey SQL. Could anyone make a recommendation as to how I can improve this?
أكثر...