[postgis-users] A way to split polygons?

[Peter N. Schweitzer]

Just wanted to report to the list that I seem to have successfully
applied the tiling idea to these geologic map units, by creating a
separate table containing only those polygons where ST_NPoints(the_geom) > 10000,
a table containing tiles that are 1/7th of a degree square, and
a third table containing the intersection of the complex polygons
with the tiles.  By marking, in the original polygon table, those
polygons for which I have tiled versions, I am now able to generate
KML that is simple enough for Google Earth to display.

I'm happy about this because this is a problem that has been
following me for several years now--every once in a while I
would receive email from a user inquiring about polygons that
weren't displaying properly in Google Earth.  A proper understanding
and application of PostGIS's ST_Intersects and ST_Intersection
functions, coupled with some bookkeeping, has made this solution
possible.

So for any who are interested, the end result is the collection of
KML files linked from http://tin.er.usgs.gov/geology/state/kml/
or from individual pages describing the state geologic map compilation,
from http://tin.er.usgs.gov/geology/state/

There remains to be explored the worthwhile suggestion of generating
lower-resolution versions of these data for use with KML regions,
to speed the display within Google Earth.  For now, however, these
data seem (to me) to render readily enough.

Thanks to those from this list who offered suggestions towards solving
this problem.

Peter

On 03/03/2011 03:44 PM, Peter N. Schweitzer wrote:
> At http://tin.er.usgs.gov/geology/state/ and pages below it, I provide
> geologic map units for US states in KML for use within Google Earth.
> These are stored in PostgreSQL (9.0.2) with PostGIS (1.5.2). My problem
> is that many of the larger geologic units are represented as polygons that
> have a large number of vertices, often with many interior rings. Google Earth
> doesn't want to display polygons that have 100,000 vertices (I think the
> actual limit is lower, but I don't know what it is.) So my geologic maps
> have large irregular holes in them when viewed in Google Earth.
>
> Is there a way, using PostGIS functions, to split these polygons so that
> the resulting polygons have fewer vertices and thus would appear as expected
> in Google Earth? It seems to me one possibility would be to intersect
> these large polygons with Box2D's whose vertical dimensions were, say,
> one or two degrees. But I don't yet see how to carry this operation out.
>
> Can anyone offer suggestions as to how this might be done?
>
> Here are some of the characteristics of the data. The principal table
> containing the polygons is named geol_poly; the field unit_link is a
> relational key to other, more interesting data, and the_geom comes through
> shp2pgsql.
>
> select unit_link,ST_NPoints(the_geom) npt,ST_NumGeometries(the_geom) ng, ST_NumInteriorRings(the_geom) ni from geol_poly order by npt desc;
> unit_link | npt | ng | ni
> -----------------+--------+----+------
> FLwater;0 | 451759 | 1 | 8945
> PAPAa;6 | 319192 | 1 | 1235
> PAPAcg;6 | 216653 | 1 | 925
> ALwater;0 | 205251 | 1 | 108
> OHPAc;0 | 201310 | 1 | 842
> OHPAm;0 | 200763 | 1 | 895
> OHPAap;0 | 196422 | 1 | 1207
> MNOl;0 | 188753 | 1 | 107
> MNCu;0 | 165061 | 1 | 86
> TNObh;6 | 152421 | 1 | 825
> PAPAp;6 | 144889 | 1 | 324
> MOOjc;0 | 144721 | 1 | 144
> MOMk;0 | 128731 | 1 | 290
> WVPAk;0 | 128402 | 1 | 934
> TXKed;0 | 124666 | 1 | 720
> TNMfp;10 | 122021 | 1 | 117
> OHMlc;0 | 116993 | 1 | 731
> TNMfp;10 | 113034 | 1 | 406
> MOOr;0 | 109195 | 1 | 136
> PAPAm;6 | 105906 | 1 | 395
> PAPAcc;6 | 105798 | 1 | 447
> WVPAm;0 | 99944 | 1 | 474
> CAQ;0 | 97086 | 1 | 455
> WVPAc;0 | 94277 | 1 | 455
> MNOm;0 | 93054 | 1 | 53
> PAPPAw;0 | 89974 | 1 | 218
> TNOca;6 | 89292 | 1 | 564
> WVPAa;0 | 89182 | 1 | 504
> MOOr;0 | 87103 | 1 | 171
> MOOg;0 | 85660 | 1 | 277
>
> Peter


-- 
Peter N. Schweitzer (MS 954, U.S. Geological Survey, Reston, VA 20192)
(703) 648-6533  FAX: (703) 648-6252  email: pschweitzer at usgs.gov
<http://geology.usgs.gov/peter/>