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:
http://www.nws.noaa.gov/geodata/catalog/national/html/us_state.htm
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:
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):
However, some states like New Jersey are exhibiting some of the same problems Florida has using ConcaveHull:
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; tl;dr:
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:
http://workshops.opengeo.org/postgis-intro/tuning.html
Changes I've made to postgresql.conf:
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:
أكثر...
http://www.nws.noaa.gov/geodata/catalog/national/html/us_state.htm
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:
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):
However, some states like New Jersey are exhibiting some of the same problems Florida has using ConcaveHull:
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; tl;dr:
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:
http://workshops.opengeo.org/postgis-intro/tuning.html
Changes I've made to postgresql.conf:
- shared_buffers = 256MB
- work_mem = 16MB
- wal_buffers = 1MB
- checkpoint_segments = 6
- random_page_cost = 2.0
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:
- How can I monitor the resource usage of this process better?
- Am I using the right PostGIS functions to accomplish this goal?
- Is it a matter of turning down the tolerance of ConcaveHull, and increasing system limits?
أكثر...