[postgis-users] Speeding up point-in-polygon search using ST_SimplifyPreserveTopology?

[Stephen Woodbridge]

Evan,

Here is the old post about tiling polygons. There may be others also.

0Steve

On 3/3/2011 7:03 PM, Arnold Helmut Engelmann wrote:
> If you want to go the tiling route and want to try that: Create a layer of tiles (boxes) at the size you want. Then run the following query:
>
>              CREATE TABLE
>                 tiled_geo_units as
>              SELECT
>                 ST_Intersection(a.geometry, b.geometry) AS geometry,
>                 a.*,
>                 b.*
>              FROM
>                 geo_units as a,
>                 tiles as b
>              WHERE
>                 ST_Intersects(a.geometry, b.geometry)
>
> Where "geo_units" is the table with your geological units, and "tiles" is the table of boxes you created. This will intersect both layers and split the polygons at the tile boundaries. You can also add to the WHERE clause if you want to filter out just the "large" polygons, otherwise this will tile all your polygons. For this ST_NPoints will probably useful.
>
> Arnold
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Peter N. Schweitzer
> Sent: Thursday, March 03, 2011 13:21
> To: pcreso at pcreso.com
> Cc: PostGIS Users Discussion
> Subject: Re: [postgis-users] A way to split polygons?
>
> On 03/03/2011 04:00 PM, pcreso at pcreso.com wrote:
>> It sounds like you are wanting to tile your polygons. Not really ideal, as each polygon is rendered via tiles, so you need to render them without borders to hide the tiles, then often plot the border as well, which still has all the vertices. Lots more work.
>
> Hmmm.  But in my KML, I'm not drawing the boundaries, so perhaps that disadvantage doesn't cause trouble in this situation?
>
>> You might look at simplifying your polygons to reduce the number of vertices in each. If you manage your data topologically, this process will work better at retaining shared boundaries.
>
> Mike Toews has suggested ST_Simplify or ST_SimplifyPreserveTopology, but I'm unsure of what value to use for the tolerance.  The CRS is geographic, not projected.
>
>> If you can use Google to provide vector zoom layers, then as you zoom
>> in you can get less&  less simplified versions... just like pyramid'ed
>> rasters. Zoomed out you can't see so don't provide the unnecessary detail.
>
> Use Google?  I don't understand--is there some service they provide which might be used for data like these?  Or are you referring to a way to describe the data in KML that I'm not familiar with?  Either is plausible--I'm not being snarky here!  I'm just writing KML with a PHP script that queries the PostgreSQL db, and have used these technologies in only simple ways so far.  So when I say "Google" I mean only that the people who use these data typically open them with Google Earth, at least, those who experience this problem report it so.
>
> 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/>  _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users