Postgres Test Fest  April 03-05
results by dbb  (Brian Hamlin)

Postgres 9a5
PostGIS trunk (2)

* build and install postgres
* build and install postgis
* regress tests on PostGIS...
  somehow stuck when running, so instead a simple shell script
  cd postgis_trunk/regress
  for n in *sql; do psql -f $n; done
  all worked but 5, for cause.. looks good

* pgbench as per wiki suggestions, including prepared transactions

* osm2pgsql on netherlands.osm dump
  load as db 'gis'

* ogr2ogr -f KML t.kml 'PG:dbname=gis host=localhost port=5840' planet_osm_point
  good, 400k point placemarks

* convert to geography

create table po_line_r_geog as 
  select osm_id, name, religion, st_geogfromtext(st_astext(way)) as the_geog from planet_osm_line                         
where religion is not null;

create table po_point_r_geog as 
  select osm_id, name, religion, st_geogfromtext(st_astext(way)) as the_geog from planet_osm_point                         
where religion is not null;

create table po_poly_r_geog as 
  select osm_id, name, religion, st_geogfromtext(st_astext(way)) as the_geog from planet_osm_polygon                         
where religion is not null;

* is it there? working ?

ogrinfo 'PG:dbname=gis host=localhost port=5840'  po_point_r_geog
ogrinfo 'PG:dbname=gis host=localhost port=5840'  po_line_r_geog
ogrinfo 'PG:dbname=gis host=localhost port=5840'  po_poly_r_geog

* some geography

'SRID=4326;POINT(6.896389 52.22)'  -- twente

-- dwithin

select name from planet_osm_point 
where st_dwithin( way, 'SRID=4326;POINT(6.896389 52.22)', 1000 );

select name from po_point_r_geog 
where st_dwithin( the_geog, 'SRID=4326;POINT(6.896389 52.22)', 1000 );

-- distance

select name, st_distance( the_geog, 'SRID=4326;POINT(6.896389 52.22)' ) as dist 
from po_point_r_geog order by dist;

  4657 results, one for each entry.. matches

select name, st_distance( the_geog, 'SRID=4326;POINT(6.896389 52.22)' ) as dist 
from po_poly_r_geog order by dist; -- matches

select name, st_distance( the_geog, 'SRID=4326;POINT(6.896389 52.22)' ) as dist 
from po_line_r_geog order by dist; -- matches

-- intersects

select pt.osm_id, pt.name, poly.osm_id, poly.name from  po_point_r_geog pt, po_poly_r_geog poly
where st_intersects( pt.the_geog, poly.the_geog );  

  many points are also represented as polys seperately.. easy to see many matches


==
overall, no bugs to report 
-bh