I am trying to use PostGIS 2.0 on Postgres 8.4 to do some mapping. I am working with the NOAA Shapefiles, specifically their US States & Territories file located here:


The app I'm building needs to do polygon in polygon tests, and point in polygon tests (PiP). The shapefiles as-is are too detailed, and the queries are taking too long. I'm trying to get PostGIS to simplify the complex state shapes, I want to achieve a high quality shrink wrap of each state, so that rivers and bays are included in the states but small coastal islands or other tiny landmasses are ignored . My goal is to have two tables: both the states raw geometries table for high quality output situations, and also a separate states_simple table where I can use pre-compressed geometries to speed PiP tests.

I have finally figured out how to properly implement ST_Concavehull, but I'm running into limits of either the server hardware, or the configuration of postgres.

Here is an image to illustrate the issue I'm running into:

ConcaveHull vs Raw Geometry

Processing "square" states like Oklahoma using ConcaveHull process quickly and without any issue. States that have SOME coastal islands / complexity like Georgia process a little slower, but reasonably fast and come out in a way that will be useful for PiP test (i.e. tight shrink wrap):

ConcaveHull Georgia

However, some states like New Jersey are exhibiting some of the same problems Florida has using ConcaveHull:

ConcaveHull New Jersey

The top and bottom of New Jersey are EXACTLY what I want, but the large part of PA including Philladelphia, and the inclusion of Staten Island basically make this geometry useless for my purposes.

So right now the query I'm using to retrieve these polygons is as follows (Example is FL):

SELECT ST_AsGeoJSON(ST_ConcaveHull(ST_Union(geom),.4)) as geom  FROM states WHERE id = 11;


If I turn down the tolerance from .4 to something like .6, the state comes out like a giant triangle. .4 seems to be the floor limit of ConcaveHull, anything lower than that and queries run for 3+ minutes without returning results.

I've been following this article for tips and steps to increase performance of Postgres and PostGIS:


Changes I've made to postgresql.conf:

  • shared_buffers = 256MB
  • work_mem = 16MB
  • wal_buffers = 1MB
  • checkpoint_segments = 6
  • random_page_cost = 2.0

The server all this is running on is a Turnkey Linux LAPP Server running on an Amazon EC2 Medium M1 Instance (64 bit, 1cpu, 3.75gb RAM)

Am I going about this all wrong? Is there another freely available set of "smoother" CONUS states I can use? I'm theorizing that the relative size (read: huge) of the state geometries is what's gumming up PostGIS/Postgres... just not really sure how to diagnose or monitor that beyond running

ps aux | grep ^ postgres

and watching how long queries take to run.

My main questions:

  1. How can I monitor the resource usage of this process better?
  2. Am I using the right PostGIS functions to accomplish this goal?
  3. Is it a matter of turning down the tolerance of ConcaveHull, and increasing system limits?


ST_SimplifyPreserveTopology in particular is just begging to be used in this situation; taking hi-rez or complex data down to a more manageable level.

You may need to pre-process this data first by calculating areas and throwing away tiny landmasses, but then use the Simplify function with various tolerances until you find one that feels right.

You may find that you don't even need the ConcaveHull function, but if you do, it will definitely run faster on the simplified data.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.