Skip to content
Archive of entries posted on

PostGIS 2.0 Intro Presentation

at UC Berkeley Soda Hall, for the San Francisco PostgreSQL Users Group and again at the College of Natural Resources for GIS Day 2012 main slides and dustymugs’ raster slides errata: * The ST_MakeValid(geom) example could have been expanded to include ST_IsValidDetail() * It is arguably wrong to say that Lat/Lng (EPSG:4326) data is one […]

KNN Search Speed Test

SYNOPSIS: Using one million randomly generated points, time finding the ten nearest points to the center of the range by using the KNN distance operator versus using a call to PostGIS ST_Distance(). ## Use ST_Distance() explain analyze SELECT ST_Distance(the_geom, ST_GeomFromText(‘POINT(500 500)’)) FROM tgeom ORDER BY ST_Distance(the_geom, ST_GeomFromText(‘POINT(500 500)’)) asc limit 10; Total runtime: 1568.499 ms […]

Check for Validity

SELECT name, state_name, fips, st_summary(the_geom), st_isvalidreason(the_geom) FROM usa_counties WHERE not st_isvalid(the_geom); NOTICE: Ring Self-intersection at or near point -70.82466 42.26050 -[ RECORD 1 ]—-+————————————————- name | Plymouth state_name | Massachusetts fips | 25023 st_summary | MultiPolygon[B] with 1 elements | Polygon[] with 1 rings | ring 0 has 31 points st_isvalidreason | Ring Self-intersection[-70.8246609058028 42.2605069326982] […]