Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

creating template_postgis

http://geospatial.nomad-labs.com/2006/12/24/postgis-template-database/

sliver problems - part 1

This idiom can help filter out slivers based on size.
This query selects the features that intersect,
it also creates an intersection of those features,
and returns the resultset.

This can be used to detect and filter out slivers.

select
	zd.zoning_sim as zone,
	st_area(st_intersection(cl.the_geom, zd.the_geom)) as area
from
	v_citylots cl,
	planning.zoning_districts zd
where cl.blklot = '3514003'
and st_intersects(cl.the_geom, zd.the_geom)
order by st_area(st_intersection(cl.the_geom, zd.the_geom)) desc

sliver problems - part 2

If a simple area threshhold fails to allow sliver filtering
we might try a metric from ecology, the area to perimeter ratio.
Here is a query:

select
	zd.zoning_sim as zone,
	st_area(st_intersection(cl.the_geom, zd.the_geom)) as area,
	st_length(st_boundary(st_intersection(cl.the_geom, zd.the_geom))) as length,
	st_area(st_intersection(cl.the_geom, zd.the_geom)) / st_length(st_boundary(st_intersection(cl.the_geom, zd.the_geom))) as ap_ratio,
	st_length(st_boundary(st_intersection(cl.the_geom, zd.the_geom))) / st_area(st_intersection(cl.the_geom, zd.the_geom)) as pa_ratio
from
	v_citylots cl,
	planning.zoning_districts zd
where cl.blklot = '3514003'
and st_intersects(cl.the_geom, zd.the_geom)
order by st_area(st_intersection(cl.the_geom, zd.the_geom)) desc

And here are the results:

zone

area

length

a2p

p2a

C-3-G

1756.165

172.792

10.163

0.098

NCT-3  

0.618

66.608

0.009

107.696

The first row is a fairly normal city lot.
The second row is the sliver.
As you can see, the difference is an order of magnitude which is what we would want for a threshhold.

convert multi geometry to single geometry

These statements create a single geometry table from a multi geometry table.
The analogous arcmap operation is called explode (I think).
This is useful to help improve the rendering performance in WMS.

-- Create the single geometry is from the multi geometry.
create table my_single_geom as
select gid, objectid, distance, area, length, label, (ST_Dump(the_geom)).geom as the_geom
from my_multi_geom

-- Insert a row into geometry_columns.
-- Without this, its not a postgis layer.
-- A single geometry polygon is of type is POLYGON.
-- A multiple geometry polygon is of type is MULTIPOLYGON.
INSERT INTO geometry_columns (
    f_table_catalog,
    f_table_schema,
    f_table_name character,
    f_geometry_column,
    coord_dimension,
    srid,
    type)
VALUES (
    '',
    '',
    'my_single_geom'
    'the_geom',
    2,
    900913,
    'POLYGON'
)
  • No labels