Changes between Version 5 and Version 6 of UsersWikiExamplesOverlayTables


Ignore:
Timestamp:
Oct 14, 2011 2:56:29 AM (8 years ago)
Author:
chodgson
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • UsersWikiExamplesOverlayTables

    v5 v6  
    105105
    106106[[Image(overlay2.png)]]
     107
     108'''Chris Hodgson adds:'''
     109
     110Here is a basic SQL function which implements Kevin's multi-step approach in a single query, accessible by one function call:
     111
     112{{{
     113CREATE OR REPLACE FUNCTION my_overlay(table1 varchar, col1 varchar, table2 varchar, col2 varchar, result_table varchar) returns void AS
     114$$
     115BEGIN
     116EXECUTE 'CREATE TABLE ' || result_table || ' AS '
     117|| 'WITH all_lines AS ( '
     118        || 'SELECT St_ExteriorRing(st_geometryn(the_geom,1)) AS the_geom '
     119        || 'FROM ' || table1
     120        || ' UNION ALL '
     121        || 'SELECT St_ExteriorRing(st_geometryn(the_geom,1)) AS the_geom '
     122        || 'FROM ' || table2
     123|| '), noded_lines AS ( '
     124        || 'SELECT St_Union(the_geom) AS the_geom '
     125        || 'FROM all_lines '
     126|| '), new_polys AS (  '
     127        || 'SELECT geom AS the_geom, ST_PointOnSurface(geom) AS pip '
     128        || 'FROM St_Dump(( '
     129                || 'SELECT St_Polygonize(the_geom) AS the_geom '
     130                || 'FROM noded_lines)) '
     131|| ') '
     132|| 'SELECT a.' || col1 || ' as ' || table1 || '_' || col1
     133|| ', b.' || col2 || ' as ' || table2 || '_' || col2
     134|| ', p.the_geom as the_geom '
     135|| 'FROM new_polys p '
     136|| 'LEFT JOIN ' || table1 || ' a ON St_Within(p.pip, a.the_geom) '
     137|| 'LEFT JOIN ' || table2 || ' b ON St_Within(p.pip, b.the_geom)';
     138END
     139$$ LANGUAGE plpgsql;
     140}}}
     141
     142The function takes the name of two tables, and the names of a column from each of them (the id/primary key is the expected use) and the new table name to be created. I believe this approach will be memory limited because all of the linework, and the resulting polygons, are in memory at once and stored in a single (potentially very large) multi-geometry.