—————————————– install ———— create or replace function r_median(_float8) returns float as ‘ median(arg1) ‘ language ‘plr’; CREATE AGGREGATE median ( sfunc = plr_array_accum, basetype = float8, stype = _float8, finalfunc = r_median ); —————————————– test ————— select max(a.res),avg(a.res),median(a.res),min(a.res) from ( select (st_area(wkb_geometry) – shape_area) as res from landtypes_090911_marin_parcel_adjusted) a; thuban1 Time: 21255 ms r900 Time: […]
Archive of posts filed under the Postgres category.
Calling Bluff
Two years ago a hard disk arrived on the desk of a colleague, from persons claiming to have some kind of parcel data “for every state in the US.” Naturally, being very skeptical and at the same time, just a bit eager to show off open source tools on linux, I whipped up a script […]
Topology of Hex Cells
A year ago I had modified code from here to make a set of about thirty million hex cells across the continental United States, using the projection from the National Map, EPSG:2163. Today, topology wizard strk was building a set of hex cells to test conversion to topology in PostGIS 2.0. The question arose, would […]
PostGIS Geocoder behavior
I became interested in the practical behavior of the PostGIS TIGER 2010 Geocoder when I started doing statistical profiling of the results of geocoding 100,000 standard addresses in my county and got results that looked like what you see in the image at right. That inquiry morphed into this short paper PDF. Why statistical profiling? […]
ST_DWithin on a Grid
As part of a larger inquiry, I wanted to know how ST_DWithin() behaved on a regular grid – that is, what is the relationship of the input radius, and the number of grid cells that could be selected in the result. The manual implies that it is an intersects relationship not a contains relationship, and […]
Variable Buffers in PostGIS
Some problems in PostGIS are naturally parallelizable. Many problems involving a grid as a base or reference data set fall into this category; when each grid cell is visited and some operation is done there, each grid cell is uniquely referenced in its own set of calculations, and invariant, then it is likely that the […]
PostGIS subtraction
Threading in python and calling out to PostGIS in each thread can work well. Here is an example of using python’s Queue and threading.Thread to break up a large spatial workload into pieces. It turns out that many spatial data problems are naturally parallelizable, and this is one of them. Per the PostGIS manual, subtraction […]
KNN GIST Index in Postgresql
With version 9.1 of PostgreSQL and above, the KNN GIST Index is available. There are two new operators defined: <-> and <#> With version 2.0 of PostGIS and above, the KNN GIST Index functionality is exposed for PostGIS geometry types. <-> – Returns the distance between two points. For point / point checks it uses […]
list schemas the low level way
SELECT n.nspname AS “Name”, pg_catalog.pg_get_userbyid(n.nspowner) AS “Owner” FROM pg_catalog.pg_namespace n WHERE n.nspname !~ ‘^pg_’ AND n.nspname ‘information_schema’ ORDER BY 1;